Macro 90: Saving All Attachments to a Folder
You may often find that certain processes lend themselves to the exchange of data via e-mail. For
example, you may send a budget template out for each branch manager to fill out and send back to
you via e-mail. Well, if there are 150 branch members, it could be a bit of a pain to bring down all
those e-mail attachments.
The following procedure demonstrates one solution to this problem. In this procedure, we use auto-
mation to search for all attachments in the inbox and save them to a specified folder.
How it works
Because this code will be run from Excel, we need to set a reference to the Microsoft Outlook Object
Library. You can set the reference by opening the Visual Basic Editor in Excel and selecting Tools➜
References. Scroll down until you find the entry Microsoft Outlook XX Object Library, where the XX is
your version of Outlook. Select the check box next to the entry.
Sub Macro90()
‘Step 1: Declare our variables
Dim ns As Namespace
Dim MyInbox As MAPIFolder
Dim MItem As MailItem
Dim Atmt As Attachment
Dim FileName As String
‘Step 2: Set a reference to our inbox
Set ns = GetNamespace(“MAPI”)
Set MyInbox = ns.GetDefaultFolder(olFolderInbox)
‘Step 3: Check for messages in our inbox; exit if none
If MyInbox.Items.Count = 0 Then
MsgBox “No messages in folder.”
Exit Sub
End If
‘Step 4: Create directory to hold attachments
On Error Resume Next
MkDir “C:\Temp\MyAttachments\”
‘Step 5: Start to loop through each mail item
For Each MItem In MyInbox.Items
‘Step 6: Save each attachment then go to the next attachment
For Each Atmt In MItem.Attachments
FileName = “C:\Temp\MyAttachments\” & Atmt.FileName
Atmt.SaveAsFile FileName
Next Atmt
‘Step 7: Move to the next mail item
Next MItem
‘Step 8: Memory cleanup
Set ns = Nothing
Set MyInbox = Nothing
End Sub
1. Step 1 declares five variables. ns is an object used to expose the MAPI namespace. MyInbox
is used to expose the target mail folder. MItem is used to expose the properties of a mail
item. Atmt is an object variable that holds an Attachment object. FileName is a string vari-
able that holds the name of the attachment.
2. Step 2 sets the MyInbox variable to point to the inbox for the default mail client.
3. Step 3 performs a quick check to make sure there are actually messages in the inbox. If there
are no messages, the macro exits the procedure with a message box stating that there are no
messages.
4. Step 4 creates a directory to hold the attachments we find. Although you could use an exist-
ing directory, using a directory dedicated specifically for the attachments you bring down is
usually best. Here, we are creating that directory on the fly. Note we are using On Error
Resume Next. This ensures that the code does not error out if the directory we are trying
to create already exists.
5. Step 5 starts the loop through each mail item in the target mail folder.
6. Step 6 ensures that each mail item we loop through gets checked for attachments. As we
loop, we save each attachment we find into the specified directory we created.
7. Step 7 loops back to Step 5 until there are no more mail items to go through.
8. Releasing the objects assigned to our variables is good general practice. This reduces the
chance of any problems caused by rogue objects that may remain open in memory. Step 8
simply sets the variable to Nothing.
How to use it
To implement this macro, we can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code into the newly created module.
