If cell contains specific word then send email

  • Hello and Welcome to the Forum :)


    There are two aspects in your question :


    1. Send an e-mail


    2. Trigger the action (of sending e-mail) upon the Event of a specific selection



    Which one of these two aspects do you need to concentrate on ... ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    Edited once, last by Carim ().

  • rory

    Changed the title of the thread from “HELP!! (if cell contains specific word then send email)” to “If cell contains specific word then send email”.
  • I modified the sample code slightly and got the email to send depending on what was selected (conditional was satisfied). I'd now like to have the email display whatever is in a cell adjacent to the dropdown menu selection.


    Say I select "Needs Debug" in A3 (which will send an email) and B3 has some arbitrary number "10255'. How do I get the email to display "10255" in the subject?



    In worksheet:

    Option Explicit


    Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("J:J")) Is Nothing Then Exit Sub

    If Target <> "TBD" Then Exit Sub

    Call Mail_with_outlook1

    End Sub


    In Macro:

    Option Explicit


    Public FormulaCell As Range


    Sub Mail_with_outlook1()

    Dim OutApp As Object

    Dim OutMail As Object

    Dim strto As String, strcc As String, strbcc As String

    Dim strsub As String, strbody As String


    If ActiveWorkbook.Path <> "" Then

    Set OutApp = CreateObject("Outlook.Application")

    Set OutMail = OutApp.CreateItem(0)


    strto = "[email protected]"

    strcc = ""

    strbcc = ""

    strsub = "Radio S/N# has failed Ship Check"

    strbody = "Hi all,<br><br>" & _

    "Radio has failed System Check<br>" & _

    "Please see: " & _

    "<A HREF=""file://" & ActiveWorkbook.FullName & _

    """>Test Failure Raw Data</A> " & _

    "for additional details" & _

    "<br><br>Regards," & _

    "<br><br>System Check Technician</font>"



    On Error Resume Next

    With OutMail

    .To = strto

    .CC = strcc

    .BCC = strbcc

    .Subject = strsub

    .HTMLBody = strbody

    'You can add a file to the mail like this

    '.Attachments.Add ("C:\test.txt")

    .Display ' or use .Display

    End With

    On Error GoTo 0


    Set OutMail = Nothing

    Set OutApp = Nothing

    Else

    MsgBox "The ActiveWorkbook does not have a path, Save the file first."

    End If

    End Sub