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