If you're like me you log in to your mailbox each morning and find one or more automated messages with attachments—typically system reports. Then you go through the repetitive process of saving those reports to a directory so that you can work with them later. Undoubtedly you've wished, as I did, that Outlook could take care of that process for you upon logging in, so you wouldn't have to be bothered with it. This 10-Minute Solution shows you how to set up Outlook to do this task for you.
Thanks to Visual Basic for Applications, you can make Outlook process those attachments automatically.
How? To begin with, go to Tools > Macro > Macros, type a new name for your macro in the Macro Name field, and click Create.
Outlook will load the Visual Basic editor for you and start your new macro with the proper name, like so:
Sub ReportSave()
End Sub
Now just set up the objects you'll need to make this work:
Dim oApp As Application
Dim oNS As NameSpace
Dim oMsg As Object
Dim oAttachments As Outlook.Attachments
Set oApp = New Outlook.Application
Set oNS = oApp.GetNamespace("MAPI")
This gets your macro read to work with items.
Be sure you're working with the Inbox, so set the current folder to the Inbox like so:
Set oFolder = oNS.GetDefaultFolder(olFolderInbox)
The table is set; let's eat.
How the Program Works
Outlook looks at each item in the Inbox and determines which of them to auto-process. In most cases, the FROM address is the same for your automated report messages, so you can use that address as the criterion to check:
For Each oMsg In oFolder.Items
This tells Outlook to cycle through each item...
With oMsg
and with each item it is to...
If .SenderName = "Report Generator" Then
Check to see if the SenderName is "Report Generator." SenderName is the display name of the person who sent you the message. If it is from the specified sender, then it is to...
oMsg.Attachments.Item(1).SaveAsFile "C:\reports\in\" &
myAttachments.Item(1).DisplayName
Use the SaveAsFile method to save the file to the C:\Reports\in\ directory using the filename that the sender gave it. If you'd rather assign your own filename to the item, you can do so by substituting your own filename, in quotes, for the "myAttachments.Item(1).DisplayName" part, like this:
oMsg.Attachments.Item(1).SaveAsFile "C:\reports\in\" & "report.txt"
After you're done saving the file, you probably want to delete the message it came in because you don't need it once the file is saved. You can use this...
.Delete
to do that. Remember that you started with a "With Each oMsg in oFolder.Items" so you can simply use the .Delete method to delete the current message.
Knowing Whether You Got a Report
By having the program automatically save the file and delete the message, you may not know (without looking) whether you received your automatic report or not. To solve this, you can give yourself a notification with a message box by adding this line after the .Delete:
MsgBox "The report has been processed"
At this point, if you receive a report that is processed, the message box will pop up with that message and you'll acknowledge it by clicking OK.
That's the basics of the application. You'll need an "End If" statement to close your IF procedure that checks for SenderName, an "End With" statement to finish the "With Each oMsg…", and a "Next" statement, like this:
End If
End With
Next
End Sub
Now you're done!
Keeping Old Reports
One flaw in this program is that if you have more than one report waiting from the same auto-report generator (say, if you've been away for a day or two and haven't logged in), it's going to cycle through and save each one with the same filename—overwriting the first ones saved with the later ones, so that you end up with just one report.
To prevent this auto-deletion, have the program assign a unique number to the beginning of each filename. Go back to where you defined your variables and define a string like so:
DIM StrControl
Assign it a value by adding this line beneath where you set your oFolder to the Inbox:
StrControl = 0
Then add this:
StrControl = StrControl + 1
Beneath the "If" statement that checks for SenderName to increment the value by one each time a message from the report generator is discovered.
Finally, change the line that saves the file by adding that StrControl value to the beginning of the filename, like this:
oMsg.Attachments.Item(1).SaveAsFile "C:\reports\in\" & strControl
& "report.txt"
The Final Script
When you're all done, the completed macro should look something like this:
Sub Reportave()
Dim oApp As Application
Dim oNS As NameSpace
Dim oMsg As Object
Dim oAttachments As Outlook.Attachments
Dim strControl
Set oApp = New Outlook.Application
Set oNS = oApp.GetNamespace("MAPI")
Set oFolder = oNS.GetDefaultFolder(olFolderInbox)
strControl = 0
For Each oMsg In oFolder.Items
With oMsg
If .SenderName = "Report Generator" Then
strControl = strControl + 1
oMsg.Attachments.Item(1).SaveAsFile "C:\reports\in\"
& strControl & "report.txt"
.Delete
End If
End With
Next
End Sub
