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.