Ladies and Gentlemen:

In 2003, Ron De Bruin wrote a VBA code LET EXCEL AUTOMATICALLY E-MAIL
A SPECIFIC E-MAIL ADDRESS EACH TIME A PARTICULAR CELL EQUALS "Y" OR
"True". His did it through a change event in cell "A1" as an example.
However, my situation is a little bit different from his. Here is my
situation: I need to enters number in two different columns, say
column A and column B. The PERCENTAGE different betwenn column A and
column B IN THE SAME ROW (say Cn=(Bn-An)/An where n is the index of
the row number) will be calculated automatically in the column C. What
I am trying to figure out is whenever Cn is greater than 20%, I would
like a message sent to my outlook mailbox. Any ideas?

Thanks,




The following is the code that Ron De Bruin wrote for your reference:

The change event will run the code when you change the cell
It must be placed in a sheet module


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
If Target.Value = "Y" Or Target.Value = "True" Then
Mail_with_outlook
End If
End If
End Sub


This sub in a normal module


Change Display to Send if you like it
Don't forget to set a reference to Outlook


How do you that:
1: Go to the VBA editor, Alt -F11
2: ToolsReferences in the Menu bar
3: Place a Checkmark before Microsoft Office Outlook ? Object Library
? is the Excel version number


Sub Mail_with_outlook()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
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(olMailItem)


strto = "
'Or this with the address in a cell
'strto = Sheets("Sheet1").Range("a1").Value
strcc = ""
strbcc = ""
strsub = "Cell A1 is changed"
strbody = "something you want"


With OutMail
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = strsub
.Body = strbody
.Display
End With
End Sub