How do we automatically save attachments we receive in email (Microsoft Outlook)
Certifications:
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 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.
