excel - Using Restrict method for emails within a specified date

Question

I am creating a macro to get email by subject and received date in our team shared box. My problem is that once I select date (e,g 1/16/2018 to 1/17/2018), only few emails are stored in object. In below screenshot, I have 9 items which are applied restrict method. It should be 14 items emails which are received after 1/16/2018 to now(right outlook mail in screenshot), but 5 emails are not stored in object. can anyone help me out? I'm STUCK!

Sub GetFromOutlook()

Dim OutlookApp As Outlook.Application
Dim OutlookNamespace As Namespace
Dim Folder As MAPIFolder
Dim OutlookMail As Variant
Dim i As Integer
Dim olItems As Outlook.Items
Dim myItems As Outlook.Items
Dim DateStr As Date
Dim DateEnd As Date
Dim oOlResults As Object

Dim DateToCheck As String
Dim DateToCheck2 As String
Dim DateToCheck3 As String

Set OutlookApp = New Outlook.Application
Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")

Dim olShareName As Outlook.Recipient
Set olShareName = OutlookNamespace.CreateRecipient("[email protected]")
Set Folder = OutlookNamespace.GetSharedDefaultFolder(olShareName, olFolderInbox).Folders("sub1").Folders("sub2")
Set olItems = Folder.Items


'DateStr = 1/16/2018
'DateEnd = 1/17/2018

DateStr = Format(Range("From_Date").Value, "DDDDD HH:NN")
DateEnd = Format(Range("To_Date").Value, "DDDDD HH:NN")

'DateStr = DateAdd("d", -1, DateStr)
'DateEnd = DateAdd("d", 1, DateEnd)

DateToCheck = "[ReceivedTime] > """ & DateStr & """"
DateToCheck2 = "[ReceivedTime] <= """ & DateEnd & """"
DateToCheck3 = "[SenderName] = ""[email protected]"""

Set myItems = olItems.Restrict(DateToCheck)
Set myItems = myItems.Restrict(DateToCheck2)
Set myItems = myItems.Restrict(DateToCheck3)

i = 1

For Each myitem In myItems
    ' MsgBox myitem.ReceivedTime

     Range("eMail_subject").Offset(i, 0).Value = myitem.Subject
     Range("eMail_date").Offset(i, 0).Value = myitem.ReceivedTime

     i = i + 1

Next myitem

Set Folder = Nothing
Set OutlookNamespace = Nothing
Set OutlookApp = Nothing


End Sub

Answer 1

If you are missing most recent mail then set DateEnd, without time, one day later. This should calculate to the beginning of the day at time 00:00.

Sub GetFromOutlook()

Dim OutlookApp As Outlook.Application
Dim OutlookNamespace As Namespace
Dim Folder As MAPIFolder
Dim OutlookMail As Variant

Dim i As Integer

Dim olItems As Outlook.Items
Dim myItems As Outlook.Items
Dim myitem As Object

Dim DateStr As String
Dim DateEnd As String

Dim oOlResults As Object

Dim DateToCheck As String
Dim DateToCheck2 As String
Dim DateToCheck3 As String

Set OutlookApp = New Outlook.Application
Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")

Dim olShareName As Outlook.Recipient
'Set olShareName = OutlookNamespace.CreateRecipient("[email protected]")
'Set Folder = OutlookNamespace.GetSharedDefaultFolder(olShareName, olfolderinbox).Folders("sub1").Folders("sub2")

' for my testing
Set Folder = OutlookNamespace.getdefaultfolder(olfolderinbox)

Set olItems = Folder.Items

DateStr = "2018-01-16"
Debug.Print DateStr

' User input DateEnd without a time
DateEnd = "2018-01-17"
Debug.Print DateEnd

' Calculated DateEnd is the beginning of the next day
DateEnd = DateAdd("d", 1, DateEnd)
' This is 2018-01-18 00:00
Debug.Print DateEnd

DateToCheck = "[ReceivedTime] > """ & DateStr & """"
Debug.Print vbCr & "Filter 1: " & DateToCheck

Set myItems = olItems.Restrict(DateToCheck)

For Each myitem In myItems
    Debug.Print myitem.ReceivedTime & ": " & myitem.Subject
Next myitem

'DateToCheck2 = "[ReceivedTime] <= """ & DateEnd & """"
DateToCheck2 = "[ReceivedTime] < """ & DateEnd & """"
Debug.Print vbCr & "Filter 2: " & DateToCheck2

Set myItems = myItems.Restrict(DateToCheck2)

For Each myitem In myItems
    Debug.Print myitem.ReceivedTime & ": " & myitem.Subject
Next myitem

DateToCheck3 = "[SenderName] = ""[email protected]"""
Debug.Print vbCr & "Filter 3: " & DateToCheck3

Set myItems = myItems.Restrict(DateToCheck3)

For Each myitem In myItems
    Debug.Print myitem.ReceivedTime & ": " & myitem.Subject
Next myitem

Set Folder = Nothing
Set OutlookNamespace = Nothing
Set OutlookApp = Nothing

End Sub