Saving Outlook Email Attachments to a Folder

» Posted on March 7 2015 in Code

We run a Professional Photo Lab (in San Diego) and while we have a very powerful online ordering system called ROES, and also have a Simple File Upload form for our customers to use to order prints, we still end up with some people who just want to email us their images as attachments.

Attachments are a pain, especially when there are multiple files attached. You have to save one, which asks you where you want to save, then you have to browse out to it, create a folder etc.

Recently I saw a cool Dropbox plugin that allowed for this same sort of thing - you got an email address from the vendor, and it would harvest and save any attachments along with the body of the email to a folder in your Dropbox. I liked the idea, so I decided to see if I could get a Macro in Outlook to do more or less the same thing. I like this better than the Dropbox plugin as I won’t have to use a 3rd party email address, and I don’t have to use Dropbox.

Goal: Check incoming email for attachments, if there are any, save them to a specific folder in a sub folder by Sender Name, then in another sub folder by Date + subject line, and include a copy of the entire email as a txt file. It also needs to be somewhat bulletproof so we need to check for and remove any special characters that may be in the Subject or Sender Name, since both of those will be in the folder structure. Just for good measure it needs to save each filename with a sequence prefix so they are unique (within a single email).

I found lots of info on the web, though none of what I found did exactly what I wanted. These sites were very helpful:

http://www.pixelchef.net/content/rule-autosave-attachment-outlook
This site had most of what I needed, especially code that was in one of the comments.

Stackoverflow post
This helped expand on the pixelchef post above, both the OP’s own answer and especially the link to slipstick about Macros and security and why it worked at first and then stopped.

http://www.slipstick.com/developer/how-to-use-outlooks-vba-editor/
Without this I am not sure I would have ever figured it all out.

Stackoverflow post on replacing illegal characters
This post had a way to strip potential illegal characters out of a string in VBA. I used this to strip potential illegal characters out of the Subject and Sender Name since I am appending them to folder names.

Here is what I did to get all of this to work. This is with Outlook 2010 on a Windows 7 computer. Please keep in mind I am not an expert at VBA or Macros, I just like to obsess over things like this until they do what I want.

• Create a folder on your local hard drive somewhere - try to make it something easy like D:/emails. If it is a networked drive you can probably still use it, but you will need to make sure the path is correct.
• Take a deep breath, you need to create a Self Signed Security Certificate. this sounds scary, but it is really easy. Inside your Program Files (x86) folder look for the Microsoft Office folder. Mine was here: C:\Program Files (x86)\Microsoft Office\Office14. Inside this folder is a file called SELFCERT.exe. You can also just search for it in the file manager and it should come up. Double click this to launch and it should open up a small window where you can only type in one place - simply type in a name for your certificate - I called mine RonsCert. When you are done press ok.
• Open Outlook and press Alt + F11 this will open up the VBA editor for Macros in Outlook.
• You need to create a Module - I did this by Right-clicking on Microsoft Outlook Objects in the Panel on the left side and then chose Insert > Module. I think you can also just choose Insert > Module from the main navigation at the top. If you are already using Macros, You might be able to just add this Sub to an existing module, but I am not sure.
• Copy and paste the code below into the main window. Edit the line Root_Folder = "D:\Emails" ' sets the parent folder to save to and change the “D:\Emails” part to whatever the path/name of the parent folder you want to use is. This folder must already exist. Make sure you keep the quotes around it. Make sure it is a valid path. I think if there are spaces in the path or it is on a network drive it will work, but you will have to research how to write the correct path.

Public Sub Save_Attach_To_Disk(itm As Outlook.MailItem)
' note the use of quotes around static things like / or - or emait.txt while  declared variables like Root_Folder do not have them
' if you get an error - object expected it might be because you didn't put quotes around something static
' if this runs only once but then doesn't work it is because of macro security settings are not allowing it - there is no error shown

' only process emails that have attachments
If (itm.Attachments.Count >= 1) Then

Dim objAtt As Outlook.Attachment

' prep date format of sub folder
Dim dateFormat_target_folder
dateFormat_target_folder = Format(itm.ReceivedTime, "yyyy-mm-dd")

