email alerts in excel

S

shooter

If a condition is met in a cell, (ie:cell value exceeds
200) is it possible to trigger an email alert to a
specified email address? Thanks in advance.
 
R

Ron de Bruin

Hi shooter

Which mail program do you use ?
Is the cell value a formula or do you change the cell manual ?
 
R

Ron de Bruin

Hi Shooter

Always diffecult with a formula.

Ok Try this with the formula in A1
Copy this event in the sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo EndMacro
If Target.Dependents.Address = Range("A1").Address Then
If Range("A1").Value > 200 Then Mail_with_outlook
End If
EndMacro:
End Sub


This in a module

Change display to Send if it is working correct for you

Sub Mail_with_outlook()
'Example for Outlook
Dim OutApp As Object
Dim OutMail As Object
Dim strto As String
Dim strcc As String
Dim strbcc As String
Dim strsub As String
Dim strbody As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strto = "[email protected]"
strcc = ""
strbcc = ""
strsub = "Important message"
strbody = "Hi there" & vbNewLine & vbNewLine & _
"Cell A1 is changed"

With OutMail
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = strsub
.Body = strbody
.display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
 
Top