Outlook Email Travel Time Report

• Do you know how long an email takes to get to you?  
• How long from the sender clicking [SEND] until it comes to rest in your inbox?  
• Is there a problem with your email provider?
• Is there a problem with your hosted SPAM provider - are they adding a delay? 

{This information applies to the Microsoft Outlook email client.  Specifically I'm using Outlook 2013}

If you are asking yourself any of these questions you can look at the "Received" time in your inbox, then open the email and compare it to the "Sent" time that it usually displays once open.  But if you are constantly having issues you may want a better faster way to look at a lot of emails quickly, you can do this with a Macro.

I'm not going to dive into what macro's are; if you have no clue then do a search (Search Google "How to use Outlook macros" or Here is a fine article on how to get started.  I just want to share the macro and I'll give you the quick and dirty steps to get it installed.

What does the macro do?
This macro will produce a report of all messages that you have highlighted in your inbox.  Simply highlight the email messages you want to get a report of sent-and-received times and a new email window will open and show the results.  Here is a sample:

Subject:               Critical Alert for your ProLiant Servers and Options

From:                    alerts@alerts.hp.com

Sent on:               10/26/2015 6:45:12 PM

ReceivedTime: 10/26/2015 7:50:38 PM

Limbo Time:       65 Minutes  26 Seconds

X-Katharion-ID: 144590403.16557.cal1-mh778 (0.0)

---------------

Subject:               Find holiday items in high demand.

From:                    eBay@reply1.ebay.com

Sent on:               10/26/2015 2:50:30 PM

ReceivedTime: 10/26/2015 2:51:33 PM

Limbo Time:       1 Minutes  3 Seconds

X-Katharion-ID: 144588430.36058.cal1-mh782 (0.0)

---------------

Subject:               Join me at the Inaugural AerospaceDefenseChain Conference

From:                    AviationWeek@info.aviationweek.com

Sent on:               10/26/2015 2:07:53 PM

ReceivedTime: 10/26/2015 2:10:26 PM

Limbo Time:       2 Minutes  33 Seconds

X-Katharion-ID: 144582874.74400.ams1-mh928 (0.0)

This report will be shown in a new email form - simply because it is a quick and easy way to do it.  You can cut and paste it to Word or something if you want to print it, or you can quickly send it to someone via the email.  What I'm calling "Limbo Time" is the difference of "Sent on" and "Received Time."  If you want more info on where the delay happened you will want to checkout the email header.

For me, we use MaxMail / MaxFocus from LogicNow for Spam filtering (used to be GFI) and they were introducing a long delay in us getting our email.  Sometimes more than an hour, so I needed a quick way to keep an eye on the problem.  The X-Katharion-ID tag that I include in the report is what they use for message tracking, so I included it so I can send them this info and ask "What's up with the delay today?"  If  you don't need that simply remove it from the script.

Most of the base code I scrounged from the Internet and then I added some math and some string operations. There is a function to get the email header that I didn't touch so I give credit to the original author on that one.  I'm a programmer (Cobol) from days long gone so I don't pretend to know exactly what and how this works and I'm sure there may be better and more efficient way to code it, but hey; it works.

When in Outlook press ALT-F11 to open the "Microsoft Visual Basic for Applications" windows.  Then paste this into the "Module 1" section or I think you can create a new one - I really don't know. I put mine in Module-1.

Sub SendReceiveTimes()
    Dim olItem As Outlook.MailItem, olMsg As Outlook.MailItem
    Dim strheader As String
    Dim xPos1 As Integer
    
    For Each olItem In Application.ActiveExplorer.Selection
     If Left(olItem.SenderEmailAddress, 2) = "/O" Then
     Else
        strheader = GetInetHeaders(olItem)
        xPos1 = InStr(strheader, "X-Katharion-ID")
        xPos2 = InStr(strheader, "Return-Path:")
        xDiff = xPos2 - xPos1
        If xPos1 > 5 Then
            XKID = Mid(strheader, xPos1, xDiff)
        Else
            XKID = "X-Katharion-ID: -none-"
        End If
     
        xBody = xBody & Chr(13) & "Subject: " & Chr(9) & olItem.Subject & Chr(13) & "From: " & Chr(9) & Chr(9) & olItem.SenderEmailAddress
        xBody = xBody & Chr(13) & "Sent on: " & Chr(9) & olItem.SentOn
        xBody = xBody & Chr(13) & "ReceivedTime: " & Chr(9) & olItem.ReceivedTime
         
        xLTs = DateDiff("s", olItem.SentOn, olItem.ReceivedTime)
        xLT = Int(xLTs / 60)
        xLTs = xLTs - (xLT * 60)
        xBody = xBody & Chr(13) & "Limbo Time: " & Chr(9) & xLT & " Minutes  " & xLTs & " Seconds"
         
        xBody = xBody & Chr(13) & XKID
        xBody = xBody & Chr(13) & "---------------" & Chr(13) & Chr(13)
    End If
    Next
    
    Set olMsg = Application.CreateItem(olMailItem)
    With olMsg
        .BodyFormat = olFormatPlain
        .Body = xBody
        .Display
    End With
    Set olMsg = Nothing
End Sub

Function GetInetHeaders(olkMsg As Outlook.MailItem) As String
    ' Purpose: Returns the internet headers of a message.'
    ' Written: 4/28/2009'
    ' Author:  BlueDevilFan'
    ' http://techniclee.wordpress.com/
    ' Outlook: 2007'
    Const PR_TRANSPORT_MESSAGE_HEADERS = "http://schemas.microsoft.com/mapi/proptag/0x007D001E"
    Dim olkPA As Outlook.PropertyAccessor
    Set olkPA = olkMsg.PropertyAccessor
    GetInetHeaders = olkPA.GetProperty(PR_TRANSPORT_MESSAGE_HEADERS)
    Set olkPA = Nothing

End Function

Now just highlight the emails that you want to create a report on and run the macro "SendReceiveTimes." I created a shortcut to it on my Quick Access Tool Bar so I can run a quick report anytime.  

A few things to know and a few issues to avoid. 

1. You can't include calendar invites - it will cause an error.  Skip those.   
2. This excludes internal messages.  You can highlight them, but it skips them
3. You can't go crazy and highlight hundreds of messages, it will error out.  Keep it to a few days.  

report on send and receive times
email delay report
long email delays
isp email delays
emails take a long time to deliver
email header times
email travel time