Download attachment from Outlook and Open in Excel

by

I can give you the complete code in one go but that wouldn’t help you learn from it 😉 So let’s Break up your requests and then we will tackle them 1 by 1. This is gonna be a very long post so be patient 🙂

There are total 5 parts which will cover all 7 (yes 7 and not 6) points so you don’t have to create a new question for your 7th point.

PART – 1

  1. Creating a Connection to Outlook
  2. Checking if there is any unread email
  3. Retrieving details like Sender email Address, Date received, Date Sent, Subject, The message of the email

See this code example. I am latebinding with Outlook from Excel then checking if there are any unread items and if there are I am retrieving the relevant details.

Const olFolderInbox As Integer = 6

Sub ExtractFirstUnreadEmailDetails()
    Dim oOlAp As Object, oOlns As Object, oOlInb As Object
    Dim oOlItm As Object

    '~~> Outlook Variables for email
    Dim eSender As String, dtRecvd As String, dtSent As String
    Dim sSubj As String, sMsg As String

    '~~> Get Outlook instance
    Set oOlAp = GetObject(, "Outlook.application")
    Set oOlns = oOlAp.GetNamespace("MAPI")
    Set oOlInb = oOlns.GetDefaultFolder(olFolderInbox)

    '~~> Check if there are any actual unread emails
    If oOlInb.Items.Restrict("[UnRead] = True").Count = 0 Then
        MsgBox "NO Unread Email In Inbox"
        Exit Sub
    End If

    '~~> Store the relevant info in the variables
    For Each oOlItm In oOlInb.Items.Restrict("[UnRead] = True")
        eSender = oOlItm.SenderEmailAddress
        dtRecvd = oOlItm.ReceivedTime
        dtSent = oOlItm.CreationTime
        sSubj = oOlItm.Subject
        sMsg = oOlItm.Body
        Exit For
    Next

    Debug.Print eSender
    Debug.Print dtRecvd
    Debug.Print dtSent
    Debug.Print sSubj
    Debug.Print sMsg
End Sub

So that take care of your request which talks about storing details in the variables.

PART – 2

Now moving on to your next request

  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

See this code example. I am again latebinding with Outlook from Excel then checking if there are any unread items and if there are I am further checking if it has an attachment and if it has then download it to the relevant folder.

Const olFolderInbox As Integer = 6
'~~> Path for the attachment
Const AttachmentPath As String = "C:\"

Sub DownloadAttachmentFirstUnreadEmail()
    Dim oOlAp As Object, oOlns As Object, oOlInb As Object
    Dim oOlItm As Object, oOlAtch As Object

    '~~> New File Name for the attachment
    Dim NewFileName As String
    NewFileName = AttachmentPath & Format(Date, "DD-MM-YYYY") & "-"

    '~~> Get Outlook instance
    Set oOlAp = GetObject(, "Outlook.application")
    Set oOlns = oOlAp.GetNamespace("MAPI")
    Set oOlInb = oOlns.GetDefaultFolder(olFolderInbox)

    '~~> Check if there are any actual unread emails
    If oOlInb.Items.Restrict("[UnRead] = True").Count = 0 Then
        MsgBox "NO Unread Email In Inbox"
        Exit Sub
    End If

    '~~> Extract the attachment from the 1st unread email
    For Each oOlItm In oOlInb.Items.Restrict("[UnRead] = True")
        '~~> Check if the email actually has an attachment
        If oOlItm.Attachments.Count <> 0 Then
            For Each oOlAtch In oOlItm.Attachments
                '~~> Download the attachment
                oOlAtch.SaveAsFile NewFileName & oOlAtch.Filename
                Exit For
            Next
        Else
            MsgBox "The First item doesn't have an attachment"
        End If
        Exit For
    Next
 End Sub

PART – 3

Moving on to your next request

  1. Save the email into a different folder with a path like “C:…”

See this code example. This save the email to say C:\

Const olFolderInbox As Integer = 6
'~~> Path + Filename of the email for saving
Const sEmail As String = "C:\ExportedEmail.msg"

