April 3rd, 2008

Hey, Scripting Guy! How Can I Sort Items Retrieved From a Microsoft Outlook Folder?

Hey, Scripting Guy! Question

Hey, Scripting Guy! I’m trying to write a script that does something with all the email messages in my Outlook Inbox. However, I need to sort those messages by the date they were received, and I can’t figure out how to do that. Can you help?
— SL

SpacerHey, Scripting Guy! AnswerScript Center

Hey, SL. You know, no doubt a lot of you who read this column have thought, “The Scripting Guy who writes that column: I bet he’s a Red. He just seems like a Red to me.” Well, we have good news for you: you were absolutely right, the Scripting Guy who writes this column is a Red. Or, to be a little more precise, red and yellow are his dominant energies, with green and blue his inclined energies.

And yes, now that you see it in print it all seems so obvious, doesn’t it?

So what does all this dominant and inclined energy stuff mean? Well, to be honest, the Scripting Guy who writes this column has no idea. Recently the Scripting Guys’ larger team took part in an Insights Discovery session put on by Insights. Unfortunately, the Scripting Guy who writes this column had an important meeting with TechNet that same day and was thus unable to attend the session. Because he didn’t make the session he knows only that he’s a Fiery Red (as opposed to a Cool Blue, Earth Green, or Sunshine Yellow); for better or worse, he has no idea what that actually means.

Note. What’s that? If he hadn’t had the TechNet meeting would the Scripting Guy who writes this column have attended the session? Um, that’s such a silly question we aren’t even going to dignify it with a response.

In addition to being a Fiery Red, the Scripting Guy who writes this column is a Motivator (Orange) on the 72 Type Wheel System. And before you ask, no, he doesn’t know what that means, either. He does know, however, that the 72 Type Wheel system has 72 discrete sections that offer “an infinite number of colour combinations to represent the uniqueness of each individual.” Admittedly, the Scripting Guy who writes this column isn’t totally sure how you can take Red, Blue, Yellow, and Green and create an infinite number of color combinations. But, then again, Fiery Reds aren’t really known for their math skills. We tend to be people persons, if you know what we mean.

Speaking of people, it turns out that Scripting Guy Jean Ross is a Blue, with blue and red representing her dominant energies and green and yellow representing her inclined energies.

Oh, come on, don’t laugh: she can’t help being who she is.

Meanwhile, Scripting Guy Dean Tsaltas is a Burnt Umber, which is noteworthy for the fact that Burnt Umber isn’t even one of the colors that you can be. “We don’t understand it, either,” said Ken Myer, spokesperson for Insights. “But every time we ran his profile it came out Burnt Umber.”

Go figure.

At any rate, according to the brochure a Fiery Red is “Competitive, demanding, determined, strong-willed, and purposeful.” As you can see, these are all outstanding personality traits, and the Scripting Guy who writes this column is glad to see scientific acknowledgment of the fact that he’s just one heck of a guy. By contrast, a Cool Blue, like Scripting Guy Jean Ross, is “dull and boring, and should always defer to a Fiery Red when it comes to work-related issues.”

Or at least we assume that’s what a Cool Blue is. We didn’t really have time to read the entire brochure.

Incidentally, Fiery Reds are also known for their ability to write scripts that can sort the items found in a Microsoft Outlook folder. You know, scripts like the following, which returns a collection of items found in the Inbox, sorted (in descending order) by the date that the item was received:

On Error Resume Next

Const olFolderInbox = 6

Set objOutlook = CreateObject("Outlook.Application")

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

Set colItems = objFolder.Items
colItems.Sort "ReceivedTime", True

For Each objItem in colItems
    Wscript.Echo objItem.ReceivedTime
Next

So do we have any idea how this script works? Tut-tut; did you forget that the Scripting Guy who writes this column is a Fiery Red? To begin with, we define a constant named olFolderInbox and set the value of that constant to 6; we’ll use olFolderInbox to tell the script which Outlook folder we want to work with. After defining the constant we create an instance of the Outlook.Application object, then use these two lines of code to bind to the MAPI namespace (the only namespace we can bind to) and then to the Inbox folder:

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

As soon as we’ve made a connection to the Inbox we can go ahead and retrieve a collection of all the mail messages found there; that can be done simply by creating an object reference to the folder’s Items collection:

Set colItems = objFolder.Items

And now comes the really cool part:

colItems.Sort "ReceivedTime", True

Uh, actually, that was the really cool part, the part where we sort the returned collection – in descending order – by the date that the message was received. To do that we simply call the Sort method followed by two parameters: 1) ReceivedTime, the name of the property we want to sort by; and, 2) True, which tells the script to sort the collection in descending order. What if we wanted to sort the collection in ascending order? No problem; just leave off the second parameter:

colItems.Sort "ReceivedTime"

And what if we wanted to sort the collection, in descending order, by, say, the Subject line? That’s fine; just sort by the Subject property, and include that optional second parameter:

colItems.Sort "Subject", True

Etc., etc.

Note. What other property values can you sort by? You must be a Sunshine Yellow, aren’t you? But that’s fine; we have no problem with Sunshine Yellows. (Earth Greens, of course, are a different story.) Well, Sunshine Yellow, with a few exceptions you can sort your collection by any of the properties of Outlook’s MailItem object.

All we have to do now is see if this actually works. To do that, we set up a For Each loop designed to walk us through each item in the Inbox collection:

For Each objItem in colItems

And what can we do inside this loop? Pretty much anything we want. For today’s sample script, all we do is echo back the value of each item’s ReceivedTime property:

Wscript.Echo objItem.ReceivedTime

That results in output similar to this:

4/1/2008 8:11:23 AM
4/1/2008 8:00:12 AM
4/1/2008 7:59:16 AM
4/1/2008 7:44:47 AM
4/1/2008 7:43:26 AM
4/1/2008 7:33:39 AM
3/31/2008 4:17:42 PM
3/31/2008 12:55:12 PM

Pretty cool, huh? And, again, to sort in ascending order just leave off the second parameter.

So, in retrospect, does the Scripting Guy who writes this column wish that he had been able to make it to the Insights Discovery session? To be honest, no; for him it would have been a waste of time. After all, Insights Discovery is designed to help you become a better teammate, to help you better communicate with your co-workers, to help you learn to value and respect other people and their opinions. Needless to say, the Scripting Guy who writes this column has already mastered all of these things; no one is more sensitive to the needs and feelings if his co-workers than he is.

And if you don’t believe that, just ask the Scripting Editor; she’ll tell you what a kind-hearted and sensitive soul he truly is. Although you might have to speak slowly and avoid using big words; after all, she is a Cool Blue, you know.

Just like we said: sensitive to the unique needs of his co-workers.

Author

0 comments

Discussion are closed.