Dim Root_Folder As String
Root_Folder = "D:\Emails" ' sets the parent folder to save to

'add sub folder with sender name, checking for illegal characters
Dim saveFolder_Root2 As String
strSenderName = itm.senderName

sreplace = "_"
'create an array to loop through and replace any potential illegal characters

For Each mychar In Array("/", "\", "^", "*", "%", "$", "#", "@", "~", "`", "{", "}", "[", "]", "|", ";", ":", ",", ".", "'", "+", "=", "?", "!", " ", Chr(34), "<", ">", "¦")
   strSenderName = Replace(strSenderName, mychar, sreplace)
Next mychar

saveFolder_Root2 = Root_Folder & "\" & strSenderName

Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
' check and see if sub folder with sender name exisits, if not create it
If Not objFSO.FolderExists(saveFolder_Root2) Then
objFSO.CreateFolder (saveFolder_Root2)

End If

''''''''''''''''''''''
'add sub-sub folder with date and subject
Dim saveFolder_bySender_date As String
strSubject = itm.Subject

sreplace2 = "_"
'create an array to loop through and replace any potential illegal characters
For Each mychar2 In Array("/", "\", "^", "*", "%", "$", "#", "@", "~", "`", "{", "}", "[", "]", "|", ";", ":", ",", ".", "'", "+", "=", "?", "!", " ", Chr(34), "<", ">", "¦")
   strSubject = Replace(strSubject, mychar2, sreplace2)
Next mychar2

saveFolder_bySender_date = saveFolder_Root2 & "\" & dateFormat_target_folder & "-" & strSubject 'adds subject to folder name
' check and see if sub-sub folder with date exists, under sender name, if not create it
If Not objFSO.FolderExists(saveFolder_bySender_date) Then
objFSO.CreateFolder (saveFolder_bySender_date)
End If

''''''''''''''''''''''
' loop thru and save each attachment to folder, ading a sequence prefix in case they managed to send a bunch with the same name
Dim i As Integer
i = 0
For Each objAtt In itm.Attachments
i = i + 1
objAtt.SaveAsFile saveFolder_bySender_date & "\" & "(" & i & ")-" & objAtt.DisplayName
' clear object after saving each one
Set objAtt = Nothing
Next 'go to next attachment

' save a copy of the email to the same folder
itm.SaveAs saveFolder_bySender_date & "\" & "email.txt", olTXT
'done
End If

End Sub

• Now you need to sign your Macro with the Cert you created. Go to Tools > Digital Signature and choose the Cert you created.
• Now save the macro by going to File Save (it might be called VBAproject or something). Close down the VBA editor.

At this point you are mostly done, just a few more steps to create a Rule to run the script on incoming mail, and then also we need to change a security setting to allow self signed certs to run.

• In Outlook go to ‘File > Options > Trust Center > Trust Center Settings > Macro Settings’ and choose ‘Notifications for digitally signed macros, all other macros disabled’. The box below for Add-Ins should not be checked. Press OK a couple times to exit.
• In Outlook, you should be in the Home Tab, click on Rules > Create Rule and then click on Advanced Options. For ‘Step 1’ choose the condition ‘which has an attachment’. Click Next. For ‘Step 1 Select Action -what do you want to do’ choose ‘Run a Script’. Down below in ‘Section 2 Edit the Rule Description’ click on the underlined ‘a script’ and choose the Script we created. For mine it is called ‘Project1.Save_Attach_To_Disk’. I also added a bit to ignore emails if the subject contained “Returned mail” or “Undelivered mail”.

• When closing Outlook, it will ask if you want to save the project - as long as I know I saved it from the VBA editor by clicking File > Save I say No to this. I am not sure if this is correct, but if you do press yes, I think it may cause problems.

That’s it, you should now have a working rule that runs and checks incoming mail if it has attachments. If it does it should save them in a folder with the Senders Name and a sub folder with the date + subject.

Please note - if this works once, and then never again, it is probably because of Macro security, and you did no properly sign your macro or set the macro security properly. Outlook should pop up a warning box the first time you run your macro, say ok, and then it should run on its own even after rebooting/restarting.

I tried to comment the code as best I could, it should be fairly simple to change parts of it.