How to uniquely identify an Outlook email as MailItem.EntryID changes when email is moved

Question

My company uses a single email address for customers to send requests and orders to. we created an Access database that import emails into a table. The table creates it's own unique identifier for each email imported but is not supposed to import an email twice. The system was working as we were only concerned with emails coming into the inbox and didn't need anything more than that.

However we now need to know the "flow", "traffic" and "workload" of the email pool that this account is. The email that comes into the inbox is categorized and then moved to a folder called "my_tasks" and a subfolder the folder named as 1 of the four CSRs to be worked on by a manager. This email is then dealt with and the CSR moves it to a subfolder under another folder called "Completed".

So email comes into Inbox, gets moved to my_tasks\joeblow is dealt with and gets moved to Completed\Canada.

Currently I have code that iterates through the folders and finds each email, grabs the fields we want to store and then inserts them into the table. All of this is done in Access through VBA code.

Private Sub ImportEmailItem(objMailItem As Outlook.MailItem)
On Error GoTo ImportEmailItem_Error

    ' Set up DAO objects
    Dim rstMB As DAO.Recordset
    Dim dskippedFolderMailCount As Double
    Dim strSQLrMB As String

    strSQLrMB = "SELECT * FROM tblMailBox WHERE OLID='" & objMailItem.EntryID & "'"

    Set rstMB = CurrentDb.OpenRecordset(strSQLrMB)

        With rstMB
            If Not .BOF And Not .EOF Then

                .MoveLast
                .MoveFirst
                While (Not .EOF)
                    If .Updatable Then
                        .Edit
                            rstMB!Subject = objMailItem.Subject
                            rstMB!Body = objMailItem.Body

                            Call subCategory(objMailItem)

                            rstMB!CSR = IIf(Len(objMailItem.Categories) = 0, "Unassigned", objMailItem.Categories)
                            rstMB!Importance = objMailItem.Importance
                            rstMB!Region = objMailItem.Parent
                            rstMB!DateModified = objMailItem.LastModificationTime
                            rstMB!FlagCompleted = objMailItem.FlagRequest
                            rstMB!folder = objMailItem.Parent
                            rstMB!Path = objMailItem
                        .Update
                    End If
                .MoveNext
                Wend
            Else
                rstMB.AddNew
                    rstMB!olid = objMailItem.EntryID
                    rstMB!ConversationIndex = objMailItem.ConversationIndex
                    rstMB!ConversationID = objMailItem.ConversationID
                    rstMB!Conversation = objMailItem.ConversationTopic
                    rstMB!To = Left(objMailItem.To, 250)
                    rstMB!CC = Left(objMailItem.CC, 250)
                    rstMB!Subject = objMailItem.Subject
                    rstMB!Body = objMailItem.Body

                    Call subCategory(objMailItem)

                    rstMB!CSR = IIf(Len(objMailItem.Categories) = 0, "Unassigned", objMailItem.Categories)
                    rstMB!Importance = objMailItem.Importance
                    rstMB!From = objMailItem.SenderEmailAddress
                    rstMB!Region = objMailItem.Parent
                    rstMB!DateReceived = objMailItem.ReceivedTime
                    rstMB!DateSent = objMailItem.SentOn
                    rstMB!DateCreated = objMailItem.CreationTime
                    rstMB!DateModified = objMailItem.LastModificationTime
                    rstMB!FlagCompleted = objMailItem.FlagRequest
                    rstMB!folder = objMailItem.Parent
                rstMB.Update
            End If
            .Close
        End With

ImportEmailItem_Exit:
    Set rstMB = Nothing
    Exit Sub

ImportEmailItem_Error:
    Debug.Print Err.Number & " " & Err.Description

    Select Case Err.Number
        Case 91
            Resume Next
        Case 3022
            Resume Next
        Case -2147221233
            MsgBox "Customer Care Account Name is incorrect, please enter the Mail box name as seen in your outlook client.", vbOKOnly, "Mail Folder Name Error"
            Me.txtMailAccountName.SetFocus
            Exit Sub
        Case Else
            MsgBox "Error #: " & Err.Number & "  " & Err.Description '& Chr(13) + Chr(10) & IIf(mail.Subject Is Null, "", mail.Subject) & " " & IIf(mail.ReceivedTime Is Null, "", mail.ReceivedTime)
'            DoCmd.RunSQL "INSERT INTO tblImportReport(ImportDate,ImportFolder,ImportResult,ImportEmailCount) VALUES (#" & Now() & "#,'" & mailFolder & "', 'Error " & Err.Number & "', " & dMailCount & ")"
            Resume Next 'cmdImportEmail_Exit
    End Select

End Sub

Is there a way to uniquely identify an email with a single field no matter whether it has been moved or not?

