Excel VBA download attachment from specific fodler in outlook

[djaenike]

Hello,

I am looking to download the most recent attachment from a specific folder in outlook. Right now the macro looks to the default folder (inbox) for the first unread email it finds and if that email has an attachment it downloads it. I would like to have it look to a folder called "Daily Spreadsheets" and download the most recent unread email, not the oldest. Here is my code:

Const olFolderInbox As Integer = 6
'~~> Path for the attachment
Const AttachmentPath As String = "C:\Users\MyName\Desktop\Test Bin\"

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

All suggestions welcome!

 

[John_w]

Replace:

    Set oOlInb = oOlns.GetDefaultFolder(olFolderInbox)

with:

    Set oOlInb = oOlns.GetDefaultFolder(olFolderInbox).Parent
    Set oOlInb = oOlInb.Folders("Subfolder1").Folders("Subfolder1_subfolder1").Folders("Daily Spreadsheets")

using the Folders collection to get the folder path to "Daily Spreadsheets", as the example shows.