Use Filters to speed up Outlook macros

If you find that your Outlook macros run slow, your code may benefit from using filters to limit the number of items being processed by a loop.

According to the Visual Basic Help in Outlook, filtering works best using "needle-haystack" setups, where there are large numbers of emails with only a few emails as the target.

Instead of looping through all emails in a folder, filtering down to only emails that match a specific criteria can make your loops more efficient. Here are some examples that show how we can use the Items.Restrict Method to do so.

Helper Functions

The following functions are used below in various capacities. They should be pasted into a standard module in the Outlook VBA project, so they can be used throughout the project.

Function GetOutlookApp() As Outlook.Application
' returns reference to native Application object
  Set GetOutlookApp = Outlook.Application
End Function
Function GetNS(ByRef app As Outlook.Application) As Outlook.NameSpace
  Set GetNS = app.GetNamespace("MAPI")
End Function
Function GetItems(olNS As Outlook.NameSpace, _
    folder As OlDefaultFolders) As Outlook.Items
  Set GetItems = olNS.GetDefaultFolder(folder).Items
End Function
Function QuoteWrap(stringToWrap As String, _
    Optional charToUse As Long = 39) As String
' use 34 for double quotes, 39 for apostrophe
  QuoteWrap = Chr(charToUse) & stringToWrap & Chr(charToUse)
End Function
Function PrintItems(filteredCollection As Outlook.Items)
  Dim i As Long
  For i = 1 To filteredCollection.Count
    Debug.Print filteredCollection.Item(i).Subject
  Next i
End Function
Function HasItems(filteredCollection As Outlook.Items) As Boolean
  HasItems = (filteredCollection.Count > 0)
End Function

Filtering By Subject

Filtering emails by subject ensures that the resulting collection contains only items that have the same subject. In this example, we filter the default local Inbox folder for all items that contain the subject "Company Invoice". If you need a different folder, you will need to walk the folder hierarchy. Note that we cannot parse partial subjects — for that, we would need to loop through the entire folder and use Instr and Mid$ to examine each subject. The Restrict Method only allows us to read the whole Subject property.

Sub FilteringBySubject()
  Dim itemsCollection As Outlook.Items
  Dim filterCriteria As String
  Dim filteredItemsCollection As Outlook.Items

  ' start with Inbox (1)
  Set itemsCollection = GetItems(GetNS(GetOutlookApp), olFolderInbox)

  ' *************************************
  ' filter Inbox items by Subject (2)
  ' *************************************
  filterCriteria = "[Subject] = " & QuoteWrap("Company Invoice")
  Set filteredItemsCollection = itemsCollection.Restrict(filterCriteria)
  If HasItems(filteredItemsCollection) Then
    ' change this to do what you want with
    ' the restricted items collection (3)
    PrintItems filteredItemsCollection
  End If
End Sub

This function is designed to be as generic as possible. There are three points where changes can be made. To use a different folder, change the folder pointer (1). To change the filter criteria, change the input for the filterCriteria variable (2). To change what you want to do with the output, add your code after the items count (3).

Suppose you wanted the subject to be a variable passed to the procedure instead of an internally hardcoded string, you could alter the procedure like this:

Sub FilteringBySubject(subjectLine As String)
  Dim itemsCollection As Outlook.Items
  Dim filterCriteria As String
  Dim filteredItemsCollection As Outlook.Items

  ' start with Inbox (1)
  Set itemsCollection = GetItems(GetNS(GetOutlookApp), olFolderInbox)

  ' *************************************
  ' filter Inbox items by Subject (2)
  ' *************************************
  filterCriteria = "[Subject] = " & QuoteWrap(subjectLine)
  Set filteredItemsCollection = itemsCollection.Restrict(filterCriteria)
  If HasItems(filteredItemsCollection) Then
    ' change this to do what you want with
    ' the restricted items collection (3)
    PrintItems filteredItemsCollection
  End If
End Sub

The filter criteria is very sensitive and must be constructed just so. The easiest way I found to do it was to use string literals for the criteria names, and wrap variables into their own quotes using a function. The QuoteWrap function is a variation of a function I usually use to wrap quotes, and is used to ensure that variables for the Restrict method are wrapped in their own set of quotation marks. This is neater and less prone to error than trying to match double-double quotes (ex: "" & someVar & "")

You can use apostrophes or double quotes. I prefer apostrophes because it makes the result easier to read (in the Immediate/Locals Window); change 39 to 34 in the QuoteWrap function if you feel differently.

For this exercise we simply print each item's subject to the Immediate Window after checking if the resulting collection isn't empty. What you choose to do here depends on what you are trying to actually do with each item (ex: print, move, save, append).

Filtering By Time

Filtering items by time of receipt is a popular way to process incoming messages. Instead of filtering by Subject, suppose we wanted to process only Inbox items that were received within a known time period, ex: everything after 4:30PM on August 8th, 2012:

