Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Welcome to Office Space, the column that offers tips and tricks for scripting Microsoft® Office applications. We’ll post new tips every Tuesday and Thursday; to see an archive of previous tips, visit the Office Space Archive. And if you have particular questions about Microsoft Office scripting, feel free to send them to scripter@microsoft.com (in English, if possible). We can’t promise to answer all the questions we receive, but we’ll do our best.

Saving All the Attachments in Your Inbox

One of the things that the Scripting Guys’ managers worry about most is this: how can we ensure that our columns can easily be distinguished from one another? (Yes, you would think that as managers of the Scripting Guys something like that would be the least of their concerns, wouldn’t you?) To tell you the truth, we Scripting Guys don’t lose a lot of sleep over this: our belief is that readers don’t care where they get their information from just as long as they get their information. Still, managers being managers, we do what we can to keep them happy and we ensure that each column follows its own format and its own subject matter.

Or at least we did until today. One of the ways that we keep our columns separate and distinct is this: Hey, Scripting Guy! is the column where we answer questions sent in by readers; the other columns are the columns where we make up the topics ourselves, without direct input from our readers. Keep this under your hat, but today we’re going to break the rules: today we’re going to use the Office Space column to address a reader question.

Shhh. We realize that this is shocking news, and we don’t want certain people to find out about it, if you catch our drift.

Actually, this is a question that has been sent in by a number of people, which is why we decided to answer it: is there a way to automatically save all the attachments to all the emails in your Outlook inbox? We can see why people are interested this, because Outlook’s user interface will allow you to save only a single attachment from a single email at a time. For example, when you select two or more items in Outlook the Save Attachments command gets grayed-out on the File menu:

Kind of a hassle, especially when your inbox is getting a bit full. One good way to clear it out would simply be to save all the attachments and then delete those emails. So is there a way to automatically save all the attachments to all the emails in your Outlook inbox? Lo and behold, there is:

Const olFolderInbox = 6

Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set objFolder = objNamespace.GetDefaultFolder(olFolderInbox)

Set colItems = objFolder.Items

For Each objMessage in colItems
    intCount = objMessage.Attachments.Count
    If intCount > 0 Then
        For i = 1 To intCount
            objMessage.Attachments.Item(i).SaveAsFile "C:\Temp\" &  _
                objMessage.Attachments.Item(i).FileName
        Next 
    End If
Next

This script begins by defining a constant named olFolderInbox and setting the value to 6; we’ll use this constant to indicate the Outlook folder we want to retrieve messages from. We create an instance of the Outlook.Application object and then use the GetNamespace method to connect to the MAPI namespace. (Incidentally, this is the only namespace you can connect to, but you still need to use this line of code). We then use the GetDefaultFolder method to bind to the Inbox folder; notice that we used the constant olFolderInbox, just like we said we would!

Note. This script assumes that Outlook is already up and running. If it’s not safe to make that assumption, take a look at this previous Office Space column, which shows you how to check to see if Outlook is running and, if it isn’t, starts the application for you.

We then use this line of code to create a collection consisting of all the items in the Inbox:

Set colItems = objFolder.Items

Note. Technically we don’t need to do this; we could simply reference objFolder.Items directly. However, creating a separate collection like this can be useful when doing things like filtering items in the Inbox. Because of that we went ahead and created an object reference to the Items collection just so you’d get used to doing that.

Next we set up a For Each loop to walk through the collection of messages. For each message in the collection we check the number of attachments; that’s what we do with this line of code:

intCount = objMessage.Attachments.Count

If intCount is greater than 0 that means there is at least one attachment for this message. With that in mind we set up a For Next loop that runs from 1 to the number of attachments; if we have 1 attachment our loop will run once (For i = 1 to 1) and if we have 5 attachments the loop will run 5 times (For i = 1 to 5).

What do we do inside this For Next loop? Actually we do just one thing: we call the SaveAsFile method in order to save each attachment. SaveAsFile requires a single parameter: the full path for the saved file. In our sample script we construct this path using two parts: C:\Temp\ (which happens to be the folder where we want to save all the attachments) and objMessage.Attachments.Item(i).FileName. This is the FileName property for the attachment. Suppose the file name for one of our attachments is Budget.xls. In that case, the path we pass to SaveAsFile will be C:\Temp\ plus Budget.xls, or a path of C:\Temp\Budget.xls.

As you probably figured out, objMessage.Attachments.Item(i) simply refers to an individual attachment within the Attachments collection for that message. The first time through the loop i will be equal to 1, which means we’ll be working with attachment 1 in the collection; the second time through the loop i will be equal to 2, so we’ll be working with the second attachment in the collection. Bear in mind that each email message has its own attachment collection (and the collection exists even if there are 0 items in the collection). In other words, we’re not working with the set of all attachments found in the Inbox; instead, we’re working with the set of all the messages found in the Inbox. And then for each individual message we work with that message’s attachment collection.

Yes, very cool. The one potential problem here is the fact that SaveFileAs neither checks for nor cares if a file already exists. Suppose your first email message has an attachment named Budget.xls. The script will dutifully save that attachment as C:\Temp\Budget.xls. Now suppose your next email message also has an attachment named Budget.xls. Without stopping or without asking, the script will simply replace the first Budget.xls with the second Budget.xls. That’s behavior built right into the method, and there’s no way to override it.

So we need to work around it. No doubt the best way to do that would be to use the FileSystemObject and check to see if a file exists before actually saving it; if the file does exist, your code could take some sort of action to ensure that file names are unique. We won’t take the time to do that today. Instead, we’ll show a quicker (albeit far less elegant) method of generating unique file names. In this revised script, we use the FileSystemObject and the GetTempName method to help create the file name. GetTempName generates random file names similar to this:

radE728B.tmp

In our revised script, we simply tack one of these random file names and an underscore to the front of the file name; that gives us path names similar to this:

C:\Temp\radE728B.tmp_budget.xls

If you’re concerned about duplicate file names, this is a quick and easy way to sidestep the problem.

Here’s the revised script:

Const olFolderInbox = 6

Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set objFolder = objNamespace.GetDefaultFolder(olFolderInbox)

Set colItems = objFolder.Items

Set objFSO = CreateObject("Scripting.FileSystemObject")

For Each objMessage in colItems
    intCount = objMessage.Attachments.Count
    If intCount > 0 Then
        For i = 1 To intCount
            strTempFile = objFSO.GetTempName
            objMessage.Attachments.Item(i).SaveAsFile "C:\Temp\" &  strTempFile & "_" & _
                objMessage.Attachments.Item(i).FileName
        Next 
    End If
Next

That should do it. Remember, if any manager-types ask, you read this in Hey, Scripting Guy!, not in Office Space. (As Scripting Guys we don’t mind breaking the rules, we just don’t like getting caught.)