Hey, Scripting Guy! We have a lot of users who let unread email messages pile up in their Outlook Inboxes; after awhile, that causes their Inboxes to fill up. How can I write a script users could run that would delete any unread messages that are more than 6 months old?

— ST

Hey, ST. You know, sometimes it’s tough being a Scripting Guy. After all, people don’t come to the Script Center just hoping to pick up a tip or two about system administration scripting; instead, people come to the Script Center hoping to be uplifted and inspired. Even well-known TV personalities like Oprah Winfrey and Dr. Phil don’t bother trying to help people anymore; instead, they simply say, “Listen, you want to be uplifted and inspired? Then read the Hey, Scripting Guy! column every morning.”

Now, as a general rule, the Scripting Guys welcome the responsibility of spreading joy and happiness throughout the world. However, fulfilling their roles as the Happiness Fairies can be difficult at times, especially during a week like this one. What’s so bad about this week? Boy, where do we start? For one thing, the Scripting Guy who writes this column started the week off with a car that was broken and could never be fixed; saw that change to a car that could be fixed simply by replacing a $1.25 part; then ended the week with a car that was broken and could never be fixed.

Meanwhile, Scripting Guy Jean Ross made arrangements to have a few things taken care of, the only stipulation being that everything had to be taken care of by July 3^rd. No problem, she was told; everything will be done by July 3^rd. When she went in to sign the final contract they told her, “Here you go, ma’am. Everything will be done by July 13^th, just like you asked.”

Let’s see, what else …. Well, TechNet is planning to make some changes to the publishing process, changes that will make the Scripting Guys’ work life much … better …. (Apparently TechNet didn’t think it was challenging enough for a two-person team to write, publish, and maintain the entire Script Center. Therefore they decided to up the ante a little.) As for the icing on the cake, we’re also in the midst of performance reviews here at Microsoft. We’re probably not supposed to share this information, but here’s an excerpt from last year’s performance review for the Scripting Guy who writes this column:

Too bad for him that they don’t have a category for consistency. That he has.

But hey, you didn’t come to the Script Center to listen to the Scripting Guys bemoan their fates, did you? Instead, you came here because Oprah Winfrey and Dr. Phil promised that the Scripting Guys would uplift and inspire you. OK, let’s see what we can do about that … uplifting and inspiring … hmmm …

Oh, we know: Suppose we show you a script that can delete all the unread messages in your Outlook Inbox that are more than 6 months old? You know, a script like this one:

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(“[UnRead] = True”)
For i = colFilteredItems.Count to 1 Step – 1
    If DateDiff(“m”, colFilteredItems(i).ReceivedTime, Now) > 6 Then
        colFilteredItems(i).Delete
    End If
Next

Feel better? Good; after all, your happiness is the only thing that matters to the Scripting Guys. Of course, you’d probably feel even better if you understood how this script works. Very well; let’s see if the two Happiness Fairies can help you with that.

To begin with, we Happiness Fairies start out by defining a constant named olFolderInbox, then assign olFolderInbox the value 6; we’ll use this constant to tell the script which Outlook folder to work with. After defining the constant we create an instance of the Outlook.Application object, then use the GetNamespace method to connect to the MAPI namespace. (A required step, even though the MAPI namespace is the only namespace we can connect to.) Finally, we use the GetDefaultFolder method to bind to the Outlook Inbox:

Set objFolder = objNamespace.GetDefaultFolder(olFolderInbox)

Now we’re ready to roll. With the connection complete we use the following line of code to retrieve a collection of all the items (that is, all the email messages) found in the Inbox folder:

Set colItems = objFolder.Items

That’s a good point: we’re really only interested in unread emails, aren’t we? That’s fine; all we have to do is apply a filter and create a “sub-collection” (named colFilteredItems) that contains only the unread emails found in the Inbox folder:

Set colFilteredItems = colItems.Restrict(“[UnRead] = True”)

We’re not going to discuss email filtering in any detail today; if you’d like to know more about filtering email take a look at our Office Space article on that very subject. About all we’ll say here is that we’re limiting our collection to items where the Unread property is True; as you might expect, if the Unread property is True that means that the message hasn’t been read yet.

Our next step is to loop through the entire collection of unread messages and then delete any emails more than 6 months old. In theory, we could have expanded our filter so that we limited the sub-collection to messages that were both unread and more than 6 months old. We didn’t bother with that simply because we were afraid the filter would become unduly complicated. Instead, we’re going to loop through all the unread messages, checking each one to see if it’s more than 6 months old. If it is, we’ll delete it. If it’s not, we won’t.

Just exactly the way Dr. Phil would do things.

Because we’re going to be deleting items we need to start our loop at the bottom (that is, with the very last email in the collection) and then work our way to the top (the very first email in the collection). That’s why we have a For Next loop that starts with the last item (whose index number can be determined by using the Count property) and works its way towards the first item, the one with the index number 1:

For i = colFilteredItems.Count to 1 Step -1

What’s that? You say you have two questions? Look, we don’t really feel like answering a bunch of – no, sorry. After all, we are the Happiness Fairies; we have a job to do. With that in mind, we’d be thrilled to answer your questions. As to the first question (“Why the Step -1 parameter?”), well, that’s easy: Step -1 is the key to doing a “backwards” loop. Let’s say we have 100 items in our collection. In that case, the counter variable i starts out equal to 100, and the first time through the loop we’ll be working with item 100. When we’re done with that item we need to turn our attention to item 99. How do we get from item 100 to item 99? That’s right: by subtracting 1 from the current value of our counter variable. That’s what Step -1 is for.

As for your other question (“Why does deleting items require us to start at the bottom and work our way up?”), well, take a peek at this Hey, Scripting Guy! column for an explanation.

As we noted, inside the loop we need to take a look at each email and determine whether the message is more than 6 months old. That’s what this line of code is for:

If DateDiff(“m”, colFilteredItems(i).ReceivedTime, Now) > 6 Then

So what are we actually doing with that line of code? Well, we’re using VBScript’s DateDiff function to determine the number of months (the “m” parameter) between the date and time the message was received (colFilteredItems(i).ReceivedTime) and the current date and time (Now). If the number of months is greater than 6 we then use this line of code to delete the message:

colFilteredItems(i).Delete

And if the number of months is not greater than 6 then we don’t do anything at all.

Which, as our performances reviews suggest, is what we do best.

From there we loop around and repeat the process with the next email in the collection. By the time we exit the loop we’ll have deleted all the unread emails that are more than 6 months old.

At any rate, we hope that you found today’s column uplifting and inspiring, ST. If not, here’s something that might help. Remember, no matter how bleak things might look, they could be worse: you could be a Scripting Guy.