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.

Filtering Email Messages in Microsoft Outlook

Despite the fact that we are the Microsoft Scripting Guys we don’t see scripting as being the answer to all of life’s problems. (Most of them, maybe, but not all of them.) In fact, when it comes to system administration we tend to believe that the quickest and easiest solution is almost always the best solution. More often than not we find it quicker and easier to write a script than to use a command line tool or the graphical user interface. However, that isn’t always the case, and if it’s quicker and easier to use the GUI than to write and use a script, well, we recommend you use the GUI. (Besides, that would make one less scripting column we’ll have to write!)

This is especially true when it comes to Microsoft Outlook. We haven’t written much about Outlook in Office Space, for two reasons: first, Outlook’s user interface is actually pretty good, and second, due in large part to the new security features that have been added to the program, Outlook scripts aren’t always faster and easier, and they can’t always be run in unattended fashion. If we’re going write about scripting against Microsoft Outlook, it will have to be because we’ve found an area where scripting outshines the user interface.

We think we’ve found just such an area when it comes to filtering email messages. It’s not unusual for people to want to grab all the email messages that fit certain criteria and then do something with them. For example, maybe you want to take all the messages sent by Ken Myer and move them from the Inbox to a different folder. Maybe you want to take all the messages with the subject Project Proposal and print them. Maybe you want to take all the messages that have been marked for follow-up and dump the text into a single Word document. You can do things like that via the user interface. Or, you can do things like that using a script no more complicated than this:

Const olFolderInbox = 6

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

Set colItems = objFolder.Items
Set colFilteredItems = colItems.Restrict("[Subject] = 'Project Proposal'")

For Each objMessage In colFilteredItems
    objMessage.PrintOut
Next

This script – which automatically prints all the emails with the subject line Project Proposal – begins by defining a constant named olFolderInbox, and then sets the value of this constant to 6; we’ll use this constant to tell the script which Outlook folder to bind to. Next we create an instance of the Outlook.Application object, and use the GetNamespace method to bind to the MAPI namespace (which happens to be the only namespace we can bind to). We then call the GetDefaultFolder method (passing our constant olFolderInbox as the only parameter), thus creating a connection to the Inbox.

All that gets us to the point where we can start to do something interesting. To begin with, we use this line of code to return a collection containing all the items in the Inbox:

Set colItems = objFolder.Items

With that collection in hand we can then call the Restrict method to filter out emails that don’t fit the specified criteria. Note that we aren’t deleting the emails from the Inbox, we’re just grabbing all the emails with the subject line Project Proposal and putting them in a new collection named colFilteredItems. We do this by enclosing the mail message property (Subject) in square brackets, adding an equal sign, and then enclosing the target subject line in single quotes. In other words:

Set colFilteredItems = colItems.Restrict("[Subject] = 'Project Proposal'")

What if we wanted only email messages from Ken Myer? No problem; we do the same sort of thing:

Set colFilteredItems = colItems.Restrict("[From] = 'Ken Myer'")

Again, we have the mail message property (From) in square brackets, an equal sign, and then the target subject line in single quotes.

How about mail items marked for follow-up? Well, if you check out the MailItem documentation in the Microsoft Outlook VBA Language Reference, you’ll see that FlagStatus property is an integer value; further investigation will tell you that a 2 indicates that a message has been marked for follow-up. (Hint: Look up the enumeration values for the constant olFlagStatus.) Thus our code would look like this:

Set colFilteredItems = colItems.Restrict("[FlagStatus] = 2")

Notice that we don’t enclose the value 2 in single quotes. Single quotes are used for string values; you don’t need them when working with numeric values or with Boolean values. For example, this code returns a collection of unread messages:

Set colFilteredItems = colItems.Restrict("[Unread] = True")

Neat, huh? Generally speaking, if it’s a property of the MailItem object then you can filter on it. You can also combine filter requirements. For example, suppose you want only those unread messages that came from Ken Myer. Here you go:

Set colFilteredItems = colItems.Restrict("[Unread] = True AND [From] = 'Ken Myer'")

In this case we have two separate filters: [Unread] = True and [From] = 'Ken Myer'. All we’ve done is combine those two filters using the AND operator.

Or how about emails that came from either Ken Myer or Pilar Ackerman:

Set colFilteredItems = colItems.Restrict("[From] = 'Ken Myer' OR [From] = 'Pilar Ackerman'")

You get the idea.

What’s cool, of course, is not only the ability to return a targeted set of emails, but to then do something with that entire set. In our sample script, we use the PrintOut method to print each email in the collection:

For Each objMessage In colFilteredItems
    objMessage.PrintOut
Next

You could just as easily dump each message into a Word document or a database. Alternatively, you might want to display each message in the command window. That’s as simple as echoing back the value of the Body property:

For Each objMessage In colFilteredItems
    Wscript.Echo objMessage.Body
Next

Just like that, you now have a quick and easy way to read related emails from the command line.

Like we said, this is one time where we find it quicker and easier to use a script than to use Outlook’s user interface. (And thank goodness we found yet another place where scripting really shines: after all, we’re not real keen on becoming the Microsoft Unemployed Guys.) We can’t guarantee that the scripts we showed you today represent the answers to all of life’s problems. But, then again, we can’t guarantee that they aren’t the answers to all of life’s problems, either. Hey, you never know….