Excel VBA Outlook - Subject Contains Is Not Finding Reply Emails

Question

How do I get the following Excel VBA code to return all emails with subjects containing 'Timesheet 06/19/20'?

The below code does return "Timesheet 06/19/20 - Name (EmpId) ". However, IT DOES NOT RETURN "[EXTERNAL] RE: Timesheet 06/19/20 - Name (EmpId) ". What am I doing wrong?

     Public Function Test()
         Dim Folder As Outlook.Folder, MailItems As Outlook.Items, MailItem As Outlook.MailItem
         Dim Filter As String
    
         Filter = "@SQL=urn:schemas:httpmail:subject" & "" & " ci_phrasematch 'Timesheet 06/19/20'"
         Set Folder = Outlook.Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
         Set MailItems = Folder.Items.Restrict(Filter)
         For Each MailItem In MailItems
             Debug.Print MailItem.Subject
         Next MailItem
     End Function

Note, if I change the filter to search on "[EXTERNAL] RE: Timesheet 06/19/20" it finds the email. Obviously this won't work for my solution as I need to search for "Timesheet 06/19/20" to accommodate the original, forward, and reply emails.

Thank you for any help you can give!

Answer 1

Not sure why but an email with the subject "Undeliverable: Timesheet 06/19/20 - Name (EmpId) " was throwing an error which caused the code to exit the for loop early. The below code fixed the error from being thrown.

Maybe someone has an explanation as to why the above subject likes an index count for loop better?

Public Function Test()
    Dim Folder As Outlook.Folder, MailItems As Outlook.Items, MailItem As Outlook.MailItem
    Dim Filter As String, idx As Long
    
    Filter = "@SQL=urn:schemas:httpmail:subject" & "" & " ci_phrasematch 'Timesheet 06/19/20'"
    
    Set Folder = Outlook.Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
    Set MailItems = Folder.Items.Restrict(Filter)
    For idx = 1 To MailItems.Count
        Debug.Print MailItems(idx).Subject
    Next idx
    
End Function