Hey, Scripting Guy! How can I delete all the email sent from a specific person?

— VP

Hey, VP. Ah, yes, deleting email from a specific person; that’s something we Scripting Guys are all too familiar with. For example, the first time the Scripting Guys ever heard of such a thing was back in the early days of Microsoft Mail, when Bill Gates supposedly had a “bozo filter” installed on his machine that could automatically delete mail sent by a specified person or persons. (Of course, nowadays Outlook has also sorts of filters and rules built right into the product. But back then – or so the story goes – only Bill’s version of Microsoft Mail had this capability.)

We can’t say for sure that Bill had this bozo filter constructed specifically to delete mail sent from the Scripting Guys; when you consider the fact that the Scripting Guys didn’t even exist back then, well, that’s a reasonably good argument against that notion. On the other hand, there is the name they gave this contraption. Can you think of anyone who better fits the definition of “bozo” than the Scripting Guys? With that in mind, the Scripting Guys got together and developed a script that zips through our Inboxes and deletes any mail Bill sends to us. Does this script work? Let’s put it this way: you won’t find mail from Bill Gates in any of our Inboxes. No way.

In other words, if Bill Gates has been hounding you day and night with questions, business proposals, and what-have-you, well, here’s a script that will delete all that mail from your Inbox:

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(“[From] = ‘Bill Gates'”)
For i = colFilteredItems.Count to 1 Step -1
    colFilteredItems(i).Delete
Next

You know, that’s a very good question: how exactly does this script work? Well, we start out by defining a constant named olFolderInbox and setting the value to 6; we’ll use this constant to tell the script which Outlook folder we want to work with. Could we work with a different folder, or – even better – could we work with all the folders and subfolders in the Inbox? Sure; for an example of working with all the folders and subfolders in your Outlook Inbox take a look at this Hey, Scripting Guy! column.

After defining the constant we create an instance of the Outlook.Application object, and then use the GetNamespace method to bind to the MAPI namespace (which we need to do even though the MAPI namespace is the only namespace we can bind to). Once we’re bound to the MAPI namespace we can then use this line of code to connect to the Inbox:

Set objFolder = objNamespace.GetDefaultFolder(olFolderInbox)

After we’ve made the connection to the Inbox we could methodically walk through each and every mail message, checking to see if the message came from Bill Gates and, if it did, delete it. That works, but it’s a bit cumbersome. And if you’ve seen some of the Inboxes around here, be forewarned that a script that took that approach could also take forever to complete.

With that in mind we decided to do something a little fancier, and a little more efficient. To begin with, we use this line of code to grab a collection of all the items found in the Inbox:

Set colItems = objFolder.Items

Like we said, though, we aren’t going to start walking through this entire collection. Instead, we’re going to apply a filter of our own, one that restricts the collection to those messages sent by Bill Gates:

Set colFilteredItems = colItems.Restrict(“[From] = ‘Bill Gates'”)

As you can see, we simply call the Restrict method, indicating that we want to restrict the collection to items where the From property is equal to Bill Gates. Just like that, our collection will be filtered, and the only things left in colItems will be messages that were sent from Bill Gates.

Why do we use a filter? Well, for one thing, we now have a collection that contains only messages sent by Bill Gates; that means we don’t have to walk through all the items in the Inbox and laboriously check each message to see who sent it. Instead, we have a collection where we already know that each message was sent from Bill Gates. In turn, that means we can set up a For Next loop to walk through our filtered collection; each time through the loop we can then call the Delete method to delete the message:

For i = colFilteredItems.Count to 1 Step -1
    colFilteredItems(i).Delete
Next

Very observant of you: that is a somewhat unusual-looking For Next loop, isn’t it? With this loop we start with the very last item in the collection (which we can determine by checking the value of the Count property). We delete that item and then delete the next-to-last item in the collection, continuing in this vein until we finally delete the first item in the collection. (The Step -1 parameter tells the script to count backwards as it loops through the collection.)

Why don’t we just start at the beginning and then stop at the end? As it turns out, if you start deleting items from a collection Outlook can lose its place; that’s because, as items get deleted, the order of those items – and thus the nature of the collection – begins to change. For example, say you delete item 1. Obviously the next thing you’ll want to do is delete item 2. However, what used to be item 2 is now item 1 (because item 1 is gone). Consequently, you’ll end up deleting item 3 instead. See how that works? If you follow this all the way through, you’ll end up deleting only about half the items.

That’s right: we wanted to delete all the items, not just every other item. Fortunately we can do that as log as we start at the bottom and work our way up. With that approach, items don’t change position as other items are deleted; if you delete item 5, item 1 will still be item 1. As long as we delete items in inverse order – 5-4-3-2-1 – everything will be fine.

It’s weird, but it works. Just like the Scripting Guys.

Well, OK: except maybe for the part about working. But, hey, we must be doing something right; why else would they have named an email filter after us?