Sending email based on cell values
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
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, 2022Comments
-
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 almost 6 yearsHey! 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 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 almost 6 yearsGot 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 almost 6 yearsThank you bro! Everything works as expected. Appreciate your help! :)
-
pRo over 1 yearIn case that the rng.value containts formula instead of simple value?