29th October 2021, 1 min read

Saving Attachments in Outlook via VBA

Original post is here eklausmeier.goip.de/blog/2021/10-29-saving-attachments-in-outlook-via-vba.

Task at hand: You receive daily reports with attachments, which you need to analyze programmatically. Therefore you want those attachments in your filesystem. You need an Outlook macro, which saves all those attachments to a directory. Outlook VBA to the rescue.

Below macro processes all e-mails which are selected in Outlook. It stores them in C:\temp. You have to adapt handling for checking the e-mail subject, whether the e-mail in question is actually you expect to contain the report. VBA function InStr() comes in handy here.

Sub saveAttachment()
    Dim oAttachment As Outlook.Attachment
    Dim mItem As Outlook.MailItem
    Dim sSaveFolder As String, dateFormat As String, partOfFilename As String
    Dim i As Long, nSelectedMsg As Long
    
    sSaveFolder = "C:\temp\"

    Set oExplorer = Application.ActiveExplorer
    If oExplorer Is Nothing Then
        MsgBox "No active explorer", vbOKOnly
        Exit Sub
    End If
    nSelectedMsg = oExplorer.Selection.Count

    For i = 1 To nSelectedMsg
        Set mItem = oExplorer.Selection.Item(i)
        dateFormat = Format(mItem.ReceivedTime, "yymmdd")
        ' Fiddle with subject-line of e-mail to get parts of filename
        If InStr(mItem.Subject, "...abc") > 0 Then
            partOfFilename = "...uvw"
        Else
            MsgBox "Macro applied to wrong e-mail", vbCritical
            Exit Sub
        End If
        For Each oAttachment In mItem.Attachments
            FileName = "DAILY_REPORT_" & partOfFilename & "_" & dateFormat & ".txt"
            ' oAttachment.DisplayName not needed, as we make our own filename
            ' MsgBox "filename=" & FileName, vbOKOnly
            oAttachment.SaveAsFile sSaveFolder & FileName
        Next
    Next
End Sub

SaveAsFile saves the attachment with date and time as of date of receipt of e-mail. It also overwrites any file with same name.