Replying to Email based off Excel Value

Question

I have come to a roadblock in my automation procedure. Will outline below:

Created form to be available online & allow users to fill Excel form and click submit (Upon submission e-mail will be sent & fields will be transposed to tracker in shared drive)
E-mail will appear in inbox and Outlook rules will sort e-mails into different folders based off Subject line

Now the part I am stuck at

In my tracker I have some rules in order to arrive at a decision. In a nutshell, based off a specific cell value (Column H), I need to respond to the original e-mail with predefined a templates(Template has 3 fields referenced from userform).

The part I am stuck at is how do I reply to the original email in outlook with my automated decision?

Please help :)

https://stackoverflow.com/questions/31817632/outlook-reply-to-an-email — braX, Oct 05 '17 at 18:48
@braX The only issue is that my pre-defined template will use 3 fields from the original excel userform. Above solution would only reply to email no? A little new to VBA but I think upon receipt of new email I need to trigger another macro maybe? — selvend2, Oct 05 '17 at 19:02
@selvend2 Seems to me like there are more than 1 question here - Could you please be a bit more specific? **What exactly are you trying to do?** — dwirony, Oct 05 '17 at 19:13
1. Create electronic form in Excel 2. Use Macro to send email and transpose form fields to another Excel File 3. Use Outlook rules to sort E-mails automatically 4. Reply to e-mail with Template based off Form fields (So template will be different for every case) — selvend2, Oct 05 '17 at 19:36
I've solved 1,2, and 3. Stuck on the 4th and final step of my automation. — selvend2, Oct 05 '17 at 19:59

score 0 · Answer 1 · answered Oct 06 '17 at 01:12

So, you want to send an email if a cell has a specific value, right. Please try the script below.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
        If IsNumeric(Target.Value) And Target.Value > 200 Then
            Call YourMacroName
        End If
    End If
End Sub

Sub Mail_small_Text_Outlook()
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Working in Excel 2000-2016
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "Cell A1 is changed" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"

    On Error Resume Next
    With OutMail
        .To = "ron@debruin.nl"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Display   'or use .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

https://www.rondebruin.nl/win/s1/outlook/bmail9.htm

Replying to Email based off Excel Value

1 Answers1