Macro 91: Saving Certain Attachments to a Folder
In the previous procedure, we showed you how to use automation to search for all attachments in your
inbox and save them to a specified folder. However, in most situations, you probably only want to save
certain attachments; for example, those attachments attached to e-mails that contain a certain Subject.
In this example, we get a demonstration of how to check for certain syntax and selectively bring down
attachments.
How it works
Because this code will be run from Excel, we need to set a reference to the Microsoft Outlook Object
Library. We can set the reference by opening the Visual Basic Editor in Excel and selecting
Tools➜References. Scroll down until we 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 Macro91()
‘Step 1: Declare our variables
Dim ns As Namespace
Dim MyInbox As MAPIFolder
Dim MItem As Object
Dim Atmt As Attachment
Dim FileName As String
Dim i As Integer
‘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:\OffTheGrid\MyAttachments\”
‘Step 5: Start to loop through each mail item
For Each MItem In MyInbox.Items
‘Step 6: Check for the words Data Submission in Subject line
If InStr(1, MItem.Subject, “Data Submission”) < 1 Then
GoTo SkipIt
End If
‘Step 7: Save each with a log number; go to the next attachment
i = 0
For Each Atmt In MItem.Attachments
FileName = _
“C:\Temp\MyAttachments\Attachment-” & i & “-” &
Atmt.FileName Atmt.SaveAsFile FileName
i = i + 1
Next Atmt
‘Step 8: Move to the next mail item
SkipIt:
Next MItem
‘Step 9: Memory cleanup
Set ns = Nothing
Set MyInbox = Nothing
End Sub
1. Step 1 first declares six 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 variable that holds the name of the attachment. i is an integer variable used to
ensure each attachment is saved as a unique name.
2. Step 2 sets the MyInbox variable to point to the inbox for our default mail client.
3. Step 3 performs a quick check to make sure there are actually messages in our inbox. If there
are no messages, it exits the procedure with a message box stating that there are no mes-
sages.
4. Step 4 creates a directory to hold the attachments we find. Note that it uses 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. In Step 6, we use the Instr function to check whether the string Data Submission is in
the Subject line of the e-mail. If that string does not exist, we don’t care about any attach-
ments to that message. Therefore, we force the code to go to the SkipIt reference (in Step
8). Because the line of code immediately following the SkipIt reference is essentially a
Move Next command, this has the effect of telling the procedure to move to the next mail
item.
7. Step 7 loops through and saves each attachment into the specified directory we created.
Note that we are adding a running integer to the name of each attachment. This is to ensure
that each attachment is saved as a unique name, helping us to avoid overwriting attach-
ments.
8. Step 8 loops back to Step 5 until there are no more mail items to go through.
9. Releasing the objects assigned to our variables is generally good practice. This reduces
the chance of any problems caused by rouge objects that may remain open in memory.
In Step 9, we simply set 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.
