VBA code for email notification

[fwcon]

Can anyone give me the VBA code for sending out an email whenever a cell value is equal to 1. I managed to find some code that will send the email every time the code is run, but the script can't monitor the cell value, i.e. I have to manually run the macro to get it to fire.

[spreadbetting]

If you're happy to use your current email routine why not simply run it via a VBA IF statement i.e.

Code: Select all

If Worksheets("Sheet1").Range("A1").Value = 1 Then Call Whatever_Your_Email_Routine_Is_Called

How you get that to run depends on how your Cell changes to 1, is it entered via VBA or Betangel or entered via some excel formula, plus you need to look at ensuring it only fires once so again depends how that 1 gets into the Cell in the first place and if you want it to reset to send further emails.

But you'd be looking at the worksheet event change, so if the cell value changes because it's entered manually or data is sent to the sheet from BA

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Static cellValue As Variant
    If Worksheets("Sheet1").Range("A1").Value = cellValue Then Exit Sub
    Application.EnableEvents = False
    
    cellValue = Worksheets("Sheet1").Range("A1").Value
    If cellValue = 1 Then Call Whatever_Your_Email_Routine_Is_Called
    
    Application.EnableEvents = True
End Sub

If it changes because of a formula

Code: Select all

Private Sub Worksheet_Calculate()
Static cellValue As Variant
    If Worksheets("Sheet1").Range("A1").Value = cellValue Then Exit Sub
    Application.EnableEvents = False
    
    cellValue = Worksheets("Sheet1").Range("A1").Value
    If cellValue = 1 Then Call Whatever_Your_Email_Routine_Is_Called
    
    Application.EnableEvents = True
End Sub

[fwcon]

cheers, I will give that a go.