none
Automated Email based on Cell Value

    Question

  • I would like to send an automatic email based on the cell value, the body of the email would contain a named range is this possible?

    My spreadsheet is as follows

    A5 - contains the email address

    C1 - contains the job#

    C2 - contains the client name

    Named range "Milestone" refers to A14 and there is a checkbox in B14

    I would like an email sent to the email listed in A5 when the checkbox in cell A14 is checked

    The message would be

    Job# C1 - Client - C2 - Milestone Completed

    Tuesday, July 23, 2013 3:22 PM

Answers

  • Use this for the checkbook in B14's change event 
    (assuming it is name CheckBox1 and it is a checkbox from the controls toolbox)

    Private Sub CheckBox1_Change()
    If Me.CheckBox1.Value Then MileStone
    End Sub

    And in a regular codemodule, with a reference to Outlook set (I am assuming Outlook here, since you have Excel)

    Sub MileStone()
        Dim ol As Outlook.Application
        Dim myItem As Outlook.MailItem

        Set ol = CreateObject("outlook.application")
        Set myItem = ol.CreateItem(olMailItem)
        myItem.To = Range("A5").Value
        myItem.Subject = "Milestone completed"
        myItem.Body = "Job# " & Range("C1").Value & " - " & Range("C2").Value & " - " & Range("Milestone").Value
        myItem.Send

        ol.Quit

    End Sub

    Tuesday, July 23, 2013 6:35 PM

All replies

  • Use this for the checkbook in B14's change event 
    (assuming it is name CheckBox1 and it is a checkbox from the controls toolbox)

    Private Sub CheckBox1_Change()
    If Me.CheckBox1.Value Then MileStone
    End Sub

    And in a regular codemodule, with a reference to Outlook set (I am assuming Outlook here, since you have Excel)

    Sub MileStone()
        Dim ol As Outlook.Application
        Dim myItem As Outlook.MailItem

        Set ol = CreateObject("outlook.application")
        Set myItem = ol.CreateItem(olMailItem)
        myItem.To = Range("A5").Value
        myItem.Subject = "Milestone completed"
        myItem.Body = "Job# " & Range("C1").Value & " - " & Range("C2").Value & " - " & Range("Milestone").Value
        myItem.Send

        ol.Quit

    End Sub

    Tuesday, July 23, 2013 6:35 PM
  • Will this still work if I use the Form Control Checkbox instead of the Activex?
    Wednesday, July 24, 2013 8:12 PM
  • You need to link your checkbox to a cell - let's say B12 - and then link another cell to that cell using a formula. So, in A12, type

    =B12

    and then in the worksheet's codemodule (right-click the sheet tab and select "View Code" if you don't know how to get there), use the event code

    Private Sub Worksheet_Calculate()
    If Range("A12").Value Then MileStone
    End Sub

    Wednesday, July 24, 2013 8:23 PM