G
Guest
Hello All;
I have been trying to change this VBA to suit my needs. I acquired it from
here:
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 = "(e-mail address removed)"
strcc = "(e-mail address removed)"
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
I have been trying to change this VBA to suit my needs. I acquired it from
here:
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 = "(e-mail address removed)"
strcc = "(e-mail address removed)"
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