VBA Search in Outlook

Question

I have this code to search in my folder. I do have a e-mail with the "sketch" subject, but VBA is not finding it (it goes to the ELSE clause)

Can anybody tell what is wrong ?

Set olApp = GetObject(, "Outlook.Application")

Set olNs = olApp.GetNamespace("MAPI")
Set olFldr = olNs.GetDefaultFolder(olFolderInbox)
Set olItms = olFldr.Items

Set Mail = olItms.Find("[Subject] = ""*sketch*""") 'Tracking
If Not (Mail Is Nothing) Then
    'use mail item here

Else
    NoResults.Show
End If

score 27 · Answer 1 · edited Dec 08 '14 at 13:23

27

Here is a way to do the search using Items Restrict.

This runs fast and you do not need to loop through the items to find the items that match the search criteria.

Sub Search_Inbox()

Dim myOlApp As New Outlook.Application
Dim objNamespace As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Dim filteredItems As Outlook.Items
Dim itm As Object
Dim Found As Boolean
Dim strFilter As String


Set objNamespace = myOlApp.GetNamespace("MAPI")
Set objFolder = objNamespace.GetDefaultFolder(olFolderInbox)

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

Set filteredItems = objFolder.Items.Restrict(strFilter)

If filteredItems.Count = 0 Then
    Debug.Print "No emails found"
    Found = False
Else
    Found = True
    ' this loop is optional, it displays the list of emails by subject.
    For Each itm In filteredItems
     Debug.Print itm.Subject
    Next
End If


'If the subject isn't found:
If Not Found Then
    'NoResults.Show
Else
   Debug.Print "Found " & filteredItems.Count & " items."

End If

'myOlApp.Quit
Set myOlApp = Nothing

End Sub

edited Dec 08 '14 at 13:23

Bill the Lizard

398,270
210
566
880

answered Dec 08 '14 at 01:45

jeffld

726
1
9
17

works as advertised - thanks - in outlook 2010. Next question how to sortby date or get the latest email.. – ozmike Mar 03 '15 at 01:15
Seems to be sorted by Recieved time but this code will also sort for you filteredItems.Sort "[ReceivedTime]", works. also itm could be of type Dim itm as Outlook.MailItem helps to get properties. – ozmike Mar 03 '15 at 01:38
How can this be modified to also search in body and in attachments? This does not seem to work `strFilter = "@SQL=" & Chr(34) & "urn:schemas:httpmail:body" & Chr(34) & " like '%153915%'` – skatun Aug 05 '16 at 12:05
2

Seems like textdescription is the same as body... `urn:schemas:httpmail:textdescription` – skatun Aug 08 '16 at 06:41

score 13 · Accepted Answer · answered Feb 04 '14 at 15:53

The reason your .Find isn't working is because Items.Find doesn't support the use of wildcards. Items.Find also doesn't support searching partial strings. So to actually find the email, you'd need to remove the wildcards and include the entire string in your search criteria.

So here are your options:

If you know the full subject line you're looking for, modify your code like so:

Set Mail = olItms.Find("[Subject] = ""This Sketch Email""")

If you don't (or won't) know the full subject, you can loop through your inbox folder and search for a partial subject line like so:

Untested

Sub Search_Inbox()

Dim myOlApp As New Outlook.Application
Dim myNameSpace As Outlook.NameSpace
Dim myInbox As Outlook.MAPIFolder
Dim myitems As Outlook.Items
Dim myitem As Object
Dim Found As Boolean

Set myNameSpace = myOlApp.GetNamespace("MAPI")
Set myInbox = myNameSpace.GetDefaultFolder(olFolderInbox)
Set myitems = myInbox.Items
Found = False

For Each myitem In myitems
    If myitem.Class = olMail Then
        If InStr(1, myitem.Subject, "sketch") > 0 Then
            Debug.Print "Found"
            Found = True
        End If
    End If
Next myitem

'If the subject isn't found:
If Not Found Then
    NoResults.Show
End If

myOlApp.Quit
Set myOlApp = Nothing

End Sub

Hope that helps!

The code is great, man. I will try it now. Could you explain why the double quotes in ("[Subject] = ""This Sketch Email""") ? — AndroidDev, Feb 04 '14 at 22:58
The double quotes are used because there's a string within a string. Without the double quotes, the compiler would think that it had reached the end of the string before it had. [SEE THIS](http://www.techrepublic.com/blog/microsoft-office/5-rules-for-embedding-strings-in-vba-code/) for more information. — ARich, Feb 04 '14 at 23:14
[This article](https://learn.microsoft.com/en-us/office/vba/api/outlook.items.find) also explains the functioning of the ```.Find``` method in this context as well as the handling of strings. — micsky, Feb 06 '23 at 09:36

VBA Search in Outlook

2 Answers2

Linked