G
George
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: Tools>References 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 = "(e-mail address removed)"
'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
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: Tools>References 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 = "(e-mail address removed)"
'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