How can I look at emails after a certain date?

Question

This Outlook VBA code searches all emails in my Outlook subfolder, and then pulls the "Subject", "Date", "Creation Time", and "Body" of the email into an Excel file.

How can I implement some code that will look at emails after a certain date (e.g. 10/1/2022)?

My current code:

Sub List_Email_Info()

    'Create excel object variables
     Dim xlApp As Excel.Application
     Dim xlWB As Excel.Workbook
     Dim i As Long  'Row Tracker
     Dim arrHeader As Variant

    'Create outlook object variables
     Dim olNS As NameSpace
     Dim olInboxFolder As MAPIFolder
     Dim olItems As Items
     Dim olMailItem As MailItem

     'store header names
     arrHeader = Array("Date Created", "Subject", "Sender's Name", "Body")

     'Create excel object's isntance
     Set xlApp = CreateObject("excel.Application")
     xlApp.Visible = True
     Set xlWB = xlApp.Workbooks.Add

     'Set outlook variables
     Set olNS = GetNamespace("MAPI")
     Set olInboxFolder = olNS.GetDefaultFolder(olFolderInbox).Folders("Law360 Alerts")
     Set olItems = olInboxFolder.Items

    'Assign role value to i variable
    i = 1

    On Error Resume Next
    xlWB.Worksheets(1).Range("A1").Resize(1, UBound(arrHeader) + 1).Value = arrHeader

    'iteriate each item from the olItems object
    For Each olMailItem In olItems

        xlWB.Worksheets(1).Cells(i + 1, "A").Value = olItems(i).CreationTime
        xlWB.Worksheets(1).Cells(i + 1, "B").Value = olItems(i).Subject
        xlWB.Worksheets(1).Cells(i + 1, "C").Value = olItems(i).SenderName
        xlWB.Worksheets(1).Cells(i + 1, "D").Value = olItems(i).Body

        i = i + 1

    Next olMailItem

    'Autofit columns
    xlWB.Worksheets(1).Cells.EntireColumn.AutoFit

    'Display a messagebox when complete
    MsgBox "Export Complete.", vbInformation

    'Empty out the objects
    Set xlWB = Nothing
    Set xlApp = Nothing

    Set olItems = Nothing
    Set olInboxFolder = Nothing
    Set olNS = Nothing
End Sub

To answer your question though, seems like you should look at `CreationTime` — Tim Williams, Oct 18 '22 at 00:42
@TimWilliams Thanks. I was thinking about that. I could change it to: `xlWB.Worksheets(1).Cells(i + 1, "A").Value = olItems(i).ReceivedTime` - If so, could I use an If statement within the For statement? For example: ` If olMailItem >= "date" Then ` ? — CyberCoder, Oct 18 '22 at 00:53
You're using a `For Each olMailItem` loop but then looking at `olItems(i)` - you should be working with `olItem` inside the loop. — Tim Williams, Oct 18 '22 at 00:59
Once you misuse `On Error Resume Next` your probability of coding success approaches zero. You cannot even ask about the problem because it is hidden. — niton, Oct 18 '22 at 13:40
[Restrict Outlook Items by Date](https://stackoverflow.com/questions/38310690/restrict-outlook-items-by-date) — niton, Oct 29 '22 at 19:02

Eugene Astafiev · Accepted Answer · 2022-10-18T15:55:54.197

Iterating over all items in the folder is not really a good idea:

 'iteriate each item from the olItems object
    For Each olMailItem In olItems

how to implement some code that will only look at emails after a certain date (e.g. 10/1/2022).

You need to use the Find/FindNext or Restrict methods of the Items class that allows getting only items that correspond to the search criteria. Here is an example of possible search criteria:

'All three filters shown below will return the same results 
'This filter uses DASL date macro for today 
strFilter = "%today(" _ 
& AddQuotes("urn:schemas:httpmail:datereceived") & ")%"

or

'This filter uses urn:schemas:httpmail namespace 
strFilter = AddQuotes("urn:schemas:httpmail:datereceived") _ 
& " > '" & datStartUTC & "' AND " _ 
& AddQuotes("urn:schemas:httpmail:datereceived") _ 
& " < '" & datEndUTC & "'"

Outlook evaluates date-time values according to the time format, short date format, and long date format settings in the Regional and Language Options applet in the Windows Control Panel. In particular, Outlook evaluates time according to that specified time format without seconds. If you specify seconds in the date-time comparison string, the filter will not operate as expected.

Although dates and times are typically stored with a date format, filters using the Jet and DAV Searching and Locating (DASL) syntax require that the date-time value to be converted to a string representation. In Jet syntax, the date-time comparison string should be enclosed in either double quotes or single quotes. In DASL syntax, the date-time comparison string should be enclosed in single quotes.

To make sure that the date-time comparison string is formatted as Microsoft Outlook expects, use the Visual Basic for Applications Format function (or its equivalent in your programming language).

See Filtering Items Using a Date-time Comparison for more information.

Read more about the Find/FindNext and Restrict methods in the articles I wrote for the technical blog:

If you need to search for items in multiple folders you may consider using the AdvancedSearch method of the Application class, see Advanced search in Outlook programmatically: C#, VB.NET.

How can I look at emails after a certain date?

1 Answers1

Linked