Find an email with subject starting with specific text

Question

I am trying to find an email, by subject starting with specific text, to then download an attachment from that email.

I am using a variable with Restrict function, however issue seems to be because of usage of wildcards.

Sub findemail()

cntofmkts = Range("A" & Rows.Count).End(xlUp).Row
cntofmkts = cntofmkts - 1
ftodaydate = Format(Date, "yyyy-mm-dd")

Do
    If i > cntofmkts Then Exit Do

    MarketName = Range("A" & j).Value    
    Findvariable = "XXX_" & MarketName & "_ABC_" & ftodaydate

    For Each oOlItm In oOlInb.Items.Restrict("[Subject] = *Findvariable*")
        eSender = oOlItm.SenderEmailAddress
        dtRecvd = oOlItm.ReceivedTime
        dtSent = oOlItm.CreationTime
        sSubj = oOlItm.Subject
        sMsg = oOlItm.Body

        If oOlItm.Attachments.Count <> 0 Then
            For Each oOlAtch In oOlItm.Attachments
                '~~> Download the attachment
                oOlAtch.SaveAsFile NewFileName & oOlAtch.Filename
                Exit For
            Next
        Else
            MsgBox "The First item doesn't have an attachment"
        End If

        Exit For
    Next

    i = i + 1
    j = j + 1

Loop
End sub

Solution 1

The first thing you should mind is that the Restrict() method does not evaluate the variable by it's name. You will have to concatenate the variable to the string.

Another one is, if you look at the example from MSDN site, you will see that there is not support for wildcards, so you will have to use the SQL syntax and the searched text in the filter expression must be between quotes.

' this namespace is for Subject
filterStr = "@SQL=""http://schemas.microsoft.com/mapi/proptag/0x0037001f"" like '%" & Findvariable & "%'"

It seems that urn:schemas:httpmail:subject also works and is easier to understand, but I can't confirm this now:

filterStr = "@SQL=""urn:schemas:httpmail:subject"" like '%" & Findvariable & "%'"

Solution 2

The string comparison that DASL filters support includes equivalence, prefix, phrase, and substring matching.

For Each oOlItm In oOlInb.Items.Restrict("[Subject] = Findvariable")

It looks like you are searching for the exact match. But what you need is to find a substring using the following syntax:

criteria = "@SQL=" & Chr(34) & "urn:schemas:httpmail:subject" & Chr(34) & " like '%question%'" 

Note that when you filter on the Subject property, prefixes such as "RE: " and "FW: " are ignored.

See Filtering Items Using a String Comparison for more information.

P.S. The Restrict method is an alternative to using the Find method or FindNext method to iterate over specific items within a collection. The Find or FindNext methods are faster than filtering if there are a small number of items. 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.