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.

 

[mrmmickle1]

Hey Welcome to the MessageBoard!

Maybe try something simple like this:

[COLOR=#0000ff]Sub [/COLOR]SendMail()
[COLOR=#0000ff]    If[/COLOR] Range("A1") = "Waiting for Admin" [COLOR=#0000ff]Then[/COLOR]
         [COLOR=#0000ff]   With[/COLOR] CreateObject("Outlook.Application").createitem(0) [COLOR=#008000]'0 will create a new email item[/COLOR]
                .To = "MyEmail@email.com"
                .Subject = "My Subject Line Here"
                .Body = "Dear John," & vbNewLine & vbNewLine & "This is my email body"
                .Display [COLOR=#008000]'Change this to .Send[/COLOR]
[COLOR=#0000ff]            End With[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

 

[Liam_S]

Thanks for your reply.
Only seems to work when I hit run and not automatically when changing the cell.

thanks for your help

 

[mrmmickle1]

Liam_S,

Put this event change code in the Worksheet Module that your data exists on (i.e. Sheet1 Code Module...etc.) :

[COLOR=#0000ff]Private Sub [/COLOR]Worksheet_Change([COLOR=#0000ff]ByVal[/COLOR] Target[COLOR=#0000ff] As[/COLOR] Range)


[COLOR=#008000]    'If value in AH is edited and it the value = "Waiting for Admin" then send the email....[/COLOR]
[COLOR=#0000ff]    If [/COLOR]Target.Column = 34 [COLOR=#0000ff]And[/COLOR] Target.Value = "Waiting for Admin" [COLOR=#0000ff]Then[/COLOR]
        [COLOR=#0000ff]    With[/COLOR] CreateObject("Outlook.Application").createitem(0)[COLOR=#008000] '0 will create a new email item[/COLOR]
                .To = "MyEmail@email.com"
                .Subject = "My Subject Line Here"
                .Body = "Dear John," & vbNewLine & vbNewLine & "This is my email body"
                .Display [COLOR=#008000]'Change this to .Send[/COLOR]
[COLOR=#0000ff]            End With[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

 

[Liam_S]

Thank You! is there a way to add in multiple different emails based off different cell values? all taking from the same column?

 

[mrmmickle1]

Yes. What column do the emails reside in?

Maybe this example will help....

Private Sub Worksheet_Change(ByVal Target As Range)

    'If value in AH is edited and it the value = "Waiting for Admin" then send the email....
    If Target.Column = 34 And Target.Value = "Waiting for Admin" Then
            With CreateObject("Outlook.Application").createitem(0) '0 will create a new email item
[COLOR=#ff0000]                .To = Target.Offset(, -1) 'This will give you Column AG, -2 will give you column AF....etc[/COLOR]
                .Subject = "My Subject Line Here"
                .Body = "Dear John," & vbNewLine & vbNewLine & "This is my email body"
[COLOR=#000000]                .Display 'Change this to .Send[/COLOR]
[COLOR=#000000]            End With[/COLOR]
[COLOR=#000000]    End If[/COLOR]
[COLOR=#000000]
[/COLOR]
[COLOR=#000000]End Sub[/COLOR]

 

[Liam_S]

Sorry I didn't word that very well. What I meant was. In column AH i have multiple different selections e.g. "waiting for admin", "admin complete", "Ongoing" etc. each selection represents a new email. At this stage the email doesn't need to change just the recipient.

 

[mrmmickle1]

How about something like this?

[COLOR=#0000ff]Private Sub[/COLOR] Worksheet_Change([COLOR=#0000ff]ByVal [/COLOR]Target[COLOR=#0000ff] As [/COLOR]Range)


[COLOR=#0000ff]    Dim [/COLOR]myToAdd [COLOR=#0000ff]As String[/COLOR]


   [COLOR=#0000ff] If [/COLOR]Target.Column = 34 [COLOR=#0000ff]Then[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]        If [/COLOR]Target.Value = "Waiting for Admin"[COLOR=#0000ff] Then[/COLOR]
            myToAdd = "matt@email.com:"
    [COLOR=#0000ff]    ElseIf[/COLOR] Target.Value = "Admin Complete"[COLOR=#0000ff] Then[/COLOR]
            myToAdd = "matt2@email.com:"
 [COLOR=#0000ff]       ElseIf [/COLOR]Target.Value = "Admin Complete" [COLOR=#0000ff]Then[/COLOR]
            myToAdd = "matt3@email.com:"
[COLOR=#0000ff]        Else[/COLOR]
[COLOR=#008000]            'etc.....[/COLOR]
[COLOR=#0000ff]        End If[/COLOR]
            
        [COLOR=#0000ff]With[/COLOR] CreateObject("Outlook.Application").createitem(0) [COLOR=#008000]'0 will create a new email item[/COLOR]
            .To = myToAdd 
            .Subject = "My Subject Line Here"
            .Body = "Dear John," & vbNewLine & vbNewLine & "This is my email body"
            .Display [COLOR=#008000]'Change this to .Send[/COLOR]
[COLOR=#0000ff]        End With[/COLOR]
[COLOR=#0000ff]        [/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

 

[Liam_S]

Hey this worked perfectly!

Have been trialling the spreadsheet now with great success!

Is it possible to have multiple different change events e.g. if another cell has a certain value then it will also create an email to send, with a different message?

Thanks so much you have been a great help

 

[mrmmickle1]

You can only have one worksheet change event. If you need to check multiple cells you can do something like this:

[COLOR="#0000FF"]Private Sub[/COLOR] Worksheet_Change([COLOR="#0000FF"][/COLOR][COLOR="#0000FF"]ByVal[/COLOR] Target [COLOR="#0000FF"]As[/COLOR] Range)
    
[COLOR="#008000"]        'Check to see what cell has been changed....[/COLOR]
   [COLOR="#0000FF"] If [/COLOR]Target.Address = "$A$1" [COLOR="#0000FF"]Then[/COLOR]
        MsgBox "You just changed cell: " & Target.Address
       [COLOR="#008000"] 'Your code would go in this space in place of the msgbox....[/COLOR]   
    [COLOR="#0000FF"]ElseIf[/COLOR] Target.Address = "$F$1" [COLOR="#0000FF"]Then[/COLOR]
        MsgBox "You just changed cell: " & Target.Address
    [COLOR="#0000FF"]ElseIf[/COLOR] Target.Address = "$D$1" [COLOR="#0000FF"]Then[/COLOR]
        MsgBox "You just changed cell: " & Target.Address
  [COLOR="#0000FF"]  Else[/COLOR]
        [COLOR="#008000"]'Do Something Else Here......[/COLOR]
 [COLOR="#0000FF"]   End If[/COLOR]

[COLOR="#0000FF"]End Sub[/COLOR]