Check if you have replied to client email via VBA

Question

I want to check if my team has responded to the client's email.

Dim O As Outlook.Application
Dim R As Long

Sub project2()
Set O = New Outlook.Application
Dim Omail As Outlook.MailItem
Set Omail = O.CreateItem(olMailItem)
Dim ONS As Outlook.Namespace
Set ONS = O.GetNamespace("MAPI")

Dim FOL As Outlook.Folder
Set FOL = ONS.GetDefaultFolder(olFolderInbox).Folders("MD-GPS")
R = 2
For Each Omail In FOL.Items

    Cells(R, 1) = Omail.Subject
    Cells(R, 2) = Omail.SenderEmailAddress
    
    Call REPLY_STATUS(Omail.Subject, Omail.SenderEmailAddress)
            
    R = R + 1
    On Error Resume Next
Next Omail
End Sub

Sub REPLY_STATUS(MailSubject As String, MailSender As String)

Dim SentEmail As Outlook.MailItem
Set SentEmail = O.CreateItem(olMailItem)

Dim ONS2 As Outlook.Namespace
Set ONS2 = O.GetNamespace("MAPI")

        
Dim FOL2 As Outlook.Folder
Set FOL2 = ONS2.GetDefaultFolder(olFolderSentMail)
Dim check As String
check = "RE: " & MailSubject

For Each SentEmail In FOL2.Items

    If check = SentEmail.Subject And MailSender = SentEmail.Recipients(1).Address Then
        Cells(R, 3) = "Yes"
        Exit For
    Else
    End If
    On Error Resume Next
Next SentEmail

End Sub

1. In most cases, the code captures something other than the sender's email address in an Excel sheet.
   

2. As we daily receive 500+ emails, the code becomes too slow as it checks the entire folder from scratch.
   Can I add a start date that I can mention in the Excel sheet and the code will start from that date.

3. The code is not filling column 3 if my team has replied to those emails.

4. The code is not picking up the latest emails from the defined sub-folder ("MD-GPS").

Note: To handle SMPT exchange account error, I tried MailItem.Sender.GetExchangeUser.PrimarySmtpAddress but if I change the sub-folder to something else, it doesn't work.

Answer 1

Firstly, you do not need to create SentEmail - get rid of the

Set SentEmail = O.CreateItem(olMailItem)

line.

Secondly, never loop through all items in a folder - use Items.Find/FindNext or Items.Restrict.

Thirdly, you are seeing an EX type address (as opposed to SMTP). Check MailItem.Sender.Type property - if it is "SMTP", use MailItem.Sender.Address. Otherwise (in case of "EX") use MailItem.Sender.GetExchangeUser().PrimarySmtpAddress.

That being said, you can check if anybody replied to the original message at all - check if PR_LAST_VERB_EXECUTED MAPI property (DASL name http://schemas.microsoft.com/mapi/proptag/0x10810003) is present - 103 is EXCHIVERB_REPLYTOALLand 102 is EXCHIVERB_REPLYTOSENDER. If the property is not set at all, there is no reason to search.

To search for a matching subject, use a query like

"[Subject] = ' & MailSubject  & "'"

Note that Outlook Object Model will not let you search on the message recipients or attachments. If using Redemption (I am its author) is an option, you can use something like the following. You can specify Recipients as one of the search fields, and Redemption will create a restriction on recipient name / address / SMTP address

set session = CreateObject("Redemption.RDOSession")
session.MAPIOBJECT = O.Session.MAPIOBJECT
set SentEmail = FOL2.Items.Find("""http://schemas.microsoft.com/mapi/proptag/0x0E1D001F"" = '" & MailSubject & "' AND Recipients = '" & MailSender & "'")

Note that most MAPI stores won't search on PR_SUBJECT, only on PR_NORMALIZED_SUBJECT (which is the subject without the RE: or FW: prefix) - which is what the snippet above is using.