send email based on cell value when value is text

[Liam_S]

Hi,

I am needing assistance in creating a macro to send an email to a certain person based on a cells value as being a text e.g. "Waiting for Admin". I currently use a drop down menu for cells in column AH. Based off the selection from the drop down I want it to send/display an email using outlook.

I have found how to do this based off a numeric value however I'm a novice to excel macros and can't change it to do it on a text value. I am needing creating the macro and adding into excel.

Any help is greatly appreciated.

 

[Liam_S]

I'm not sure what you mean. This is what I currently have.

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim myToAdd As String


    If Target.Column = 35 Then


        If Target.Value = "Waiting for Admin" Then
            myToAdd = "email@email.com.au;"
        ElseIf Target.Value = "Admin Complete" Then
            myToAdd = "email@email.com.au;"
        ElseIf Target.Value = "See Comments" Then
            myToAdd = "email@email.com.au;"
        ElseIf Target.Value = "Need Further Info" Then
            myToAdd = "email@email.com.au;"
        ElseIf Target.Value = "Aaron/Tammy to Complete" Then
            myToAdd = "email@email.com.au"
        ElseIf Target.Value = "Waiting to be contacted" Then
            myToAdd = "email@email.com.au;"
        ElseIf Target.Value = "Waiting for Shift Manager" Then
            myToAdd = "email@email.com.au;"
        ElseIf Target.Value = "Complete" Then
            End
        ElseIf Target.Value = "Ongoing" Then
            End
              ElseIf Target.Value = "" Then
            End
        End If
            
        With CreateObject("Outlook.Application").createitem(0) '0 will create a new email item
            .To = myToAdd
            .Subject = "Gaming Query/Issue Needs Actioning"
            .htmlBody = "[B]Attn: Users[/B]
 The Gaming Query/Issue Log has been updated.
 [B]Reference #(insert number)[/B] needs actioning. Please view the Gaming Query/Issue Log and action as needed.
 [URL="file:///\\M:\File"]Click Here To Open Query/Issue Log[/URL] 
  Once actioned please ensure that that the Query Status is changed appropriately.
 [B][I]Thank you,[/I][/B]

 Auto Email Message from The Gaming Query/Issue Log
"
            .Display 
        End With

    End If


End Sub

I was wanting it if the cell says "complete", and possibly other text values later on, then to display another email message.

As you can tell I am quite the novice

 

[mrmmickle1]

I believe you need to add the email body into the If statements:

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim myToAdd As String
 [COLOR=#ff0000]   Dim myEmailBody As String[/COLOR]


    If Target.Column = 35 Then


        If Target.Value = "Waiting for Admin" Then
            myToAdd = "email@email.com.au;"
[COLOR=#ff0000]            myEmailBody = "My Email Body Goes Here"[/COLOR]
        ElseIf Target.Value = "Admin Complete" Then
            myToAdd = "email@email.com.au;"
[COLOR=#ff0000]            myEmailBody = "My Email Body Goes Here"[/COLOR]
        ElseIf Target.Value = "See Comments" Then
            myToAdd = "email@email.com.au;"
[COLOR=#ff0000]            myEmailBody = "etc..........."[/COLOR]
        ElseIf Target.Value = "Need Further Info" Then
            myToAdd = "email@email.com.au;"
        ElseIf Target.Value = "Aaron/Tammy to Complete" Then
            myToAdd = "email@email.com.au"
        ElseIf Target.Value = "Waiting to be contacted" Then
            myToAdd = "email@email.com.au;"
        ElseIf Target.Value = "Waiting for Shift Manager" Then
            myToAdd = "email@email.com.au;"
        ElseIf Target.Value = "Complete" Then
            End
        ElseIf Target.Value = "Ongoing" Then
            End
              ElseIf Target.Value = "" Then
            End
        End If
            
        With CreateObject("Outlook.Application").createitem(0) '0 will create a new email item
            .To = myToAdd
            .Subject = "Gaming Query/Issue Needs Actioning"
[COLOR=#ff0000]            .htmlBody = myEmailBody[/COLOR]


            .Display 
        End With


    End If


End Sub