Using VBA to Manage Your Outlook Email Attachments

Some people get a lot of email attachments! This tutorial came about as a result of an enquiry from a client of mine who received each day around twenty emails, each with an attached file. She had to open each email then save each file to disk before working on the data they contained. It was a tedious process and she asked me if it was possible to automate it. I said yes before realising that I hadn't ever done any Outlook programming! But I was glad to discover that Outlook is as easy to program with VBA as the rest of the Microsoft Office family. This tutorial shows you how to create an Outlook macro which scans your Inbox for messages that contain attachments, then saves them to a named folder. The next tutorial in the series takes the technique a step further and illustrates how you can create a similar macro in Excel to import newly arrived data straight into an Excel workbook.

About Outlook Macros

Writing VBA code in Microsoft Outlook is no different from working in your other Microsoft Office programs. Outlook 2000 and 2002 have the familiar Visual Basic Editor with all the same tools you are used to. Outlook 97 does not have a Visual Basic Editor, instead using VBScript attached to custom forms for its macros. But this doesn't mean that this project isn't suitable for Outlook 97 users. You can program Outlook 97 using VBA Automation from one of the other Office programs. Only a few modifications to the code are required and a full explanation of how to do this is given at the end of this tutorial. Outlook 97 users might want to read through this section first. Unlike other Microsoft Office programs, Outlook can support only one VBA Project at a time. This makes sense really, as you open only one instance of Outlook at a time unlike for example Word or Excel in which each open document or workbook can host its own VBA Project. There is no simple way to distribute Outlook VBA code to users other than exporting VBA modules and relying on the user to import them into the VBA Project in their own copy of Outlook. Outlook VBA code can be 1

distributed using COM Add-ins but this is beyond the scope of this tutorial. But this need not be a problem if you are creating Outlook macros for your own use, or if you are able to import code modules into other people's copies of the program (or can trust them to do it themselves!).

A Macro to Collect Email Attachments

Start Outlook 2000 or 2002 and open the Visual Basic Editor (Tools > Macro > Visual Basic Editor or Keys: Alt+F11). The Project Explorer window displays the current (and only) project. Add a new code module by choosing Insert > Module and give it a suitable name by entering it into the Name section of the Properties window. I have chosen to call my module GetEmailAttachments.

>>>

The module's code window should open automatically. If it doesn't, open it by double-clicking the module name in the Project Explorer window. You're now ready to build the code for the macro...

Name the Macro, Add an Error Handler and Declare the Variables
Enter a suitable name for your macro. I've chosen to call it GetAttachments
Sub GetAttachments() End Sub

It's good practice to add an error handling routine to all your macros, just in case something goes wrong, and the statement that tells the macro where to find it should always come at the top of your code:
On Error GoTo GetAttachments_err

This line tells the macro to jump to a label (like a bookmark in your code) called GetAttachments_err that it will find at the end of the procedure

