title: How to uniquely identify an Outlook email as MailItem.EntryID changes when email is moved
body: <p>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.</p>
  
  <p>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".</p>
  
  <p>So email comes into Inbox, gets moved to my_tasks\joeblow is dealt with and gets moved to Completed\Canada.</p>
  
  <p>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.</p>
  
  <pre><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='" &amp; objMailItem.EntryID &amp; "'"
  
      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 &amp; " " &amp; 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 #: " &amp; Err.Number &amp; "  " &amp; Err.Description '&amp; Chr(13) + Chr(10) &amp; IIf(mail.Subject Is Null, "", mail.Subject) &amp; " " &amp; IIf(mail.ReceivedTime Is Null, "", mail.ReceivedTime)
  '            DoCmd.RunSQL "INSERT INTO tblImportReport(ImportDate,ImportFolder,ImportResult,ImportEmailCount) VALUES (#" &amp; Now() &amp; "#,'" &amp; mailFolder &amp; "', 'Error " &amp; Err.Number &amp; "', " &amp; dMailCount &amp; ")"
              Resume Next 'cmdImportEmail_Exit
      End Select
  
  End Sub
  </code></pre>
  
  <p>Is there a way to uniquely identify an email with a single field no matter whether it has been moved or not?</p>
  
  <p>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.</p>