If cell contains specific word then send email

  • Hello,

    I am trying to figure out how to send myself an email if a word (TBD) is entered through Data Validation.


    Have been looking for an answer for a while and i really only see on that works for numeric texts.


    Thanks

    Tabor

  • 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”.
  • Hello,


    Attached is your test file


    Hope this will help

    Files

    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:)

  • 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