If Outlook Subject and Date Received

Question

I am using vba from excel to search my default outlook inbox for emails that contain "Weekly Op's Report for" on the subject line and transfer to excel. How can I modify my code to meet to conditions: if subject line reads "Weekly Op's Report for" and Received date = today then copy email body. Here is my code below:

Sub GetFromInbox()

Dim olApp As Outlook.Application
Dim olNs As Outlook.Namespace
Dim olFldr As Outlook.MAPIFolder
Dim olItms As Outlook.Items
Dim olMail As Variant
Dim i As Long

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

olItms.Sort "Subject"

i = 1

For Each olMail In olItms
    If InStr(1, olMail.Subject, "Weekly Op's Report for ") > 0 Then
        ThisWorkbook.Sheets("tester").Cells(i, 1).Value = olMail.Body
        i = i + 1

    End If
Next olMail

Set olFldr = Nothing
Set olNs = Nothing
Set olApp = Nothing

End Sub

Simon Li Simon Li · Accepted Answer · 2018-08-21T06:21:43

You can use ReceivedTime property of olMail object to achieve your request. Please try this code below:

Sub GetFromInbox()

Dim olApp As Outlook.Application
Dim olNs As Outlook.NameSpace
Dim olFldr As Outlook.MAPIFolder
Dim olItms As Outlook.Items
Dim olMail As Variant
Dim i As Long
Dim date1 As String
Dim date2 As String

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set olFldr = olNs.GetDefaultFolder(olFolderInbox)
Set olItms = olFldr.Items
date1 = Date
olItms.Sort "Subject"
olItms.Sort "[ReceivedTime]", True

i = 1

For Each olMail In olItms
date2 = olMail.ReceivedTime
If InStr(1, olMail.Subject, "Weekly Op's Report for ") > 0 And DateDiff("d", date1, date2) = 0 Then
    ThisWorkbook.Sheets("tester").Cells(i, 1).Value = olMail.Body
    i = i + 1

End If
Next olMail

Set olFldr = Nothing
Set olNs = Nothing
Set olApp = Nothing

End Sub

For more DateDiff function, please see How to use the DATEDIFF Function (VBA).

If Outlook Subject and Date Received

1 Answers