Excel VBA to reply to existing email chain and add workbook as attachment

[gopalgriffith]

All,
I am working on an "approval process" whereby colleagues will fill out a "Checklist" (an Excel workbook), and then click a button in the checklist, which generates an email (with the completed checklist attached) that then gets sent to a group mailbox for review and approval.

(Abridged) VBA code below:

Sub ApprovalRequest()
Set oWB = ActiveWorkbook

RequestFileName = "Fin Prom Checklist - " & Sheets("Checklist").Cells(3, 3).Text & " - " & Format(Sheets("Checklist").Cells(24, 3), "YYYYMMDD") & ".xlsm"

Subject = "Fin Prom Approval Request - " & Sheets("Checklist").Cells(3, 3) & " - " & Format(Sheets("Checklist").Cells(24, 3), "YYYYMMDD")
HTMLBody = "<b><font color=red><span style='background:yellow;mso-highlight:yellow'>PLEASE TAILOR THE EMAIL FURTHER AS APPROPRIATE.</font></span></b>"
HTMLBody = HTMLBody & "<br><br><p style='font-family:arial;font-size:13'><font color = black>Dear Compliance Team,<br>Please find attached a Financial Promotion approval request.<br>I will await your approval and/or any comments you may have.<br>Regards"

Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)

With objMail
.To = "abc@def.com"
.Subject = Subject
.HTMLBody = HTMLBody
oWB.Activate
oWB.SaveCopyAs Environ("temp") & "\" & RequestFileName
.Attachments.Add (Environ("temp") & "\" & RequestFileName)
.Display
End With

Set objOutlook = Nothing
Set objMail = Nothing
End Sub

The above code works beautifully.

Once an approval request comes in, the team will then correspond with the colleague / ask questions by replying to the email. Once we are ready to approve the request, the team will then click on a button in the checklist, which will record the approval in a separate register (works fine) and generate an email back to the colleague (again with the updated checklist as an attachment) confirming approval.

The problem is that the above code (or a variant thereof) works well to create a new email. But ideally, I would like approval email to be part of the existing email chain (we will know the subject line, as it was generated programmatically). That way, the entire interaction is in a single email chain, rather than the approval email being in a separate email from the rest of the interaction.

Does anyone know how I would be able to adapt the above code to reply to an existing email chain?
Cheers
Gopes

 

[gopalgriffith]

Any suggestions?

As a fudge/workaround, I have created some code to copy the workbook path and filename to the clipboard, from where we can use the 'add attachment' button in the email to add the attachment. But this is not as elegant, and means I have no means of programmatically adding text to the body of the email.

Cheers
Gopes

 

[Logit]

Looks like this might be an answer to your question : VBA to Reply All To Latest Email Thread
Post #8 seems to have nailed it with elegance.

If you were to give each new email, as it is first generated, a unique SUBJECT ... perhaps including a unique serial number that is assigned to the specific employee for that
email chain ... it would be a means of identifying which email should be used in the replies.

tr(objMail.Subject, "REQUEST FOR OVERTIME") <> 0 Then 'REQUEST FOR OVERTIME
                Set objConversation = objMail.GetConversation
                Set objTable = objConversation.GetTable
                objVar = objTable.GetArray(objTable.GetRowCount)
                Set objReplyToThisMail = olApp.Session.GetItemFromID(objVar(UBound(objVar), 0))
                With objReplyToThisMail.ReplyAll
                    strBody = "Hello " & "<br>" & _
                                "<p>Following up with the below. May you please advise?" & _
                                "<p>Thank you," & vbCrLf & vbCrLf & "<br>" & _
                                "<p>" & Session.CurrentUser.Name
                    .HTMLBody = strBody & .HTMLBody
                    .Display
                End With
                Exit For
            End If
        End If
    Next objMail
    
    Set olApp = Nothing
    Set olNs = Nothing
    Set Fldr = Nothing
    Set objMail = Nothing
    Set objReplyToThisMail = Nothing
    lngCount = Empty
    Set objConversation = Nothing
    Set objTable = Nothing
    If IsArray(objVar) Then Erase objVar
    
End Sub

 

[gopalgriffith]

Thanks for your help. Each approval request does indeed have a unique subject line.

This code seems to be able to do what I need (once I have adapted it). However, I do have a couple of follow-up questions to help me adapt this code:

1. The code is cycling through the items in my personal inbox (which normally would make sense). But I need to tweak the code to search a group mailbox instead. How do I tweak the "Set Fldr = olNs.GetDefaultFolder(olFolderInbox)" line to do this?
2. The "for each...next" loop starts with the oldest item in the folder and works up (in chronological order). The group mailbox has many thousands of emails, and so I (suspect - but can't test until I have figured out 1) above) that it will be slow to run - is there any way of starting with the most recent mail item and working backwards? This is also important because there may be several emails in the chain (with the unique subject line for that particular request) - I need to ensure the code is responding to the most recent email (if that makes sense).

Thoughts?
Gopes

 

[gopalgriffith]

So I have found a solution to point 1), by adding the following lines (in the relevant places) to my code:

    Dim objOwner As Outlook.Recipient
    Set objOwner = olNs.CreateRecipient("sharedmailbox@abc.com")
    objOwner.Resolve
    Set Fldr = olNs.GetSharedDefaultFolder(objOwner, olFolderInbox)

It now cycles through the group (shared) mailbox, and actually only takes a couple of seconds to reach to the most recent email (currently circa 3,000 emails in the inbox).

Does anyone have any suggestions on how to loop through, but starting with the most recent email. This is important, as I need to ensure that the code is responding to the most recent email (with a specific subject line). N.b. because there will be interim manual email exchanges as part of the approval process, I have no means of adding a specific unique string to the subject for each separate email exchange within a chain).

Any ideas?