VBA: Get date and time from Outlook e-mail

VBA - Visual Basic for Application

VBA is very useful for automating daily, recurring tasks. If you use VBA in the context of Microsoft Outlook to simplify your daily work with e-mails or to transfer form data from e-mails to an Excel spreadsheet with one click, you may sometimes need to read out the date and/or time when you received the e-mail.

VBA code

This is even relatively simple and can be done with just a few lines, once you have the mail object available in the VBA code. The corresponding Visual Basic code looks like this:

Sub TimeOfEMail()
    Dim objOL As Outlook.Application
    Dim objMsg As Outlook.MailItem
    Dim objSelection As Outlook.Selection
    Dim receivedDateTime As Date
    ' Outlook Application object.
    Set objOL = CreateObject("Outlook.Application")
    ' Get collection of selected objects (emails).
    Set objSelection = objOL.ActiveExplorer.Selection
    'Use first selected email.
    Set objMsg = objSelection.Item(1)
    receivedDateTime = objMsg.ReceivedTime
    'to something with DateTime Value.
    Debug.Print receivedDateTime
End Sub

The first part is just about finding the right VBA object that represents the email (objMsg). Once you have that, it’s quite simple. Because the MailItem object has a property called “ReceivedTime“, which contains the date and time. Via Debug.Print, it is output in this case in the console window or direct window.

Screenshot VBA editor with code to read the date and time of receipt of an email
Code and output in the console window

If the standard output is not enough, you can format the date and time of receipt with the format function. This could then look like this:

MyDate = Format(receivedDateTime, "dddd, mmm d yyyy")
' results in "Monday, Feb 27 2023".
Debug.Print MyDate

Further and more detailed information on the different ways to output date and time formatted can be found in the Microsoft documentation on the format function.

Leave a Reply

Your email address will not be published. Required fields are marked *