{"id":49287,"date":"2023-02-27T19:32:25","date_gmt":"2023-02-27T18:32:25","guid":{"rendered":"https:\/\/ekiwi-blog.de\/49287\/vba-get-date-and-time-from-outlook-e-mail\/"},"modified":"2023-02-27T19:40:34","modified_gmt":"2023-02-27T18:40:34","slug":"vba-get-date-and-time-from-outlook-e-mail","status":"publish","type":"post","link":"https:\/\/ekiwi-blog.de\/en\/49287\/vba-get-date-and-time-from-outlook-e-mail\/","title":{"rendered":"VBA: Get date and time from Outlook e-mail"},"content":{"rendered":"

VBA<\/abbr> 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.<\/p>\n

VBA code<\/h2>\n

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:<\/p>\n

Sub<\/span> TimeOfEMail()\r\n    Dim<\/span> objOL As<\/span> Outlook.Application\r\n    Dim<\/span> objMsg As<\/span> Outlook.MailItem\r\n    Dim<\/span> objSelection As<\/span> Outlook.Selection\r\n    Dim<\/span> receivedDateTime As<\/span> Date\r\n    ' Outlook Application object<\/span>.\r\n    Set<\/span> objOL = CreateObject(\"Outlook.Application\")\r\n    ' Get collection of selected objects (emails)<\/span>.\r\n    Set<\/span> objSelection = objOL.ActiveExplorer.Selection\r\n    'Use first selected email<\/span>.\r\n    Set<\/span> objMsg = objSelection.Item(1)\r\n    receivedDateTime = objMsg.ReceivedTime\r\n    'to something with DateTime Value<\/span>.\r\n    Debug.Print<\/span> receivedDateTime\r\nEnd Sub<\/span><\/code><\/pre>\n
The first part is just about finding the right VBA object that represents the email (objMsg<\/em>). Once you have that, it’s quite simple. Because the MailItem<\/em> object has a property called “ReceivedTime<\/em>“, which contains the date and time. Via Debug.Print, it is output in this case in the console window or direct window.<\/p>\n
\"Screenshot
Code and output in the console window<\/figcaption><\/figure>\n
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:<\/p>\n
MyDate = Format(receivedDateTime, \"dddd, mmm d yyyy\")\r\n' results in \"Monday, Feb 27 2023\"<\/span>.\r\nDebug.Print<\/span> MyDate<\/code><\/pre>\n
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<\/a>.<\/p>","protected":false},"excerpt":{"rendered":"
VBA is very useful for automating daily, recurring tasks. If you use VBA in the context of Microsoft Outlook to<\/p>\n","protected":false},"author":2,"featured_media":6532,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1555],"tags":[2714,2713,2252,1707,1703,1704,2168,1700,1705,2715,1706,1558],"class_list":["post-49287","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-software-en","tag-automate","tag-automatic","tag-code-en","tag-macro","tag-microsoft-en","tag-microsoft-office-en","tag-office-en","tag-programming","tag-vba-en","tag-vba-code-en","tag-visual-basic-for-application-en","tag-windows-en"],"_links":{"self":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/posts\/49287","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/comments?post=49287"}],"version-history":[{"count":0,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/posts\/49287\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/media\/6532"}],"wp:attachment":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/media?parent=49287"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/categories?post=49287"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/tags?post=49287"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}