auto email if cell value changed

[Guest]

I'm using a workbook to schedule staff. Each month is a worksheet then I
have some counts that are done. The days of the month are in the top row and
the names of the staff in the first column. We've set up autorefresh on web
pages so the schedule is always available but... people still don't notice
shift changes.

I want excel to generate an email to the person (we're running exchange too)
if a cell value is changed. I was thinking of a couple of steps.
1. So they only get emails about the current month and the month ahead --
Have a cell at the top with YES/NO in it -- if it's set to yes then any
changes will get emailed, if NO then no email is generated
2. There email address is in a column beside there name
3. If any cell in the same row as there name is change in a "YES" worksheet
then a stock email is sent using the address -- such as "You're schedule has
changed please check it".

Any ideas -- I found some stuff on how to trigger a macro if a cell value is
changed but nothing to limit it to a specific row.

Thx.

 

[Ron de Bruin]

Hi Ian

You can use a formula that count Yes in the row
=COUNTIF(A1:E1,"yes")

And use code like this to mail
http://www.rondebruin.nl/mail/change.htm

In this case if the value >0

 

[Guest]

Thanks Ron, I've been playing with this for a couple of days so I have a
marathon post -- sorry. here's the logic -- when a cell is changed it puts
the current date into a cell. A second cell calculate the difference between
the current date and the date the cell was last changed. My idea is that if
the schedule is changed (eg the cell is changed) within 30days of the current
date I want the program to send an email. so I have a worksheet
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A1:C3"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 3).ClearContents
Else
With .Offset(0, 3)
.NumberFormat = "dd mmm yy"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

So the range it monitors is A1:C3 and post the date of last change into
D1:F3. the difference between the current date and the date of change is
posted into G1:I3. I posted your VBA (below) using just G1 (couldn't figure
out how to monitor a range of cells).

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("g1"), rng) Is Nothing Then
If Range("g1").Value = 200 Then Macro1
End If
End If
EndMacro:
End Sub

When I post your script into the worksheet VBA two problems:
1. Ambiguous Name because Worksheet_Change is the same for both programs.
2. How do I monitor a range of cells rather than just 1 cell to trigger a
macro in the VBA above
3. I plan to use the Macro below but I need it to pick up an email address
from the row of the cell that changed (I was thinking about incorporating
vlookup somewhere) so that if row 5 has a number <30 in the monitored cell
range it will pick up the email address in column A of row 5 and put it into
where you had the email address in the VBA below strto = "CAN I MAKE THIS A
CELL?".

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")
Set OutMail = OutApp.CreateItem(0)
strto = "(e-mail address removed)"
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


That's it -- Thanks for any help. Ian.

 

[Ron de Bruin]

Hi Ian

I look at it this evening or tomorrow
must go now

 

[Ron de Bruin]

Hi

It is not possible to have two events with the same name so you must combine them

First at the top of the module where the mail macro is add this line

Public str As String

You can use .To = str in the mail macro now


And test this sheet event in the sheet module
Read the notes in the code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("A1:C3"), Target) Is Nothing Then

If IsEmpty(Target.Value) Then
Target.Offset(0, 3).ClearContents
Else
With Target.Offset(0, 3)
.NumberFormat = "dd mmm yy"
.Value = Date
End With
End If
If Target.Offset(0, 6).Value = "yes" Then
'if the formula cell in G, H or I is "yes" then save the value of the cell in "J" and run the macro
str = Cells(Target.Row, "J").Value

'You can use str now in the To line
Call macro1
End If
End If
End Sub

Post back if I not understand you correct