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
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 & "%'"