VBA Save e-mail attachment

Outlook is a very widespread programme, especially in the professional field, for managing and processing e-mails and of course much more, if you only think of the calendar and task management.

A big advantage of Outlook over other e-mail clients is that it comes with VBA (Visual Basic for Applications), which makes it possible to automate many additional tasks if the appropriate functions cannot already be done via Outlook rules and integrated functions.

Save email attachment automatically with VBA

One task that can save you a lot of clicks is saving email attachments. If you have typical recurring e-mails whose attachments always have to be saved under the same path, you have to click through to this path each time with the “Save as” dialogue.

Using VBA, you can write a macro to save the e-mail attachment. You can also assign a shortcut to the macro or insert a button in the bar for quick access. This way, you only need one click or keystroke to save the e-mail attachment. Such a macro can also be combined with other macros so that, for example, the email attachment is printed by VBA at the same time.

VBA code to save Outlook e-mail attachment

The following VBA code can be used to save email attachments under a fixed folder path. Only the e-mail attachments of the selected mail are saved.

In Outlook, you can access the Visual Basic Editor by pressing Ctrl+F11. It is also possible via the menu Developer Tools ? Visual Basic.

Public Sub SaveAttachments()

    Dim objOL As Outlook.Application
    Dim objMsg As Outlook.MailItem
    Dim objAttachments As Outlook.Attachments
    Dim objSelection As Outlook.Selection
    Dim i As Long
    Dim lngCount As Long
    Dim strFile As String
    Dim strFolderpath As String
    
    Instantiate Outlook Application object
    Set objOL = CreateObject("Outlook.Application")
    
    ' Get the collection of selected objects (e-mails)
    Set objSelection = objOL.ActiveExplorer.Selection
    
    ' Set the folder path where the e-mail attachment should be saved
    strFolderpath = "c:\Users\AnyUser\Documents\subfolder\bills\year_2020\"
    
    ' Check each selected object (e-mail) to see if it has an attachment. If attachment exists,
    ' then save under the folder path.
    For Each objMsg In objSelection
    
        ' Determine the attachments of the selected object (e-mail).
        Set objAttachments = objMsg.Attachments
        lngCount = objAttachments.Count
    
        If lngCount > 0 Then
    
            ' We use a backwards counter here, but it should also work the other way round.
    
            For i = lngCount To 1 Step -1
    
                Save attachment before deleting from item.
                Determine file name
                strFile = objAttachments.Item(i).FileName
    
                ' Combine folder path with file name
                strFile = strFolderpath & strFile
    
                ' Save attachment as a file
                objAttachments.Item(i).SaveAsFile strFile
    
            Next i
    
        End If
    Next
    
ExitSub:
    Set objAttachments = Nothing
    Set objMsg = Nothing
    Set objSelection = Nothing
    Set objOL = Nothing
End Sub

Call Outlook macro via quick access

You can now call or execute the macro, provided it is Public, via the Developer Tools ? Macro ? Macros dialogue. Or you can place a button in the quick access. Using the small arrow pointing downwards in the quick access, select “More commands …” or go to File ? Options ? Quick Access Toolbar.

In the dialogue window that then opens, limit the commands to macros and add the macro in question.

Using the “Change” button, you can then assign an icon or adjust the text.