In order to work with e-mails in MS Outlook VBA, it's necessary to know the times that certain events occurred for each e-mail, such as when it was sent, when it was received, etc. Outlook appears to have four timestamps for this purpose, which are properties of the MailItem object, three of which have corresponding Messaging API (MAPI) properties:
| MailItem property | MAPI property | Description |
|---|---|---|
| SentOn | PidTagClientSubmitTime | Set by sender's system when sent. |
| ReceivedTime | Set by receiving system when received. | |
| CreationTime | PidTagCreationTime | When the record of the e-mail was first stored in Outlook. |
| LastModificationTime | PidTagLastModificationTime | When the record in Outlook was last modified. |
The descriptions above are my brief paraphrase of what I read in the documentation at the given links. According to these descriptions, the above table should usually be in chronological order, i.e., each timestamp should usually be equal to or later than the one listed before it. On an analysis of 107 e-mails sent and received over a span of two days, I find the following:
- The difference between the time received and the time sent:
- For e-mails I sent, the received time should be null because my system cannot know when, or even if, the e-mail was received. But instead, the received time is always equal to the sent time. This is logically flawed, but at least it's internally consistent.
- For e-mails I received, this should always be positive and fairly small.
- The difference is sometimes up to two minutes negative. While appearing to mean illogically that the e-mail was received before it was sent, this can be caused by the clocks of the sending and receiving systems not being in synch, which can easily happen.
- The difference is usually between zero and ten minutes, which is what it should be. Zero appears to indicate lightning fast transmission.
- The difference is sometimes between one and seven hours. It seems surprising that e-mails would ever take that long to arrive, but almost all these cases are on spam e-mails, so I'm guessing that the senders are either on a slow connection or their clocks are set wrong.
The above all make sense. But there are several surprises when I look at the creation and modification times:
The difference between the creation time and received time.
- For e-mails I sent, this is always zero or negative because Outlook stores a record of the e-mail as a draft before it is sent. However, the difference should often be very large because I often draft e-mails and let them sit for hours or overnight for review before sending. But in the two days of e-mails that I checked, this difference for e-mails that I sent is never more than an hour, so there seems to be something wrong here.
- For maintenance e-mails that my system sends to itself, this difference is positive and between a few minutes and up to 40 minutes. This seems strange because it seems that these should be instantaneous.
- For e-mails that I received, this difference should always be positive and I would think it should be very short, like less than a second. But:in fact, it is seldom less than a minute and is often several hours, even as much as 22 hours. These long delays are longest and most frequent for junk e-mails, which is strange because I don't use automated junk handling but manually move junk e-mails to a junk folder, so their processing times shouldn't be any different from other e-mails.
The difference between modification time and creation time:
- For e-mails that I sent, this should always be positive and should often be large because of changes I make long after the draft is first saved. It should usually be shorter than the (negative) difference between the creation and sent (=received) times because I can't change an e-mail after it's sent except by deleting an attachment. But it seems that this difference is actually usually longer than the creation/sent difference, and I don't understand why.
- For e-mails that I received, this should usually be zero, but can be positive and of any size if I deleted an attachment. That is consistent with what I see.
These observations were made on my computer running MS Office LTSC Pro Plus 2021 under Windows 11 Pro 64, both with the latest updates.
These surprises suggest that my descriptions in the above table are not accurate. Can someone explain what these timestamps really mean? A proper understanding of their algorithmic definitions would help in writing code to effectively manipulate e-mails.
