- How to bulk detach / attach databases in SQL Server - February 2, 2024
- How to enforce password policy in an Oracle database - 4 January 2024
- How do we change the name of the user / schema in an Oracle database - 1 December 2023
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.
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.