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
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.