CODEWITHSUNDEEP
vbaemailoutlookoutlook-filter
aaa
aaa

Reputation: 858

Use Restrict method to get recipient, subject and date range

I tried 

'only works for name, not email address.
"@SQL=""urn:schemas:httpmail:displayto"" ci_phrasematch '%John Doe%'" 

'works great, failed when use AND. today was declared as string
"@SQL=""urn:schemas:httpmail:subject"" like '%" & emailSubject & "%'"

'works great, failed when use AND. today was declared as string
"[ReceivedTime] <= today"

How can I use Item.Restrict to filter the recipient email address, subject and date range together?

Upvotes: 1

Views: 2776

Answers (2)

0m3r
0m3r

Reputation: 12499

Here is example for multiple filters

Option Explicit
Public Sub Example()
    Dim olNs As Outlook.NameSpace
    Dim TargetFolder As Outlook.MAPIFolder
    Dim Items As Outlook.Items
    Dim Item As Object
    Dim i As Long
    Dim Filter As String

    Set olNs = Application.Session
    If TargetFolder Is Nothing Then Set TargetFolder = ActiveExplorer.CurrentFolder
    Debug.Print TargetFolder.Name

    Filter = "@SQL=" & Chr(34) & "urn:schemas:httpmail:datereceived" & _
                       Chr(34) & " >= '01/10/2018' And " & _
                       Chr(34) & "urn:schemas:httpmail:datereceived" & _
                       Chr(34) & " < '01/16/2018' And " & _
                       Chr(34) & "urn:schemas:httpmail:fromname" & _
                       Chr(34) & "Like '%0m3r 0m3r%'"

    Set Items = TargetFolder.Items.Restrict(Filter)
        Items.Sort "[ReceivedTime]"

    For i = Items.Count To 1 Step -1
        DoEvents
        If TypeOf Items(i) Is mailitem Then
            Set Item = Items(i)
            Debug.Print Item.Subject
            Debug.Print Item.ReceivedTime
        End If
    Next

End Sub

If TargetFolder Is Nothing Then Set TargetFolder = ActiveExplorer.CurrentFolder

Returns or sets a MAPIFolder object that represents the current folder displayed in the explorer

Or use

Dim olNs As Outlook.NameSpace
Dim TargetFolder As Outlook.MAPIFolder
Set olNs = Application.GetNamespace("MAPI")
Set TargetFolder = olNs.GetDefaultFolder(olFolderInbox)

Filter with Subject line

Filter = "@SQL=" & Chr(34) & "urn:schemas:httpmail:datereceived" & _
                   Chr(34) & " >= '01/10/2018' And " & _
                   Chr(34) & "urn:schemas:httpmail:datereceived" & _
                   Chr(34) & " < '01/17/2018' And " & _
                   Chr(34) & "urn:schemas:httpmail:fromname" & _
                   Chr(34) & "Like '%0m3r 0m3r%' And " & _
                   Chr(34) & "urn:schemas:httpmail:subject" & _
                   Chr(34) & " Like '%Bla Bla%'"

Upvotes: 2

Dmitry Streblechenko
Dmitry Streblechenko

Reputation: 66225

Outlook does not know what "today" is. You need to provide a suitably formatted date value, e.g. "2018-01-17"

Upvotes: 0

Related Questions