Download attachment from Outlook and Open in Excel

Viewed 92553

I'm trying to download and then open an Excel spreadsheet attachment in an Outlook email using VBA in Excel. How can I:

  1. Download the one and only attachment from the first email (the newest email) in my Outlook inbox
  2. Save the attachment in a file with a specified path (eg: "C:...")
  3. Rename the attachment name with the: current date + previous file name
  4. Save the email into a different folder with a path like "C:..."
  5. Mark the email in Outlook as "read"
  6. 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
2 Answers
Related