How do we automatically save attachments we receive in email (Microsoft Outlook)
Certifications:
- How to create users in databases that belong to a SQL Server Always On Availability Group - 10 February 2025
- How to create a Logon Trigger to control which users are allowed to connect to an Oracle Database - 13 January 2025
- How do we collect the actual execution plan from queries using Extended Event and how do we read its data - 2 December 2024
How would it feel if attachments sent by a certain sender with perhaps a certain title and many others perhaps, could automatically be archived in a computer folder dated?
Also couldn't we automatically upload them to some server with ftp or enter them into a database?
Excel is known to have programming capabilities through VBA (Visual Basic for Applications) scripting but Microsoft hasn't stopped there. It enables us to use them even in Outlook.
By using them in Outlook we can automate many tasks or create email rules.
The possibilities are unlimited but in this article you will see how we will create a rule that whenever an email arrives from a specific recipient you will save the attachment in a specific path with today's date.
*Steps and images are for Outlook 2016, works on all versions but may need some modifications.
Enable developer mode
First, open Outlook and select:
File – Options – Customize Ribbon – click on the Developer box
With the previous step, the Developer tab appeared, so we choose:
Macro Security – Enable all macros
Add the VBA code
Go to the Developer tab again and select Visual Basic.
At this point we make a modification to the following script and put the path we want in place of the one I have put in the example 'C:\Users\smatzouranis\outlook_files\'.
We double click on the module option and place the code. Then we click the save and play buttons.
Public Sub SaveAttachmentsToDisk(MItem As Outlook.MailItem)
Dim oAttachment As Outlook.Attachment
Dim sMakeFolder As String
sMakeFolder = "C:\Users\smatzouranis\outlook_files\" & Format(Now(), "yyyyMMdd") & "\"
If Not CreateObject("Scripting.FileSystemObject").FolderExists(sMakeFolder) Then
CreateObject("Scripting.FileSystemObject").CreateFolder sMakeFolder
End If
For Each oAttachment In MItem.Attachments
oAttachment.SaveAsFile sMakeFolder & oAttachment.DisplayName
Next
End Sub
Enable running macros in the registry (only applies to new versions of Outlook)
In this step we close Outlook and we have to activate a value in the registry.
In our example I use Outlook 2016 so the steps are:
Regedit – in the path HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Outlook\Security – Right click new dword value by name EnableUnsafeClientMailRules and value 1.
Macro activation rules
Open Outlook again and go to create the rule, the option is on the tab Home – Rules – Create Rule… – Advanced Options…
In the first window that will appear when we press the option Advanced Options is the point where we choose when the rule will apply, from which sender, with which title, etc.
By selecting Next we should select the option run a script and then the hyperlink script marked in red.
In the popup window we will select the option we created before with the vbscript SaveAttachmentsToDisk.
OK – Next – Finish
And we are ready.
The result
Sending a test email with an excel to myself. A folder was automatically created with today's date and the attachment.
In the next article we will see how we can read the records that this attachment can have with T-SQL and how we can insert the records into a table with an automated agent job in SQL Server.
