Open an Excel attachment in Outlook 2010 using VBA

Question

I would like to know how to Open an Excel attachment in my inbox Outlook 2010 with VBA code.

I would like the code to:

1. Check for a specific subject that does not change "Test"
2. Check if that email is read or unread and if it is unread to use that one

I have a rule in place where the emails are stored in a subfolder based on the subject but I can change it to go back to the main inbox

I would really appreciate if you can explain what the code is doing as I am not familiar with the Outlook connection bit.

---

This is what I have pulled together from various sites including stackoverflow it does the job.

• It seems to run for emails that have RE: in the subject for the 10PM and 5PM emails. I explicitly named subject of the two emails. Can anyone help me?
• I am not familiar with declaring variables for Outlook objects. Have I declared the variables correctly?
• I would also like to copy the data within each file and paste it into another workbook. I would like the data to be pasted underneath the first data from each work book so it will need to find the last row and paste it 1 row below the last row for each data on each workbook onto the open workbook which is stored in another path.

.

Sub DownloadAttachmentFirstUnreadEmail()

    Const olFolderInbox = 6
    Const AttachmentPath As String = "C:\My Documents\Outlook Test\"

    Dim oOlAtch As Object

    Set objOutlook = CreateObject("Outlook.Application")
    Set objNamespace = objOutlook.GetNamespace("MAPI")
    Set objFolder = objNamespace.GetDefaultFolder(olFolderInbox)
    Set objFolder = objFolder.Folders("**CLIENT ISSUES**").Folders("*Daily Reports").Folders("1. Open Trade Report")

    Set colItems = objFolder.Items
    Set colFilteredItems1 = colItems.Restrict("[Unread] = True AND [Subject] = '10PM FXC Email notification for Martin Currie'")
    Set colFilteredItems2 = colItems.Restrict("[Unread] = True AND [Subject] = 'FXC Email notification for Martin Currie Funds'")

       '~~> Check if there are any actual unread 10PM FXC emails
    If colFilteredItems1.Count = 0 Then
        MsgBox "NO Unread 10PM Email In Inbox"
    Else
        '~~> Extract the attachment from the 1st unread email
        For Each colItems In colFilteredItems1
            '~~> Check if the email actually has an attachment
            If colItems.Attachments.Count <> 0 Then
                For Each oOlAtch In colItems.Attachments
                    '~~> save the attachment and open them
                    oOlAtch.SaveAsFile AttachmentPath & oOlAtch.Filename
                    Set wb = Workbooks.Open(Filename:=AttachmentPath & oOlAtch.Filename)
                Next oOlAtch
            Else
                MsgBox "10PM email doesn't have an attachment"
            End If
        Next colItems

    End If

           '~~> Check if there are any actual unread FXC Email emails
    If colFilteredItems2.Count = 0 Then
        MsgBox "NO Unread 5PM Email In Inbox"
    Else
        '~~> Extract the attachment from the 1st unread email
        For Each colItems In colFilteredItems2
            '~~> Check if the email actually has an attachment
            If colItems.Attachments.Count <> 0 Then
                For Each oOlAtch In colItems.Attachments
                    '~~> save the attachment and open them
                    oOlAtch.SaveAsFile AttachmentPath & oOlAtch.Filename
                    Set wb = Workbooks.Open(Filename:=AttachmentPath & oOlAtch.Filename)
                Next oOlAtch
            Else
                MsgBox "5PM email doesn't have an attachment"
            End If
        Next colItems

    End If

End Sub

Answer 1

First of all, I'd suggest starting from the Getting Started with VBA in Outlook 2010 article in MSDN.

You can assign a VBA macro to the Outlook rule, it should look like the following one:

public sub test(mail as MailItem)
    ' 
end sub

where you can check out the mail object. It looks like you need to check out the Subject, UnRead and Attachments properties of the MailItem class.