Email on value change (Formula)

[Frank Pytel]

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

 

[Ron de Bruin]

Hi Frank

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

Is C33 used in the formula in B33 ?

Or do you enter the values manual ?

 

[Frank Pytel]

Ron;

Thanks for getting on this so quickly. Please allow me to say, Beautiful
Work. You're a genius.

Yes and No. First, I got the cells wrong. They should have both been 34
instead of 33. I changed this and it had no effect.

Second. Cell B34 references a manually entered number that is set to a
percentage. It displays as a percentage using the simple formatting dialog
selection. I can readily change this to a formula by referencing this in a
dummy cell if it would help. It's a small application so it won't add much to
the file. Cell C34 references cell G30 (=G30). G30 is a formula as follows
(all cells in the G column are also formatted to percentages as above):

=(SUM(G10:G12,G14:G16,G18:G20,G22:G24,G26:G28))/15

This is a Gantt sheet/chart. The formula above gives me a mean percentage
completion.

Ron, would it be better to reference all of this data in another section as
decimal values and calculate as < 1 or = 1 when calling the macro. I noticed
you have:

Target.Cells.Count > 1

Is this how I should be referencing this instead of a text statement. It
would not be anything to add a qualifier that states if(##="Yes",1,"").

Thanks for your help Ron. I really appreciate it.

Frank Pytel

 

[Ron de Bruin]

Hi Frank

When do you want to send the mail ?

When the formula cell B34 change, am I correct ?
Or do you enter the value manual in B34

 

[Frank Pytel]

Hey Ron;

I would like to send the email when 2 values fall within a range. Cells c34
and a34. I already have a formula set up in the sheet as follows:

=IF((A36-1)>$S$9,"",IF(AND((A36-1)<$S$9,(B34-0.005)<G30),"Yes",""))

The above formula is in cell D34. A36 references the date code for the date
in cell A34. $S$9 references =Today(). B34 references the Percentage of
completion required by the Date specified in A34(A36). The formula above
reads as follows (hopefully, to clarify my objectives):

=IF((The Deadline Date - One Day)>Today(),"",IF(AND((The Deadline Date - One
Day)<Today(),(The percentage of completion required - 1/2%)<The actual mean
percentage completion to date,The Deadline penalty should be applied as the
projects objectives have not been achieved according to the contract,"")

Cell B34 is manually entered into the spreadsheet as this will vary from job
to job. Cell C34 is calculated based on the actual mean percentage of
completion.

To answer your question specifically, I think it would be easier for me to
track if the qualifier to run the macro is the change in cell D34. This is
set to "Yes" if True and "" if False. I can set up a dummy cell in E34 that
defaults to 1 if cell d34 calculates to "Yes"(True). If this cell is blank I
would like the macro to calculate to not send an email (False).

Thank you so very much for your help with this Ron. I have really got to get
me a book on VBA. Any recommendations?

Thank You

Frank Pytel

 

[Ron de Bruin]

Hi Frank

To answer your question specifically, I think it would be easier for me to
track if the qualifier to run the macro is the change in cell D34. This is
set to "Yes" if True and "" if False. I can set up a dummy cell in E34 that
defaults to 1 if cell d34 calculates to "Yes"(True). If this cell is blank I
would like the macro to calculate to not send an email (False).
Correct


Thank you so very much for your help with this Ron. I have really got to get
me a book on VBA. Any recommendations?

http://www.contextures.com/xlbooks.html

Most things are the same in the 2003 or the 2007 version
http://www.amazon.com/gp/product/04...11189&creative=373489&creativeASIN=0470044012

 

[Frank Pytel]

Ron;

Works like a charm. You can see the results here;

http://excel-applications-and-sprea...QlGG1qiJ7UbTIup-M2XPURDSQf3tj5EYnbGHToTBrHENd

Change the top deadline date to be greater than today and the required
percentage to be lower than the actual.

Thanks and have a great day.

Frank Pytel