Hello All;

I have been trying to change this VBA to suit my needs. I acquired it from
he

http://www.rondebruin.nl/mail/change.htm

This code will gladly send an email when I run the macro. I am having
trouble getting it to send an email when the value of the cell changes. I
have it set up as follows:
Module 1:

Sub Mail_with_outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim strto As String, strcc As String, strbcc As String
Dim strsub As String, strbody As String

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

strto = "
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
.Send
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

This works great when I run the macro Mail_with_outlook. My sheet is named
"Time Line Chart". I have this code in the sheet tab:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("b33"), rng) Is Nothing Then
If Range("b33").Value < Range("c33").Value Then Mail_with_outlook
If Range("b33").Value Range("c33").Value Is Nothing Then
End If
End If
EndMacro:
End Sub

I changed Ron's -

If Range("A1").Value 200 Then YourMacroName

to -

If Range("b33").Value < Range("c33").Value Then Mail_with_outlook

It will not calculate whether b33 is smaller than c33. I had originally
tried the following:

If Range("d33").Value = "Yes" Then Mail_with_outlook

where "Yes" is the result of a formula. This resulted in an email being sent
every time that I ran the macro. I can not seem to get the macro to run on
it's own. What am I doing wrong. I am such an idiot. This is a nice feature,
but I can't get it to work. Help.

Thank You

Frank Pytel