How Can I Determine the Follow-Up Status of Outlook Emails?

ScriptingGuy1

Hey, Scripting Guy! How can I go through all the emails in an Outlook folder and determine which messages are flagged for follow-up and which messages were flagged for follow-up but are now complete?

— KF

Hey, KF. We’ll be honest with you: we’re going to try to get to your question today, but first we’ve got something far more important to do. Yesterday when he got home the Scripting Guy who writes this column found a letter in his mailbox from the Cambridge Who’s Who. This letter informed him that he was “being considered for inclusion in the 2007/2008 Cambridge Who’s Who Among Executives and Professionals ‘Honors Edition’ of the Registry.”

Was the Scripting Guy who writes this column excited by this? That’s putting it mildly; as the letter notes “Inclusion is considered by many as the single highest mark of achievement.” You can count the Scripting Guy who writes this column as being one of those people who consider this their single highest mark of achievement. And no, that isn’t because he doesn’t actually have any other achievements. He doesn’t, but that’s not why he’s so excited.

Hold on a second here. You know, now that we look at this a little more closely we realize that there’s a string attached: to actually be included in the Registry itself you need to fill out an application form, indicating such things as the company you work for, your job title, and your specialty. Uh-oh; apparently you don’t get this honor unless you’ve actually done something to deserve it. Is that a problem? You bet it is. After all, we were hoping this was like the Nobel Prize for Literature which, as near as we can tell, is awarded simply by drawing names out of a hat.

So what exactly is the Scripting Guy who writes this column’s specialty? To tell you the truth, we were hoping you’d tell us. But let’s see, he can … um … he’s good at … he …. Oh, wait we know: he can write scripts that can go through all the emails in an Outlook folder and determine which messages are flagged for follow-up and which messages were flagged for follow-up but are now complete:

Const olFolderInbox = 6

Set objOutlook = CreateObject(“Outlook.Application”)
Set objNamespace = objOutlook.GetNamespace(“MAPI”)
Set objFolder = objNamespace.GetDefaultFolder(olFolderInbox)
Set colItems = objFolder.Items
For Each objItem in colItems
    If objItem.FlagStatus = 1 Then
        Wscript.Echo “Follow-up complete”
        Wscript.Echo objItem.Subject 
        Wscript.Echo
    End If
    If objItem.FlagStatus = 2 Then
        Wscript.Echo “Marked for follow-up”
        Wscript.Echo objItem.Subject 
        Wscript.Echo
    End If
Next

What’s that? You’d like to know how this script actually works? Well, explaining things isn’t exactly our specialty, but we’ll see what we can do.

We start out by defining a constant named olFolderInbox and setting the value to 6; that’s going to tell our script which folder we want to work with. And that folder is – oh, that’s right: the Inbox folder. Apparently the constant name was a bit of a giveaway, wasn’t it?

Oh, well; so much for the suspense, huh? (Fortunately writing stuff that has no suspense and no plot will actually help our chances of getting the Nobel Prize for Literature.) After defining the constant olFolderInbox we then use these three lines of code to create an instance of the Outlook.Application object, connect to the MAPI namespace, and bind to the Inbox folder:

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

Once we’ve done that we then use this line of code to return a collection of all the mail messages found in that folder:

Set colItems = objFolder.Items

At this point there are at least two different paths we can take: we can write a filter to weed out messages that don’t have any follow-up flag setting, or we can simply loop through the entire collection and check the flag setting for each and every item. Because people tend to find Outlook filters confusing we’ve opted to loop through the entire collection. But if you’re willing to tackle filter writing you’ll find some information about that very thing in this Office Space article.

And no, we can’t guarantee that writing an Outlook filter script will get you included in the Registry alongside the Scripting Guy who writes this column. But it can’t hurt.

Seeing as how we’ve decided to loop through the entire collection our next step is to set up a For Each loop that, well, loops through the entire collection. The first thing we see inside that loop is a block of code that looks like this:

If objItem.FlagStatus = 1 Then
    Wscript.Echo “Follow-up complete”
    Wscript.Echo objItem.Subject 
    Wscript.Echo
End If

What we’re doing here is examining the value of the FlagStatus property. If FlagStatus is equal to 1 that means that this particular email was originally marked for follow-up but the follow-up is now complete. (In Outlook itself you’ll see a little checkmark in the status column.) Therefore, we echo back the fact that follow-up is complete; in addition, we echo back the email Subject line (just so we can tell which message has been marked as follow-up complete).

Good question: what about emails that are still marked for follow-up? Well, that’s what this block of code is for:

If objItem.FlagStatus = 2 Then
    Wscript.Echo “Marked for follow-up”
    Wscript.Echo objItem.Subject 
    Wscript.Echo
End If

Here we’re checking to see if the value of FlagStatus is equal to 2; it probably goes without saying that this represents an email still marked for follow-up. In a case like that we then echo back the appropriate message (Marked for follow-up) as well as the subject line.

And then, of course, we loop around and repeat the process with the next message in the Inbox. What if we encounter an email that has no follow-up status of any kind (neither complete nor pending)? That’s easy. That means that the FlagStatus for the message is equal to 0, which in turn means that we simply skip that particular email.

Incidentally, there are a couple of other follow-up related properties you might be interested in. For example, if you’d like to know the actual color of the flag assigned to a message use the FlagIcon property and one of these values:

And if you’d like to know when you’re supposed to follow-up a message you can look at the value of the FlagDueBy property. (If no due date has been assigned then this property defaults to 1/1/4501, which probably still isn’t enough time for the Scripting Guy who writes this column to follow-up on all the items he has marked for follow-up.) As an added bonus, all these properties are read/write, which means you can programmatically change the follow-up status, icon, or due date. Pretty cool, huh?

OK; so much for that. We’re willing to bet that some of you are skeptical about this whole Who’s Who thing. “Is the Scripting Guy who writes that column really among the ‘country’s most accomplish professionals,’” you’re wondering, “or is his name simply on some mailing list somewhere?” To tell you the truth, we’re not worried about that. After all, when you’ve earned as many honors and awards as the Scripting Guy who writes this column (0, unless you count making the Little League and PONY League All-Star teams), well ….

Hey, wait a second: why don’t you count making the Little League and PONY League All-Star teams? That’s way more than Bill Gates ever did.

Well, baseball-wise, anyway.

ScriptingGuy1

Follow