Setting email as reply to the same email subject

Question

I have previously succeeded (with the help of PEH) to create a macro that attaches the address email and workbook to the right sender. The code is in the following link address Return value of dynamically determined cell and I will pasted also below.

My manger now wants me to attach the conversation of the specific email subject (history of replies) under this macro. So when the recipient receives the email from the sender and replies by pressing the button, the email should be attached to the same Email Subject and add the history between the two parties, not just the workbook document.

I found this Excel VBA, how to Reply to a specific email message , but I am not able to understand how can I set that to my own code.

Can this be done to what I have or the structure of the code should be change?

Sub mail()

Dim A As Long
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim wb As Workbook

Dim check

Set wb = Excel.ActiveWorkbook
Set sh1 = wb.Worksheets(1)
Set sh2 = wb.Worksheets(2)

For A = 2 To sh1.Cells(Rows.Count, "A").End(xlUp).Row
    check = Application.match(sh1.Cells(A, 1).Value, sh2.Columns(1), 0)

    If IsError(check) And Not IsEmpty(sh1.Cells(A, 1)) Then
        MsgBox "No email was found!"
    Else
        h = sh2.Cells(check, 2).Value


        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.createItem(olmailitem)
        Set wb2 = ActiveWorkbook
        wb.Save

        With OutMail
            .Display
            .To = h
            .cc = ""
            .BCC = ""
            .Subject = "Test - " 
            .htmlbody = "<p style='font-family:calibri;font-size:15'>" & "Hi " & C & "<BR/>" & "<BR/>" & "Please check the attached template." & "<br/>" & "<BR/>" & "Change data if required." & "<BR/>" & "<br/>" & "This e-mail has been automatically send! " & "<br/>" & "<br/>" & "With best regards," & "<br/>" & "<br/>"
            .attachments.Add wb2.FullName
        End With

        wb.Close
    End If
Next

End Sub

Yes, you can incorporate the reply code to your code. You will have to create a routine though that finds the email you want to reply to. If not found, you will have to create a new mail. If you have a specific subject or sender I think that would be easy. — L42, Apr 03 '18 at 09:39
So you want that your code "Answers" to a previously received email? Then how can we find out **which** email in your inbox it is that your code needs to answer at? — Pᴇʜ, Apr 03 '18 at 10:03
no, it doesn't have to take the email address as this can be inserted manually after the outlook window is opened. I was interested to find the email subject in the inbox history, take it and attached it to the new email so the users can see the email history between them. — alex2002, Apr 03 '18 at 10:37
@alex2002 what happens if there is more than one email with that subject? — Pᴇʜ, Apr 03 '18 at 11:33
it won't as the client has its specific material number for the specific template sent. So the way it should work: I press the button, opens the outlook, the subject with the specific material will be introduced + the email address and then press send. When the client replies, he should be able to press another button, open outlook and attached the previous email received (from, me), then he should insert the email address and press send. I don't know how this can be achieved as it seems to complicated. — alex2002, Apr 03 '18 at 11:41
Well you would need to loop through the emails as in the link in your question `For Each olMail In Fldr.Items` and check if your `olMail.Subject` is found. If so you can `olMail.Reply` to that email attaching your file otherwise (no subject found) you can create a new email `Set OutMail = OutApp.createItem(olmailitem)` as you already did in your code. Everything you need is in the link you posted. You are on the right track just go ahead. — Pᴇʜ, Apr 03 '18 at 12:02

score 1 · Accepted Answer · answered Apr 04 '18 at 06:33

you can use the .Find method to find a specific subject and then you can answer to that subject if it was found or create a new email if the subject was not found.

Sub mail()

    Dim A As Long
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim wb As Workbook

    Dim check

    Set wb = Excel.ActiveWorkbook
    Set sh1 = wb.Worksheets(1)
    Set sh2 = wb.Worksheets(2)

    For A = 2 To sh1.Cells(Rows.Count, "A").End(xlUp).Row
        check = Application.Match(sh1.Cells(A, 1).Value, sh2.Columns(1), 0)

        If IsError(check) And Not IsEmpty(sh1.Cells(A, 1)) Then
            MsgBox "No email was found!"
        Else
            h = sh2.Cells(check, 2).Value


            Set OutApp = CreateObject("Outlook.Application")

            'check if we can answer
            Dim OutNs As Namespace
            Set OutNs = OutApp.GetNamespace("MAPI")
            Dim OutFldr As MAPIFolder
            Set OutFldr = OutNs.GetDefaultFolder(olFolderInbox) 'default inbox folder (where we want to search for the subject)

            Dim OutMail As Variant
            Set OutMail = OutFldr.Items.Find("[Subject] = """ & "YOUR SUBJECT YOU WANT TO ANSWER TO" & """") 'search for specific subject
            If Not (OutMail Is Nothing) Then
                'we found something to reply to
                OutMail.Reply
            Else
                'we found nothing … so create new mail
                Set OutMail = OutApp.CreateItem(olMailItem)
            End If

            Set wb2 = ActiveWorkbook
            wb.Save

            With OutMail
                .Display
                .To = h
                .CC = ""
                .BCC = ""
                .Subject = "Test - "
                .HTMLBody = "<p style='font-family:calibri;font-size:15'>" & "Hi " & c & "<BR/>" & "<BR/>" & "Please check the attached template." & "<br/>" & "<BR/>" & "Change data if required." & "<BR/>" & "<br/>" & "This e-mail has been automatically send! " & "<br/>" & "<br/>" & "With best regards," & "<br/>" & "<br/>"
                .Attachments.Add wb2.FullName
            End With

            wb.Close
        End If
    Next

End Sub

you are the master. Thanks! – alex2002 Apr 04 '18 at 14:01 — alex2002, Apr 04 '18 at 14:01

score 0 · Answer 2 · answered Apr 03 '18 at 15:43

0

Instead of using OutApp.createItem(olmailitem), take the currently selected email (OutApp.ActiveExplorer.Selection(1)) and call Reply on it - it will return a new MailItem object with the subject, body, and recipients populated appropriately. You will only need to attach the file to it.

Set OutMail = OutApp.ActiveExplorer.Selection(1).Reply
Set wb2 = ActiveWorkbook
wb.Save
With OutMail
   .attachments.Add wb2.FullName
   .Display
End With

answered Apr 03 '18 at 15:43

Dmitry Streblechenko

62,942
4
53
78

Well, this will always answer to the selected email while the OP wants to answer "*to the same Email Subject*" he generates in Excel. So he will still need to loop through the inbox to find the email with that subject before he can make use of the above code. – Pᴇʜ Apr 04 '18 at 06:08

Setting email as reply to the same email subject

2 Answers2