Office Developer: How to programmatically restrict or filter “To” property using Outlook Object Model?

In this post, we will see how to programmatically filter/restrict items “To” property. Let we take this scenario. In Outlook, we notice it contains 6 items “To” property containing “Deva G”:  

Outlook UI

Note:In specifying a filter in a Jet or DASL query, if you use a pair of single quotes to delimit a string that is part of the filter, and the string contains another single quote or apostrophe, then add a single quote as an escape character before the single quote or apostrophe. Use a similar approach if you use a pair of double quotes to delimit a string. If the string contains a double quote, then add a double quote as an escape character before the double quote. For example, in the DASL filter string that filters for the Subject property being equal to the word can't, the entire filter string is delimited by a pair of double quotes, and the embedded string can't is delimited by a pair of single quotes. There are three characters that you need to escape in this filter string: the starting double quote and the ending double quote for the property reference of, and the apostrophe in the value condition for the word can't. Applying the appropriate escape characters, you can express the filter string as follows:

filter = "@SQL="""" = 'can''t'"

Alternatively, you can use the chr(34) function to represent the double quote (whose ASCII character value is 34) that is used as an escape character. Using the chr(34) substitution for a double-quote escape character, you can express the last example as follows:

filter = "@SQL= " & Chr(34) & "" & Chr(34) & " = " & "'can''t'"

For this above test, first we need to get the property reference for “to” so that we can create DASL Filter for it. For this, you can make use of latest MFC MAPI and get the same using its Property Editor.

MFC MAPI - Property Editor

I am making use of Outlook Object Model (OOM) API to filter/restrict “To” field items containing “Deva G”. In order to do that, you can make use of Items.Restrict method provided in OOM along with the Filter that you want to apply. The Restrict method is significantly faster if there is a large number of items in the collection, especially if only a few items in a large collection are expected to be found.

Filter = "@SQL= " & Chr(34) & "" & Chr(34) & " = " & "'Deva G'"

Using the above filter, we can build the code using Items.Restrict method – enclosing the Outlook VBA sample for your reference:

'Using Items.Restrict
Public Sub RestrictFilter()
Dim myNameSpace As Outlook.NameSpace
Dim myItems As Outlook.Items
Dim currentItem As Outlook.Items
Dim Filter As String
Dim i As Integer
Filter = "@SQL= " & Chr(34) & "" & Chr(34) & " = " & "'Deva G'"
Set myNameSpace = Application.GetNamespace("MAPI")
Set myItems = myNameSpace.PickFolder.Items
Set currentItem = myItems.Restrict(Filter)
For i = currentItem.Count To 1 Step -1
    Debug.Print currentItem(i).Subject
End Sub

When you execute the code, you can retrieve the filtered item’s “subject” property values(PR_SUBJECT)….

Outlook VBA - Source code & Output

You can give a try and let me know how it goes…. Happy programming!!

Comments (1)
  1. Steve says:

    Nice one Dev. Good documentation from Object model and using MFC MAPI too!!

Comments are closed.