Sending email based on cell values

10,018

On refresh of query, the code should check each cell from F3 to F14 and see if it is equal to 1, if so, it will email user the cell location.

UPDATE:

'Need to be in the sheet code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Call notify
End Sub

Sub notify()
    Dim rng As Range
    For Each rng In Range("F3:F14")
       If (rng.Value = 1) Then
           Call mymacro(rng.Address)
       End If
    Next rng
End Sub

Private Sub mymacro(theValue As String)
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hi there" & vbNewLine & vbNewLine & _
              "The value that changed is in cell: " & theValue
    On Error Resume Next
    With xOutMail
        .To = "email address"
        .CC = ""
        .BCC = ""
        .Subject = "test succeeded"
        .Body = xMailBody
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub
Share:
10,018
ken_you_not
Author by

ken_you_not

I like technology and science. Despite not doing really well on them in school, I still remain interested in those fields. Currently a student studying Computer Science.

Updated on June 04, 2022

Comments

  • ken_you_not
    ken_you_not over 1 year

    Is there a more efficient way to send email reminders based on a cell value that changes frequently?

    'This is the main function
    
    Sub notify()          
     Dim rng As Range
     For Each rng In Range("F3:F14")
        If (rng.Value = 1) Then
            Call mymacro
        End If
     Next rng
      
    End Sub
    '-----------------------------------------------------------------------
    
      'This is the function that sends an email when called by the main function
    
      Private Sub mymacro()  
        Dim xOutApp As Object
        Dim xOutMail As Object
        Dim xMailBody As String
        Set xOutApp = CreateObject("Outlook.Application")
        Set xOutMail = xOutApp.CreateItem(0)
        xMailBody = "Hi there" & vbNewLine & vbNewLine & _
                  "This is line 1" & vbNewLine & _
                  "This is line 2"
        On Error Resume Next
        With xOutMail
            .To = "email address"
            .CC = ""
            .BCC = ""
            .Subject = "test succeeded"
            .Body = xMailBody
            .Display   'or use .Send
        End With
        On Error GoTo 0
        Set xOutMail = Nothing
        Set xOutApp = Nothing
    End Sub
    

    Both codes are in the same module of my worksheet. The code sends an email (through Outlook) to the user. For example, if F3 and F7 evaluate to true, two emails will be sent to the user.

    How can I, if the same situation occurs (F3 and F7 evaluate to true), the two email sent to the user would specify which cell evaluated to true. In other words, each email sent would be different in pointing out which specific cell evaluated to true.

    Also, would the code be able to rerun if the data inside the cell ("F3:F14") is updated?

  • ken_you_not
    ken_you_not almost 6 years
    Hey! looks good and thank you for the swift response. I did use your code, but couldn't run it as the macro selection table popped out and there is nothing to select, so I'm stucked there. Also, what's the point of adding "(Target.Address)" behind the "Call mymacro" code?
  • pokemon_Man
    pokemon_Man almost 6 years
    @ken_you_not you do not need to run it, put all the code in your sheet code such as "Sheet1"...., just enter a "1" in cell F3 and the code should run itself because it is actviated on the worksheet change event which means anything that changes in F3 to F14 and equal to 1 will trigger your code. Target.Address is the cell location that is changing so that if your values does change, it captures the cell change and output in your email code.
  • ken_you_not
    ken_you_not almost 6 years
    Got it haha. The code can be run now, but that's only if I manually change the target cells. The main problem is still there, the code would not run if the cells are updated via query refresh though. Any thoughts? Thanks a lot for helping me to clear my first hurdle though :)
  • ken_you_not
    ken_you_not almost 6 years
    Thank you bro! Everything works as expected. Appreciate your help! :)
  • pRo
    pRo over 1 year
    In case that the rng.value containts formula instead of simple value?