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.

Image result for outlook run a script missing

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 Sub

Now 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.

Advertisements