I have an idea of what I could do to make sure I have the right email and get the original entry in my database. If there was no other way I could concatenate fields together to form a unique field and then get the database table's primary key field value.

Dmitry Streblechenko · Answer 1 · 2022-04-02T17:13:48.200

3

You can use the PR_SEARCH_KEY property (DASL name http://schemas.microsoft.com/mapi/proptag/0x300B0102) - it does not change when a message is moved. It can be accessed through MailItem.PropertyAccessor.GetProperty, but unfortunately you cannot use PT_BINARY properties in Items.Find/Restrict.

You can also set your own named property using MailItem.UserProperties.

UPDATE:

For PR_SEARCH_KEY, see https://msdn.microsoft.com/en-us/library/office/cc815908.aspx.

MaillItem.UserProperties can be used from anywhere - Outlook Object Model is Outlook Object Model whether it is used from inside Outlook or externally from Excel. Keep in mind that setting a user property and saving the item will change its last modified date.

If you want to stick to PR_SEARCH_KEY, to be be able to sort on it, you might want to look at Redemption (I am its author) - its RDOFolder.Items.Find / Restrict methods allow PT_BINARY properties in its queries, e.g. "http://schemas.microsoft.com/mapi/proptag/0x300B0102" = '89F75D48972B384EB2C50266D1541099'

edited Apr 02 '22 at 17:13

answered Feb 26 '15 at 00:19

Dmitry Streblechenko

62,942
4
53
78

I found those schemas properties in other forums and posts but I couldn't find a definitive list that gave me a property to use for what I wanted. Are they fully documented somewhere or does Microsoft expect us to figure them out on our own? – DawnTreader Feb 26 '15 at 15:38
Rats... Took too long to edit the previous comment. The mailitem.userproperties would be set where? And is adding my own property possible from Access or does it need to be done in Outlook? This reporting utility is all in Access. Would objMailItem.PropertyAccessor.GetProperty("Http://schemas.microsoft.com/mapi/proptag/0x300b0102") be available in Access? Or is that code that only works in Outlook? If I added a property to the objMailItem from Access, would it stick with the email no matter what? If it was moved from one folder to another would it loose that property? – DawnTreader Feb 26 '15 at 16:00
In looking for the quickest solution to my problem I am wondering if there is a way to set something on the exchange server that would create a unchangeable unique ID? If not would it be helpful to create a "macro" that runs on all received email in the outlook client that would add a field that stores an unchangeable unique identifier that I could then import into Access. We have an AD Account that is logged into on a computer that runs 24/7 with an Outlook client open to cause rules to run on the incoming emails. I could add the macro there and get it to "tag" each incoming email. – DawnTreader Feb 26 '15 at 18:29
No, there is no such Exchaneg server setting. What is wrong with a user property or PR_SEARCH_KEY? – Dmitry Streblechenko Feb 27 '15 at 14:03
Nothing, although I am not sure how to use it and I was hoping to avoid have to code something if I didn't have to. Looking at the link, what does "changeable by client after a copy" mean? My feeling is that this Property is no more reliable than the entryid or the recordkey if the email is moved from one folder to another... I need a rock solid always the same ID field. It baffles me that there isn't such a thing in Outlook. – DawnTreader Feb 27 '15 at 16:52
Nothing is rock solid. Some store providers (IMAP) do not support moves, so a message is created in the new folder and its properties are copied."changeable by client" means you can change it if you want. – Dmitry Streblechenko Feb 27 '15 at 16:55

score 1 · Answer 2 · answered Oct 05 '17 at 17:36

Here is VBA code tested in MS Access 2013 to extract the PR_SEARCH_KEY from an Outlook.MailItem and convert to a string:

Public Function strGetMailItemUniqueId( _
    olMailItem As Outlook.MailItem _
) As String
    Dim PR_SEARCH_KEY As String
    PR_SEARCH_KEY = "http://schemas.microsoft.com/mapi/proptag/0x300B0102"

    Dim olPA As Outlook.PropertyAccessor
    Set olPA = olMailItem.PropertyAccessor

    Dim vBinary As Variant
    vBinary = olPA.GetProperty(PR_SEARCH_KEY)

    strGetMailItemUniqueId = olPA.BinaryToString(vBinary)
End Function

score 1 · Answer 3 · answered Dec 10 '19 at 07:50

1

In Microsoft Outlook versions like 2007, 2010, Office 365 etc. there is a property Message-ID in the headers section of the email.

You can use this property to uniquely identify an email.

answered Dec 10 '19 at 07:50

VAT

170
2
4
20

How to uniquely identify an Outlook email as MailItem.EntryID changes when email is moved

3 Answers3

Linked