Sub SaveFirstUnreadEmail()
    Dim oOlAp As Object, oOlns As Object, oOlInb As Object
    Dim oOlItm As Object, oOlAtch As Object

    '~~> Get Outlook instance
    Set oOlAp = GetObject(, "Outlook.application")
    Set oOlns = oOlAp.GetNamespace("MAPI")
    Set oOlInb = oOlns.GetDefaultFolder(olFolderInbox)

    '~~> Check if there are any actual unread emails
    If oOlInb.Items.Restrict("[UnRead] = True").Count = 0 Then
        MsgBox "NO Unread Email In Inbox"
        Exit Sub
    End If

    '~~> Save the 1st unread email
    For Each oOlItm In oOlInb.Items.Restrict("[UnRead] = True")
        oOlItm.SaveAs sEmail, 3
        Exit For
    Next
End Sub

PART – 4

Moving on to your next request

  1. Mark the email in Outlook as “read”

See this code example. This will mark the email as read.

Const olFolderInbox As Integer = 6

Sub MarkAsUnread()
    Dim oOlAp As Object, oOlns As Object, oOlInb As Object
    Dim oOlItm As Object, oOlAtch As Object

    '~~> Get Outlook instance
    Set oOlAp = GetObject(, "Outlook.application")
    Set oOlns = oOlAp.GetNamespace("MAPI")
    Set oOlInb = oOlns.GetDefaultFolder(olFolderInbox)

    '~~> Check if there are any actual unread emails
    If oOlInb.Items.Restrict("[UnRead] = True").Count = 0 Then
        MsgBox "NO Unread Email In Inbox"
        Exit Sub
    End If

    '~~> Mark 1st unread email as read
    For Each oOlItm In oOlInb.Items.Restrict("[UnRead] = True")
        oOlItm.UnRead = False
        DoEvents
        oOlItm.Save
        Exit For
    Next
 End Sub

PART – 5

Moving on to your next request

  1. Open the excel attachment in excel

once you have downloaded the file/attachment as shown above then use that path in the below code to open the file.

Sub OpenExcelFile()
    Dim wb As Workbook

    '~~> FilePath is the file that we earlier downloaded
    Set wb = Workbooks.Open(FilePath)
End Sub

I converted this post into several blog posts (with more explanation) which can be accessed via points 15,16 and 17 in vba-excel

More Related Contents:

  1. Embed picture in outlook mail body excel vba
  2. How to do late binding in VBA?
  3. Distinguish visible and invisible attachments with Outlook VBA
  4. How to copy cells that contain certain text to another worksheet [closed]
  5. VBA Macro for copying and pasting columns on same excel sheet
  6. How to add default signature in Outlook
  7. VBA Runtime Error 1004 “Application-defined or Object-defined error” when Selecting Range
  8. How do I base64 encode a string efficiently using Excel VBA?
  9. Efficient way to delete entire row if cell doesn’t contain ‘@’ [duplicate]
  10. Save attachments to a folder and rename them
  11. Building Undo Into an Excel VBA Macro
  12. Best workaround for VBA Transpose array length limit?
  13. How to Lock the data in a cell in excel using vba
  14. Outlook Reply or ReplyAll to an Email
  15. How do I use variables to set properties in VBA (Excel)
  16. Check for the senderEmailAddress
  17. Removing a string that includes CRLF characters from body of e-mail
  18. Workbooks.Open Method in VBA
  19. Windows().Activate works on every computer except one
  20. How to highlight a cell using the hex color value within the cell?
  21. How do I open an outlook .msg file from my harddrive that is NOT in outlook?
  22. How to send email to multiple recipients with addresses stored in Excel?
  23. Open a workbook from VBA and disable Workbook_Open() code?
  24. Creating form programmatically in the module using vba
  25. How to filter listbox values based on a Textbox value
  26. Type Mismatch in Loop to scan Outlook Messages [duplicate]
  27. Find and FindNext for Excel VBA
  28. How do I get a list of unique values from a range in Excel VBA?
  29. How to get selected value in multicolumn listbox
  30. Query my excel worksheet with VBA

Leave a Comment