CODEWITHSUNDEEP
vbaoutlook
mmoschet
mmoschet

Reputation: 77

Restrict Outlook Items by Date

I have an Outlook macro that filters email objects by date and returns items based on an array.

The filter for today is the following:

sfilter = "[ReceivedTime]>=""&Date()12:00am&"""
Set myItems = myNewFolder.Items.Restrict(sfilter)

sFilter is a string and this returns the items for today as intended.

I am trying to filter to emails received yesterday.

The following were my attempts.

sfilter = "[ReceivedTime]>=""&Date(-1) 12:00am&"" AND [ReceivedTime]<= ""&Date() 12:00am&"" "
tfilter = Format(DateAdd("d", -1, Date), "mm/dd/yyyy")
rFilter = Format(DateAdd("d", 0, Date), "mm/dd/yyyy")

I intended to use the tFilter and rFilter as the upper and lower bound for sFilter.

I tried to use the DateAdd method after looking on the MSDN site with the function information but that did not return yesterday's items.

I tried the solution offered on this question (Outlook .Restrict method does not work with Date).

The method with date(-1) did not work in tandem with date. According to the MSDN site logical operators should work.

Note: The lower three examples cited compile and do not return any errors.

Upvotes: 5

Views: 25404

Answers (2)

niton
niton

Reputation: 9179

You can find yesterday's mail with two separate Restricts.

Private Sub EmailYesterday()

Dim oOlInb As Folder
Dim oOlItm As Object

Dim oOlResults As Object
Dim i As Long

Dim sFilter As String
Dim sFilter2 As String

Set oOlInb = Session.GetDefaultFolder(olFolderInbox)

'Filter recent - Lower Bound of the range
sFilter = "[ReceivedTime]>'" & format(Date - 1, "DDDDD HH:NN") & "'"

Debug.Print vbCr & sFilter
Set oOlResults = oOlInb.Items.Restrict(sFilter)
Debug.Print oOlResults.count & " items."

If oOlResults.count > 0 Then
    For i = 1 To oOlResults.count
        Set oOlItm = oOlResults(i)
        Debug.Print oOlItm.Subject & " - " & oOlItm.ReceivedTime
    Next i
End If

' Filter range - Upper Bound
sFilter2 = "[ReceivedTime]<'" & format(Date, "DDDDD HH:NN") & "'"

Debug.Print vbCr & sFilter; " AND " & sFilter2

Set oOlResults = oOlResults.Restrict(sFilter2)   ' Restrict the Lower Bound result
Debug.Print oOlResults.count & " items."

If oOlResults.count > 0 Then
    For i = 1 To oOlResults.count
        Set oOlItm = oOlResults(i)
        Debug.Print oOlItm.Subject & " - " & oOlItm.ReceivedTime
    Next i
End If
    
ExitRoutine:
    Set oOlInb = Nothing
    Set oOlResults = Nothing
    Debug.Print "Done."
    
End Sub

Upvotes: 9

hiichaki
hiichaki

Reputation: 974

Yesterday date could be filtered as below

oOlResults.Restrict("@SQL=%yesterday(""urn:schemas:httpmail:datereceived"")%")

The same for today or this month.

 oOlResults.Restrict("@SQL=%today(""urn:schemas:httpmail:datereceived"")%")
 oOlResults.Restrict("@SQL=%thismonth(""urn:schemas:httpmail:datereceived"")%")

More info here

Upvotes: 2

Related Questions