(we'll create it later). Next, declare the variables we are going to use in the macro:
Dim ns As NameSpace Dim Inbox As MAPIFolder Dim Item As Object Dim Atmt As Attachment Dim FileName As String Dim i As Integer

If you haven't programmed Outlook before some terms here will be unfamiliar. I'll explain what these variables are going to be used for...

The NameSpace is the object that gives you access to all Outlook's folders. In Outlook there is only one and it is called "MAPI" which is an acronym for Messaging Application Programming Interface. We are going to be referring to a mail folder (a MAPIFolder object) which we have given the name Inbox but the macro won't know which actual folder that is until we tell it by giving the variable a value in the next step. We will be using Atmt to refer to the attachment objects we are looking for. The FileName variable is a text string that will be used to create a name and save path for each attachment as it is saved. The integer variable i will be used as a counter to log the progress of the macro.

Set the Values of Variables

Some of the variables need to have their values set at the start of the macro:
Set ns = GetNamespace("MAPI") Set Inbox = ns.GetDefaultFolder(olFolderInbox) i = 0

Note that when you are setting the value of the Inbox variable the Visual Basic Editor provides a list of all the possible default folders. This gives an indication of the possibilities when programming Outlook. Here, you need to choose olFolderInbox from the list.

Search the Folder for Attachments

First of all, we need an If Statement to check that there are messages in the Inbox folder, and abandon the search if there are none. The first line of the If Statement counts the number of items in the folder:
If Inbox.Items.Count = 0 Then MsgBox "There are no messages in the Inbox.", vbInformation, _ "Nothing Found" Exit Sub End If

If there are no message in the Inbox folder the macro displays a message to the user and then exits (i.e. terminates). If, however, there are messages in the Inbox folder the macro proceeds to examine each for attachments. This is done using a pair of nested loops (i.e. one loop running inside the other:
For Each Item In Inbox.Items For Each Atmt In Item.Attachments FileName = "C:\Email Attachments\" & Atmt.FileName Atmt.SaveAsFile FileName i = i + 1 Next Atmt Next Item

The outer loop (For Each Item... Next Item) looks at each Item in the Inbox folder in turn. Although the Item variable was declared as an Object it refers to mail messages, which are by definition the only objects that the

Inbox folder contains because it is a mail folder. The outer loop runs for as many times as there are mail messages in the folder. Each time the outer loop encounters a mail message the inner loop (For Each Atmt... Next Atmt) runs for as many times as there are files attached to the mail message. If there are no attached files the macro continues with to the outer loop. If the inner loop finds an attached file it constructs a text string (the FileName variable) representing the save path of the file. The save path consists of the full path to the folder in which the attachments are to be saved, plus the actual filename of the attachment. Note that I have "hard coded" the folder path. I could have added some code to allow the user to choose a folder to save the attachments in (but I'm trying to keep things simple!). If you hard code a folder path like this you must make sure that the folder exists before you run the macro. The code will not create it for you. The inner lop then saves the attachment to the chosen folder and increments the counter variable (i) by one. This counter will be used later to notify the user how many attachments were found.

Display a Summary Message

After the loops have examined all the mail messages, the only job that remains is to notify the user of the results. I have been counting attachments using the variable i. The value it now holds represents the number of attachments that were found and saved. I am using an If Statement to display a message to the user:
If i > 0 Then MsgBox "I found " & i & " attached files." _ & vbCrLf & "I have saved them into the C:\Email Attachments folder." _ & vbCrLf & vbCrLf & "Have a nice day.", vbInformation, "Finished!" Else MsgBox "I didn't find any attached files in your mail.", vbInformation, _

"Finished!" End If

If i is greater than zero then attachments were found and saved so the message tells the user how many (again using the value of i) and where they were saved to. If i is not greater than zero (i.e. it must be zero) then the user is notified that no attachments were found.

Clear the Memory

The main work of the macro has been done. There a couple of housekeeping tasks to perform. The first task is to clear the computer's memory by setting the value of any object variables (i.e. variable whose values are assigned using the keyword Set) to "Nothing":

GetAttachments_exit: Set Atmt = Nothing Set Item = Nothing Set ns = Nothing Exit Sub

The first line of this code is a label. It will be used by the error handling procedure (coming next). In VBA labels are always followed by a colon and are not regarded as executable code, merely a kind of "bookmark". After each of the object variables are set to "Nothing" the line Exit Sub tells the macro that if it reaches this point it can finish. The reason it is here is that this is not the last line of code in the procedure. Next comes the Error Handler and it is necessary to terminate the macro before the Error Handler (unless, of course, there is an error!).

Handle Errors
I have included a basic "all purpose" error handler here. Remember that at the beginning of the macro code I put the statement: On Error GoTo GetAttachments_err. That statement gave the instruction that, in the event of an error, the macro should jump down to the label GetAttachments_err and continue from there. Here is what is finds:

GetAttachments_err: MsgBox "An unexpected error has occurred." _ & vbCrLf & "Please note and report the following information." _ & vbCrLf & "Macro Name: GetAttachments" _ & vbCrLf & "Error Number: " & Err.Number _ & vbCrLf & "Error Description: " & Err.Description _ , vbCritical, "Error!" Resume GetAttachments_exit

In this case my error handler consists simply of a message notifying the user of the details of the error. This is followed by the statement: Resume GetAttachments_exit which tells the macro to jump up to the label GetAttachments_exit and continue from there. This makes sure that any loose ends are tied up before the macro terminates. The reason for including an error handler is that this gives the developer the opportunity to deals with errors in an appropriate way. If, for example, I knew that a specific error was likely to occur if the user had forgotten to do something important before running the macro, I could include code in my error handler to deal with it in an appropriate way. If you don't include an error handler, the user sees the standard VBA error message:

This message is intended for the eyes of the VBA developer. The average user has no idea what it means and will probably press the Debug button because that is the highlighted one and to most users that means "Press Me!". Of course that is exactly what they should not do because it takes them into the Visual Basic Editor window with their macro in break mode.

The only safe thing for the user to do is press the End button, which will terminate the macro - but, of course, they don't know that! But with an error handler in place you can take charge and display your own message instead:

The error handler code safely shuts down the macro without panicking the user and, all being well, without causing any harm! At last we can end the procedure with the line:
Exit Sub

Test and Save the Macro Before running the macro you should check it for obvious errors and save the code module. Choose to Debug > Compile Projectname to compile the code and check for any coding errors that were not apparent when you typed the code. Then save File > Save VbaProject.OTM to save the changes you have made to Outlook's code:

>>>

Running the Macro Macros in Outlook can be run in the same way as you would run a macro in any other Microsoft Office program. From the Outlook main window choose Tools > Macro > Macros to display the Macros dialog, select your macro, and click Run...

Alternatively you can create a custom toolbar button or menu item and assign the macro to it. When the macro finishes it displays a message detailing how many attachments were found and where the were saved:

Or, if no attachments were found:

If the Inbox folder is empty this message will be displayed:

Important Notes

When this macro saves attached files, any files already in the target folder with the same filenames as the new files will be overwritten without warning. See the note in Additional Refinements below on how to include a datestamp in the filename.

Remember to create the target folder before running the macro. If the save path is incorrect, misspelled or doesn't exist the macro will return an error (although no harm will be done).

Additional Refinements
I wanted to keep this tutorial as simple as possible, so it only makes use of a fraction of the VBA programming power that can be used in Outlook. Future tutorials will explore other areas, but here are some refinements that can be added to the macro to make it more powerful...

Save Only Specific File Types

You may want to save only files of a specific type, such as Excel workbooks or Word documents. A simple If Statement can check the file extension and only save files that have, for example, an extension ending in xls (for Excel workbooks). The additions are marked in red:
For Each Item In Inbox.Items For Each Atmt In Item.Attachments If Right(Atmt.FileName, 3) = "xls" Then FileName = "C:\Email Attachments\" & Atmt.FileName Atmt.SaveAsFile FileName i = i + 1 End If Next Atmt Next Item

Look in a Named Subfolder

If you are in the habit of filing your mail messages into subfolders, perhaps doing this automatically by setting up a rule for incoming mail, you might want to scan a specific subfolder of the Inbox rather than the Inbox folder itself. This example will cause the macro to search in a folder called Sales Reports which is located inside the main Inbox folder. It requires an additional variable declaration at the top of the macro's code:
Dim SubFolder As MAPIFolder

Another line sets the value of the SubFolder variable (this line must come after the line that sets the value of the Inbox variable):

10

Set SubFolder = Inbox.Folders("Sales Reports")

The code which scans for messages and attachments also needs to be modified by substituting the variable name SubFolder for the original variable name Inbox. The message text has been modified too. The changes and additions are marked in red:
If SubFolder.Items.Count = 0 Then MsgBox "There are no messages in the Sales Reports folder." _ , vbInformation, "Nothing Found" Exit Sub End If If SubFolder.Items.Count > 0 Then For Each Item In SubFolder.Items For Each Atmt In Item.Attachments FileName = "C:\Email Attachments\" & Atmt.FileName Atmt.SaveAsFile FileName i = i + 1 Next Atmt Next Item End If

Datestamp the Saved Files The SaveAsFile method used to save an attachment will overwrite an existing file of the same name without warning. This might cause you problems! Suppose each day you received attached files that had the same name, and you ran the macro before you had dealt with yesterday's files? Or what if you hadn't run the macro for a couple of days and you had several mail messages, each containing different files but with the same filename? The answer would be to give each saved file some sort of unique identifier that would set it apart from the others. There are several ways you can do this, each making use of the various properties of a mail item that you can retrieve using VBA. You could append some of this information to the filename to make it unique. You have lots of choices, and all you have to do is modify the line of code that generates the filename with which the attachment is saved - here are some suggestions...

11

EntryID The EntryID property of an Outlook mail item is a unique 48 character alphanumeric string assigned to the item when it arrives. It looks something like this: 0000000080907036605CA742B72580C92B48995C64785300 Your code line would be (additions are marked in red):
FileName = "C:\Email Attachments\" & Item.EntryID & Atmt.FileName

As you can see, unless you choose to add just part of this string you end up with rather long filenames...

CreationTime
In fact there are three date/time properties that apply to each mail item, CreationTime, SentOn and ReceivedTime. Of these CreationTime is most likely to be unique, being the time at which the sender created the mail message to which the attachments belonged. All these properties are supplied as in the form of a full date and time stamp in the format mm/dd/yyyy hh:nn:ss. This is read by the code in this format complete with slashes and colons so needs formatting before it is incorporated into the filename. An example would be (additions are marked in red):
FileName = "C:\Email Attachments\" & _ Format(Item.CreationTime, "yyyymmdd_hhnnss_") & Atmt.FileName

Which would result in filenames sensibly timestamped and looking like this...

12

Show the User the Saved Files You might like to offer the user the opportunity to view the saved files in Windows Explorer. This involves modifying the message near the end of the macro to include a question asking if the user wants to do this, and making use of the VBA Shell command to open the Windows Explorer program. First of all you need to add a variable declaration to those at the top of the macro code:
Dim varResponse As VbMsgBoxResult

NOTE: Office 97 does not have the VBA constant vbMessageBoxResult so if you are working with Office 97 use Variant instead. This variable will hold the user's response when they click the Yes or No button on the modified message box. The modified code looks like this:
If i > 0 Then varResponse = MsgBox("I found " & i & " attached files." _ & vbCrLf & "I have saved them into the C:\Email Attachments folder." _ & vbCrLf & vbCrLf & "Would you like to view the files now?" _ , vbQuestion + vbYesNo, "Finished!") If varResponse = vbYes Then Shell "Explorer.exe /e,C:\Email Attachments", vbNormalFocus End If Else MsgBox "I didn't find any attached files in your mail.", vbInformation, _ "Finished!" End If

The message box code has been modified to display Yes and No buttons to the message box, as well as vbQuestion icon. An If Statement analyses the user's response and it is Yes it runs the line of code that opens Windows Explorer displaying the appropriate folder. The new message box looks like this...

13

Programming Outlook 97
Outlook 97 doesn't have the VBA Editor that accompanies the other Office 97 programs. This means that you can't program Outlook 97 from within Outlook itself. But Outlook 97 has a VBA object model and, thanks to VBA Automation (the process by which different programs talk to each other through VBA), you can create a macro in Excel, Access or Word that uses the same code as described in this tutorial. A few small modifications are required:

Set a Reference to the Outlook Object Model

Although this is primarily intended for users of Office 97, the same method can be applied to Office 2000 and Office 2002 if you want to run the macro from within another program. Excel 97 or Word 97: Open the Visual Basic Editor in your chosen program and in the Project Explorer select the name of the file in which you want to create your macro (you might choose to use Personal.xls in Excel 97 or Normal.dot in Word 97). If necessary, add a code module as described above. Access 97: This version of Access does not have the same Visual Basic Editor as the other programs but everything else works the same way. Instead, click the Modules tab of the Access database window. If you want to place your code in an existing module, double-click it to open it. Otherwise click the New button to open a new code module (remember to save it when prompted). All Programs: Choose Tools > References to open the References dialog. Scroll down the list until you find Microsoft Outlook 8.0 Object Model and place a tick in the box next to it. Click the OK button. [Click the thumbnail below to see a full-sized image):

14

Setting a reference to the Outlook object model gives the current VBA Project access to Outlook's VBA library so that it understands and can check and execute your Outlook programming commands.

Add Commands to Communicate with Outlook

Enter an additional variable declaration at the beginning of the macro code to open a connection with Outlook:
Dim appOl As New Outlook.Application

Those variables which refer specifically to Outlook objects must now be edited so that the macro knows you are speaking to Outlook and not the host program:
Dim ns As Outlook.NameSpace Dim Inbox As Outlook.MAPIFolder Dim Atmt As Outlook.Attachment

The code assigning a value to the namespace variable needs to be modified:

Set ns = appOl.GetNamespace("MAPI")

Finally, an additional statement is required when clearing the memory:

Set appOl = Nothing

You can download a code module containing the entire macro code for Outlook 97 below. About Outlook Security Because of concerns about macro security, specifically from email viruses, various obstacles are put in the way of Outlook programmers. The macros described here will pose no threat to the security of your Outlook data and can be run on Outlook 97 with no further action. Outlook 2000 and 2002 however are equipped with various tools to monitor macro security.

15

Depending on your existing security settings, you may find that when you try and run your macro you see the following message:

You need to change the security settings to permit VBA code to be run. Choose Tools > Macro > Security to display the Security dialog. Set the security level to Medium and click OK.

After you do this, the first time in an Outlook session that you try to run a macro or open the Visual Basic Editor you see a warning message seeking your permission to run macros:

Choose Enable Macros to proceed. It is very unwise to set the security setting to Low! Download the Code

16

You can download a text file that can be imported into your host program as a ready-made code module. Each file contains two macros. One is the basic GetAttachments macro built in this tutorial. The other, named SaveAttachmentsToFolder contains examples of the enhancements described in the Additional Refinements section above. Click on one of the links below and save the file to disk. Your browser will probably warn you about the potential dangers of downloading files containing code (a wise precaution!). To install either code module, open the Visual Basic Editor and select VB Project into which you want to import the code. Choose File > Import (or right-click and choose Import) and locate and select the .bas file then click OK. A new module will be created.

17