I'm trying to download and then open an Excel spreadsheet attachment in an Outlook email using VBA in Excel. How can I:
- Download the one and only attachment from the first email (the newest email) in my Outlook inbox
- Save the attachment in a file with a specified path (eg: "C:...")
- Rename the attachment name with the: current date + previous file name
- Save the email into a different folder with a path like "C:..."
- Mark the email in Outlook as "read"
- Open the excel attachment in Excel
I also want to be able to save the following as individual strings assigned to individual variables:
- Sender email Address
- Date received
- Date Sent
- Subject
- The message of the email
although this may be better to ask in a separate question / look for it myself.
The code I do have currently is from other forums online, and probably isn't very helpful. However, here are some bits and pieces I have been working on:
Sub SaveAttachments()
Dim olFolder As Outlook.MAPIFolder
Dim att As Outlook.Attachment
Dim strFilePath As String
Dim fsSaveFolder As String
fsSaveFolder = "C:\test\"
strFilePath = "C:\temp\"
Set olFolder = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
For Each msg In olFolder.Items
While msg.Attachments.Count > 0
bflag = False
If Right$(msg.Attachments(1).Filename, 3) = "msg" Then
bflag = True
msg.Attachments(1).SaveAsFile strFilePath & strTmpMsg
Set msg2 = Application.CreateItemFromTemplate(strFilePath & strTmpMsg)
End If
sSavePathFS = fsSaveFolder & msg2.Attachments(1).Filename
End If
End Sub