Sending An Email

  • Thread starter Thread starter Gee
  • Start date Start date
G

Gee

Can someone please tell me why this won't work?
I have a spreadsheet that feeds from an Access database.
It refreshes every minute.
When certain conditions are met, cell G12 changes from "NO" to "YES".
I don't understand why the following code won't work.
The cell CHANGES from NO to YES, but it doesn't trigger the email to be sent.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("G12").Value = "YES" Then
ActiveWorkbook.Send
Recipients = "(e-mail address removed)"
Subject = "NOC AGING" & Format(Date, "dd/mm/yy")
End If

End Sub

Thank you in advance for any help you can give me.
Gee
 
I figured it out!

Private Sub Worksheet_Calculate()
If Range("G12").Value = "NO" Then
Range("A2").Select
End If
If Range("G12").Value = "YES" Then
Range("A1").Select
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("G12").Value = "YES" Then
ThisWorkbook.SendMail Recipients:="(e-mail address removed)", Subject:="AGING"
End If
End Sub
 
Pay attention to the fact that the event will be triggered every time ANY
cell is changed.
this means that even if cell A1 will be changed, the macro will check for
the value in cell G12.

you may want to change the condition from "If Range("G12").Value..." to "If
Target.Address = "$G$12"..."

this will trigger the event only if the specific cell (G12) will change.
 
Pay attention to the fact that the event will be triggered every time ANY
cell is changed.
this means that even if cell A1 will be changed, the macro will check for
the value in cell G12.

you may want to change the condition from "If Range("G12").Value..." to "If
Target.Address = "$G$12"..."

this will trigger the event only if the specific cell (G12) will change.
 
Back
Top