Track email through the draft/send/sent process

Question

I'd like to follow the status of an email as it passes through the phases and folders of its life cycle, through "Drafts", "Outbox", and "Sent".

Of related interest is the ability to access existing emails to gather property info, such as sent time.

I've started with the included block of code. The Do Loop fails the moment the email is sent, because the variable disconnects from the email.

This causes the runtime error

The item has been moved or deleted.

The error number (Err.Number) is different every time, and I wonder what the design purpose is.

How can I stay connected to sending emails as they move through Drafts, Outbox, and Sent?

I see many mentions of the variable disconnecting from the mailitem, but no solutions that rely on the object hierarchy and avoid late-binding to address the issue. I thought perhaps there would be a GUID or UUID that identifies the message, but as indicated in the documentation, all properties such as EntryID can change if the item is moved, and such properties should not be relied on.

With deeper examination, this makes sense because an email is just a record in a database table. And if you duplicate/delete records between tables, the info might be the same or similar, but the record number probably won't be.
Also, that hits other nails: the same email can be sent multiple times, and also can be copied/pasted into different folders, and even different accounts. Now what's unique or not?

Aside from staying "connected" to a email, what properties or techniques can be used to ID one?

If there's no "proper" way to identify a mailitem as described, about the only thing I can think of is to use an existing or custom field, like the "Tag" property of OCX controls, to insert a UUID. Some companies use this sort of technique by putting a call/order/support number in the subject line to make then easier to track.

Dim outlobj As Outlook.Application
Dim mailobj As Outlook.MailItem
Set outlobj = Outlook.Application
Set mailobj = outlobj.CreateItem(olMailItem)
With mailobj
    .Recipients.Add "[email protected]"
    .Subject = "Invisible Jet Scheduled Maintenance Reminder"
    .Body = "Your invisible jet need to be polished."
    .Attachments.Add zipFilename
    .Display
    .Send
End With

Do
    'next line fails due to email moving through Drafts, Outbox, & Sent
    'notably, the VBA runtime Err.Num is different each time
    'how do i keep the variable connected to a moving target?
    If mailobj.Sent = False Then
        Sleep 100
    Else
        MsgBox "The email has been sent."
        'other code
        Exit Do
    End If
Loop

Create a class and add MailItem as the event enabled property of that class. Handle the events such as Open/Write/Send/Save etc. to have custom control on the e-mail life-cycle. EntryID is the unique property for each mail item. — cyboashu

cyboashu cyboashu · Accepted Answer · 2017-11-08T22:33:34

Create a class and add MailItem as the event enabled property of that class. Handle the events such as Open/Write/Send/Save etc. to have custom control on the e-mail life-cycle. EntryID is the unique property for each mail item.

Be cautious of the fact that Entry Id is only generated after the first save of the item and changes implicitly when user manually moves the item between folders.

Following a is an example to get you started:

Add a class Class1 like this

Option Explicit

Public WithEvents mItem As MailItem
Public id               As String

Private Sub mItem_Open(Cancel As Boolean)
    MsgBox "Mail item will be displayed."
    id = mItem.EntryID
End Sub

Add a module with following code:

Option Explicit

Sub test()

    Dim cls As New Class1
    Dim id  As String

    Dim outlobj As Outlook.Application
    Dim mailobj As Outlook.MailItem
    Set outlobj = Outlook.Application
    Set mailobj = outlobj.CreateItem(olMailItem)

    Set cls.mItem = mailobj

    With mailobj
    .Recipients.Add "[email protected]"
    .Subject = "Test"
    .Body = "Test Content of the e-mail."
    .Save
    .Display
    id = cls.id '/ Store ID for later use.
    Debug.Print id
    End With


  '/ Search that e-mail and display its body contents
  Call Retrieve(id)


End Sub


Sub Retrieve(sEntryId As String)
    Dim mailobj As Outlook.MailItem
    Dim ns As NameSpace

    Set ns = GetNamespace("MAPI")
    Set mailobj = ns.GetItemFromID(sEntryId)
    MsgBox mailobj.Body

End Sub

Run the sub test

Track email through the draft/send/sent process

1 Answers