Sub FilteringByTime()
  Dim itemsCollection As Outlook.Items
  Dim filterCriteria As String
  Dim filteredItemsCollection As Outlook.Items

  ' start with Inbox
  Set itemsCollection = GetItems(GetNS(GetOutlookApp), olFolderInbox)

  ' *************************************
  ' filter Inbox items by received time
  ' *************************************
  filterCriteria = "[ReceivedTime] > " & QuoteWrap("8/8/2012 4:30 PM") & _
                 " And [ReceivedTime] < " & QuoteWrap("8/9/2012 12:00 AM")
  Set filteredItemsCollection = itemsCollection.Restrict(filterCriteria)
  If HasItems(filteredItemsCollection) Then
    PrintItems filteredItemsCollection
  End If
End Sub

Because of the nature of time values, we need to specify two values and join them with the And operator. If we wanted to use variables instead, declare two Date variables and pass their values like this:

Sub FilteringByTime(startDate As Date, endDate As Date)
  Dim itemsCollection As Outlook.Items
  Dim filterCriteria As String
  Dim filteredItemsCollection As Outlook.Items

  ' start with Inbox
  Set itemsCollection = GetItems(GetNS(GetOutlookApp), olFolderInbox)

  ' *************************************
  ' filter Inbox items by received time
  ' *************************************
  filterCriteria = "[ReceivedTime] > " & QuoteWrap(startDate) & _
                   " And [ReceivedTime] < " & QuoteWrap(endDate)
  Set filteredItemsCollection = itemsCollection.Restrict(filterCriteria)
  If HasItems(filteredItemsCollection) Then
    PrintItems filteredItemsCollection
  End If
End Sub

Literal date values should be wrapped in '#' characters like this: #8/8/2012 4:30 PM#

Since the functions are the same except for the filter criteria, from here on we will only look at the filter criteria for each restriction.

Filtering by Importance

We can also filter items by importance. There are two ways to specify the importance level, using either the enumeration or a string literal.

  • 0 or "Low"
  • 1 or "Normal"
  • 2 or "High"
filterCriteria = "[Importance] = " & _
    QuoteWrap("Normal")  ' or "[Importance] = 1"

Despite what it says in the help documentation, I could not get numeric values to work with quotes.

Filtering by Read Receipt

I had a co-worker who would always request read receipts on his emails, then call you immediately after you read his email. That's how I discovered the feature to turn off read receipts. Filtering by those emails lets you know who is trying to spy on you.

filterCriteria = "[ReadReceiptRequested] = True"

Note that boolean values must be included in the criteria literally and not wrapped with quotes. If you wrap the value in quotes, then it will default to true. If you ever need to use false, use an empty string or do not use extra quotes at all. To avoid confusion, it's better to write the filter criteria as above.

The same goes for the OriginatorDeliveryReportRequested Property.

filterCriteria = "[OriginatorDeliveryReportRequested] = True"

or

filterCriteria = "[OriginatorDeliveryReportRequested] = " & True

Filtering by Sender (external/internal)

filterCriteria = "[SenderEmailType] = " & QuoteWrap("SMTP")

As far as I know, SenderEmailType returns two possible values: SMTP for external senders, EX for Exchange server (internal) addresses.

Once you have all items sent from external senders, use the SenderEmailAddress filter to further filter the list of items to only those from a particular sender. i.e.

Sub FilteringBySender()
  Dim itemsCollection As Outlook.Items
  Dim filterCriteria As String
  Dim filteredItemsCollection As Outlook.Items

  Set itemsCollection = GetItems(GetNS(GetOutlookApp), olFolderInbox)

  ' get all items by external senders
  filterCriteria = "[SenderEmailType] = " & QuoteWrap("EX")
  Set filteredItemsCollection = itemsCollection.Restrict(filterCriteria)

  If HasItems(filteredItemsCollection) Then 
    ' get only messages from one sender
    filterCriteria = "[SenderEmailAddress] = " & _
        QuoteWrap("someone@outside-your-company.org")
    ' filter the same collection even further
    Set filteredItemsCollection = filteredItemsCollection.Restrict( _
      filterCriteria)
    If HasItems(filteredItemsCollection) Then
      MsgBox "someone@outside-your-company.org has " & _
        filteredItemsCollection.Count " items in your Inbox."
    End If
  End If
End Sub

We could also stack the criteria like this, making the code shorter:

filterCriteria = "[SenderEmailType] = " & QuoteWrap("EX") & _
 " And [SenderEmailAddress] = " & _
    QuoteWrap("someone@outside-your-company.org")

We can also filter by sender name, if we know the exact spelling:

filterCriteria = "[SenderName] = " & QuoteWrap("GATES, WILLIAM")

or

filterCriteria = "[SenderName] = " & QuoteWrap("GATES, WILLIAM") & _
      " Or [SenderName] = " & QuoteWrap("ALLEN, PAUL")

Filtering by Category

Category filtering is difficult because categories are like Subjects — loose string values, joined together with commas. If each of your contacts are in a single category, filtering is simple:

filterCriteria = "[Categories] = " & QuoteWrap("Business")

