I worked on a project once where a bot connected to a production line would send status emails with attachments on production rates to an email address we had setup. This was great, except, the bot sent an email every hour, and the attachments piled up. We could create a rule to take the email and file it in a specific folder, but what about the attachment?
Well, don’t fret, because there’s a way you can run that Outlook rule and have a simple VBA script execute to save the attachment from the email in a specific folder on your machine.
If you go to create a rule in Outlook and don’t see the option to ‘run a script’, then check out this previous post to learn what you can do to get it.
In the visual basic editor create a module and insert the below code:
'This code saves email attachments into a specific folder on the user's machine, then deletes all other files in that folder that do not match the file type you're looking to save - in this case we are saving .xlsx files
'Written by JamesCanCode
'www.jamescancode.com
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Sub Attachment_Collect(itm As Outlook.MailItem)
On Error Resume Next
Dim objAtt As Outlook.Attachment
Dim saveFolder As String
Dim fname As String
saveFolder = "C:\Email_Attachments" ' Change this file path to your folder
For Each objAtt In itm.Attachments
posr = InStrRev(objAtt.FileName, ".xlsx")
ext = Right(objAtt.FileName, Len(objAtt.FileName) - posr)
posl = InStr(objAtt.FileName, ".")
fname = Left(objAtt.FileName, posl - 1)
objAtt.SaveAsFile saveFolder & "\" & fname & "_" & Format(itm.ReceivedTime, "mm-dd-yyyy_hhmmss") & "." & ext
Set objAtt = Nothing
Next
'Delete all other files in the folder
On Error Resume Next
Kill saveFolder & "\*.jpg*"
On Error GoTo 0
On Error Resume Next
Kill saveFolder & "\*.jpeg*"
On Error GoTo 0
On Error Resume Next
Kill saveFolder & "\*.gif*"
On Error GoTo 0
On Error Resume Next
Kill saveFolder & "\*.txt*"
On Error GoTo 0
On Error Resume Next
Kill saveFolder & "\*.doc*"
On Error GoTo 0
On Error Resume Next
Kill saveFolder & "\*.ppt*"
On Error GoTo 0
On Error Resume Next
Kill saveFolder & "\*.xml*"
On Error GoTo 0
On Error Resume Next
Kill saveFolder & "\*.png*"
On Error GoTo 0
End SubNow just set your rule to run the above script and every time an email is received that executes the rule, any .xlsx attachment in the email will be saved to the folder you specify in the script. It’s that simple.
