Using the .Restrict method in Outlook VBA to filter on single recipient email address

Question

I have code in Access that gets all emails in the user's Inbox that are sent by an individual email address. This code (simplified, below) works fine:

Dim outItems as Outlook.Items
Dim strEMAddress as string
Dim outFolder as Outlook.MAPIFolder

Set outFolder = outNS.GetDefaultFolder(olFolderInbox)
Set outItems = outFolder.Items

str="[email protected]"

Set outItems = outItems.Restrict("[SenderEmailAddress] = " & "'" & strEMAddress & "'")

I am looking for something that will do likewise on the SentMails folder, restricting the items to those sent to a specific email address.

I know this is complicated by the fact that .Recipients is a collection (as items can/do have more than one recipient). I am hoping there is a way to return a list of items that contain the email address I am looking for in any of the sent fields (To/CC/bcc - but happy with just To if this is easier).

I have searched online and found .To is no good (is not the email address) and I can't get pseudo code such as this work:

 Set outItems = outItems.Restrict("[Recipients] = " & "'" & strEMAddress & "'")

Answer 1

You can use the DASL query as the filter string in your items.restrict method.

For example to find all mails i sent to Ali Raza i use the following

str_fltr = "@SQL=""urn:schemas:httpmail:displayto"" ci_phrasematch '%Ali Raza%'"

The good thing about the above DASL query is that it returns matches with multiple recepients whether if you use the jet syntax for searching resultx will only contain items with one recipient. Jet syntax is the one that you are currently using. You should use the [To] property rather than [Recipients]

Here https://msdn.microsoft.com/en-us/library/cc513841%28v=office.12%29.aspx#SearchingOutlookData_Overview is good place where you can learn almost everything about searching in outlook.

Here http://www.msoffice.us/Outlook/PDF/%28Outlook%202010%29%20Common%20DASL%20Property%20Tags.pdf is a list of common DASL tags which will come in handy if you get a grip on DASL syntax.

Answer 2

For multiple [TO/CC/BCC] filter example would be...

Option Explicit
Public Sub Example()
    Dim olNs As Outlook.NameSpace
    Dim Folder As Outlook.MAPIFolder
    Dim Items As Outlook.Items
    Dim Filter As String
    Dim Msg As String
    Dim i As Long

    Set olNs = Application.GetNamespace("MAPI")
    Set Folder = olNs.GetDefaultFolder(olFolderSentMail)

    Filter = "@SQL=" & "urn:schemas:httpmail:displayto" & _
                       " Like '%John Doe%' Or " & _
                       "urn:schemas:httpmail:displaycc" & _
                       " Like '%John Doe%' Or " & _
                       "urn:schemas:httpmail:displaybcc" & _
                       " Like '%John Doe%'"

    Set Items = Folder.Items.Restrict(Filter)

    Msg = Items.Count & " Items in " & Folder.Name & " Folder"

    If MsgBox(Msg, vbYesNo) = vbYes Then
        For i = Items.Count To 1 Step -1
            Debug.Print Items(i) 'Immediate Window
        Next
    End If
End Sub

now remember if the display name is [email protected] then filter should be %[email protected]% else use %John Doe%

Answer 3

If using Redemption is an option (I am its author), you can use RDOFolder.Items.Restrict - unlike Outlook Object Model, it does expand To/CC/BCC queries into recipient sub restrictions on PR_DISPLAY_NAME and PR_EMAIL_ADDRESS properties on each recipient (RES_SUBRESTRICTION / PR_MESSAGE_RECIPIENTS / RES_OR / PR_DISPLAY_NAME | PR_EMAIL_ADDRESS).

set Session = CreateObject("Redemption.RDOSession")
Session.MAPIOBJECT = Application.Session.MAPIOBJECT
set Folder = Session.GetFolderFromID(Application.ActiveExplorer.CurrentFolder.EntryID)
set restrItems = Folder.Items.Restrict(" TO = '[email protected]' ")

You can also specify Recipients property in the SQL query - it will be matched against recipients of all types (to/cc/bb):

set restrItems = Folder.Items.Restrict(" Recipients = '[email protected]' ")