If you have multiple categories, you need to specify them all in the criteria. This filter will only match contacts who are in all three categories:

filterCriteria = "[Categories] = " & _
    QuoteWrap("Business, Invoicing, Sales")

There is no way to parse partial Categories. To retrieve all the contacts in the Business category (assuming multiple categories), you need to join all possible values using the Or operator. This filter will match all contacts in the Business category, assuming three possible categories:

filterCriteria = "[Categories] = " & QuoteWrap("Business") & _
    " Or [Categories] = " & QuoteWrap("Business, Invoicing") & _
    " Or [Categories] = " & QuoteWrap("Business, Sales")
    " Or [Categories] = " & QuoteWrap("Business, Invoicing, Sales")

It's interesting that the VBA help on the Restrict Method contradicts itself when discussing Categories. It states that Categories is one of the properties you cannot filter on, then proceeds to explain in detail exactly how to use the Restrict Method to filter Categories. Either you can or you can't, which is it Microsoft?

Look for AutoForwarded messages

This is another boolean criteria, like Read receipts.

filterCriteria = "[AutoForwarded] = True"

Filter by Flag Status

  • 1 or "Complete"
  • 2

The FlagStatus Property is supposed to have three values, but I could only get two to work: either "1" or "Complete" for items flagged as Complete, and "2" for items that are flagged at all. Like Importance, it also matters where you place the value (again, the help documentation says it shouldn't matter). I couldn't get numeric values to work in the QuoteWrap function.

filterCriteria = "[FlagStatus] = 2"
filterCriteria = "[FlagStatus] = 1"

or

filterCriteria = "[FlagStatus] = " & QuoteWrap("Complete")

Filter by Item Type

Filtering by the MessageClass Property lets us restrict our Items collection to only items of a certain type (emails, appointments, etc). See Customizing the MessageClass Property for a (complete?) list of possible MessageClass Property values (see the "Base message class" table). This criteria will filter out items that are not emails:

filterCriteria = "[MessageClass] = " & QuoteWrap("IPM.Note")

Find messages you can't forward

Want to find messages you're not allowed to forward? Check out the RecipientReassignmentProhibited Property.

filterCriteria = "[RecipientReassignmentProhibited] = True"

Filtering by Sensitivity

There are four levels of sensitivity:

  • 0 or "Normal"
  • 1 or "Personal"
  • 2 or "Private"
  • 3 or "Confidential"
filterCriteria = "[Sensitivity] = " & QuoteWrap("Private")

or

filterCriteria = "[Sensitivity] = 0"

Filtering by Read Status

Items can either be read or unread, so the criteria for read items is as follows:

filterCriteria = "[UnRead] = False"

Stacking Criteria

Instead of calling the Restrict Method directly in our procedures, we really should outsource it to another function like this:

Function FilterItems(itemsCollection As Outlook.Items, _
    filterCriteria As String) As Items
  Set FilterItems = itemsCollection.Restrict(filterCriteria)
End Function

Now that we've blackboxed this method, we should simply pass in our Items Collection and filter criteria and get handed back a nicely filtered Items Collection.

Let's say I have an archive folder with emails about a certain project. I want to loop through all emails in that folder received at a certain time from a certain sender.

Sub ApplyFilter()

  Dim itemsCollection As Outlook.Items
  Dim filterCriteria As String
  Dim filteredItemsCollection As Outlook.Items
  Dim itm As Object
  Dim msg As Outlook.mailItem

  ' folder is one level below default Inbox
  Set itemsCollection = _
    Session.GetDefaultFolder(olFolderInbox).Folders("Special Project")

  filterCriteria = "[ReceivedTime] > " & QuoteWrap("7/1/2012 8:30 AM") & _
             " And [ReceivedTime] < " & QuoteWrap("8/1/2012 12:00 AM") & _
             " And [SenderEmailType] = " & QuoteWrap("EX") & _
             " And [SenderEmailAddress] = " & _
             QuoteWrap("salesman@external-vendor.com")

  Set filteredItemsCollection = _
    FilterItems(itemsCollection, filterCriteria)

  If HasItems(filteredItemsCollection) Then  ' found the msg(s)!
    ' loop through each message and look for a string in the body
    For Each itm In filteredItemsCollection
      If TypeName(itm) = "MailItem" Then
        Set msg = itm
        If InStr(msg.Body, _
          "I swear I'll deliver the product on time, " & _
          "under budget and fully featured") > 0 Then  ' caught!
          MsgBox "Your vendor is probably boarding a plane for Rio as we speak."
        End If
      End If
    Next itm
  End If
End Sub

First we filter for messages during July that were sent from an external sender. If we did receive any items from that sender, we loop through those items to see if our vendor has violated the project management triangle. If so, a message box let's us know that we are screwed.

Conclusion

The Restrict Method may be used to filter the Items Collection. This can speed up existing code by targeting your loops to iterate over only emails that match a specific criteria. Instead of looping over all items in a folder, you only loop over the items you actually need to look at.

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »



Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

This article is closed to any future comments.
Random Data Generator