Search for mail in Sent Items, by date and subject

Question

I need to search for mail in Sent Items, sent on current date and with subject as "Task Completed". Sometimes Subject may have additional text like Task Completed on 07/01/2017 or Task Completed 01/09/2017.

I found this Outlook VBA code, which displays found mail. I want the code to run in Excel with wildcard search options and open an Excel file.

I tried to search the subject with wildcard "*", like "Task Completed*" and "Task Completed on & Format(Date, "dd/mm/yyyy")" for which I got an syntax error/compile error

Sub Test()

Dim olApp As Outlook.Application
Dim olNs As NameSpace
Dim Fldr As MAPIFolder
Dim olMail As Outlook.MailItem
Dim i As Integer

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.GetDefaultFolder(olFolderSentMail)
i = 1

For Each olMail In Fldr.Items
    If InStr(olMail.Subject, "Task Completed on 07/01/2017") <> 0 Then
        olMail.Display
        i = i + 1    
    End If    
Next olMail

End Sub

I am using Office 2010.

Answer 1

In order to loop through all items in Sent Items folder, including Calendar events you may have, use the Dim olMail As Object (instead of AS Outlook.MailItem).

To look for "Task Completed" string somewhere in the email's title, use If olMail.Subject Like "*Task Completed*" Then (adding the wildcard * before and after the searched string).

I've added 2 lines of code, that output all matching emails to your worksheet in Column A and Column B.

Code

Option Explicit

Sub Test()

Dim olApp As Outlook.Application
Dim olNs As Namespace
Dim Fldr As MAPIFolder
Dim olMail As Object
Dim i As Integer, j As Integer

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.GetDefaultFolder(olFolderSentMail)

i = 1
For Each olMail In Fldr.Items
    ' check if mail subject contains "Task Completed" in the email title
    If olMail.Subject Like "*Task Completed*" Then
        'Range("A" & i).Value = olMail.Subject ' <-- output email name to column A
        'Range("B" & i).Value = olMail.SentOn ' <-- output email sent date to column B
        olMail.Display ' show email through Excel
        i = i + 1
    End If
Next olMail

End Sub