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 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.

Tallying Mail Messages Using Microsoft Outlook

Computers are famous for being “number crunchers,” even though few of us ever take full advantage of those arithmetical capabilities. That’s true even when we have numbers we’d really like to crunch. Take Microsoft Outlook, for example. If you wander through the hallways (as the Scripting Guys are wont to do), you’re likely to hear laments like these:

“It seems like I have twice as many meetings in October as I do in any other month.”

“I’d say that the vast majority of my contacts work at Fabrikam. Or maybe Contoso. I don’t really know.”

“Tuesdays are no good: for some reason I get tons of email every Tuesday. Or at least I think I do.”

Sounds like a job for a number-cruncher.

As you know, there’s a huge storehouse of information stashed in Microsoft Outlook; the only problem is that there’s no obvious way to get at that information. Until now, that is. In today’s column, we’re going to show you a very simple method for tallying up items in an Outlook folder. What we’ll do is count up the number of emails sent to you per day. That might not be of paramount importance to you, but remember, once you understand the basic idea you can take this sample script and modify it to do just about anything:

• Go through the Sent Items folder and tally the number of emails you send per day.

• Go through your Inbox and tally up emails by sender name or by subject.

• Go through the Calendar and count up the number of appointments you have by week or by month.

• Go through all your Outlook folders and tally the total number of emails you’ve received, and when.

Etc.

For today, however, we’ll keep things simple: all we’re going to do is grab all the messages in the Inbox and tally them up by day (that is, how many messages did you get on October 10th, how many on October 9th, etc.). Keep in mind that we’re working with messages that are actually in the Inbox. If you received email on October 10th and then deleted those messages or moved them to another folder, well, this script can’t account for that. We can only work with items still in the Inbox.

Here’s the code:

Const olFolderInbox = 6

Set objDictionary = CreateObject("Scripting.Dictionary")

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

Set colItems = objFolder.Items

For Each objItem in colItems
    strDate = FormatDateTime(objItem.SentOn, vbShortDate)
    If objDictionary.Exists(strDate) Then
        objDictionary.Item(strDate) = objDictionary.Item(strDate) + 1
    Else
        objDictionary.Add strDate, "1"
    End If
Next

colKeys = objDictionary.Keys

For Each strKey in colKeys
    Wscript.Echo strKey, objDictionary.Item(strKey)
Next

So how does this script work? Well, it starts off simply enough: we begin by defining a constant named olFolderInbox and setting the value to 6; we’ll employ this baby when we tell Outlook which folder we want to work with. Next we create an instance of the Dictionary object; this is the object we’ll use to keep a running tally of the number of email messages sent per date.

In case you’re wondering, the Dictionary object is a handy way to track key-item pairs: you supply the Dictionary with a set of keys (such as dates) as well as a corresponding set of items (such as the number of email messages sent to you on those dates). For the most part the Dictionary object is just an array, but it has at least one added bonus: you can very quickly and very easily check to see if a particular key exists. If we have a date like 10/10/2005, we can use the Exists method to see if a key by that name is already in the Dictionary. By contrast, with an array we’d have to loop through all the values in that array checking to see if any of those values were equal to 10/10/2005. And then do the same thing for the next date. And the next. And ….

We realize that’s a very cursory overview of the Dictionary object, but you should get a better idea of how it works as we go along. You can also check out this section in the Microsoft Windows 2000 Scripting Guide for a little background information about the Dictionary object.

Now, where were we? Oh, right: the script. After we create our Dictionary object we use the following lines of code to create an instance of the Outlook.Application object, bind to the MAPI namespace, and then open the Inbox folder:

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

That allows us to then call this line of code, which retrieves a collection of all the items in the Inbox:

Set colItems = objFolder.Items

Now the fun begins.

Well, OK, it has been pretty fun up to this point, hasn’t it? But now it’s going to get even better.

We start off by setting up a For Each loop to walk through the collection of items (i.e., mail messages) found in the Inbox. That brings us to this line of code:

strDate = FormatDateTime(objItem.SentOn, vbShortDate)

The SentOn property of an email message represents the date and time that the message was sent. Of course, in this script we’re interested only in the date: we want to know how many emails were sent to us on 10/10/2005, and we don’t care what time those messages were sent. Therefore, we use the FormatDateTime function, passing it the value of the SentOn property and the VBScript constant vbShortDate. What does that do? That will chop the time off our SentOn value, turning a value like 10/10/2005 1:47 PM into 10/10/2005. And that value we store in a variable named strDate.

Now we’re ready to start working with the Dictionary object. First, we use the Exists method to determine whether the Dictionary already contains a key equal to the value of strDate (for example, 10/10/2005):

If objDictionary.Exists(strDate) Then

If the Exists method returns a value of False, that means no such key exists. In that case, we execute this line of code in our Else statement:

objDictionary.Add strDate, "1"

What we do here is call the Dictionary object’s Add method, and pass it two parameters: the variable strDate (which means we’ll be adding a key equal to the value of strDate) and a 1. Why a 1? Well, this is the first message we’ve found that was sent on 10/10/2005, and we’re using the item part of the key-item pair to keep track of the total number of messages sent to us each day. So far, that’s 1.

But what happens if the Exists method returns a value of True? That can only mean one thing: a key equal to the value of strDate already exists in the Dictionary. (By the way, keys must be unique for a given Dictionary object.) In other words, we’ve already found at least one message that was sent on 10/10/2005, and we recorded that fact in the Dictionary. Now we have another message sent to us on 10/10/2005. Therefore, we use this line of code to increment the total number of messages sent to us that day:

objDictionary.Item(strDate) = objDictionary.Item(strDate) + 1

All we’re doing is binding to the item that goes with the specified key, then adding 1 to it. Pretty simple, huh?

And that’s it. We then loop around and repeat the process for the next message in the Inbox.

After we’ve tallied up all the dates and the number of emails sent to us on those dates, we then echo back the results using this block of code:

colKeys = objDictionary.Keys

For Each strKey in colKeys
    Wscript.Echo strKey, objDictionary.Item(strKey)
Next

This is also pretty basic stuff: we’re just walking through the collection of Dictionary keys. For each key in the collection we echo back the value of both the key and the item. (The key, remember, is the date, and the item is the number of messages sent to us on that date.) That’s going to give us output similar to this:

10/6/2005 2
10/7/2005 2
10/8/2005 5
10/9/2005 21
10/10/2005 1
10/11/2005 1
10/12/2005 4

As you can see, for some reason October 9th was an especially busy day. Remind us to take next October 9th off.

Like we said, this script does not work in real-time: it doesn’t keep track each time you receive an email and then update the tally accordingly. (Is that possible? Well, it’s not impossible, but it’s obviously a bit more complicated, to say the least.) Instead, we’re simply tallying up all the mail messages still sitting in your Inbox. If you delete your email after you read it a script like this won’t do you much good. But if you have Inboxes similar to some of those we’ve seen around Microsoft (that is, Inboxes with tens of thousands of messages stuffed in them), well, in that case you might find this script a bit more useful. (Editor’s Note: Hey, some of us really need those 24 messages sent to us on 5/18/2001.)

So there you have it: number-crunching, the third best crunch in the world (trailing only Nestlé’s and Captain.)