Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
send email when changes are made to a worksheet
I have a spreadsheet that shows sales committments as Firm or Pending. The
spreadsheet is used by several people who can change the status of a committment at any time. I have a count field that counts the number of Firm Committments, so when a committment changes from Pending to Firm, the counter obviously changes. I would like to send an standard email automatically to recipients each time the counter changes. I looked at Ron de Bruins example where the cell value is 200. the macro looks like it will work but I would need help in adapting the change event where the target cell contains a formula. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
send email when changes are made to a worksheet
The second change event is for a formula
http://www.rondebruin.nl/mail/change.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... I have a spreadsheet that shows sales committments as Firm or Pending. The spreadsheet is used by several people who can change the status of a committment at any time. I have a count field that counts the number of Firm Committments, so when a committment changes from Pending to Firm, the counter obviously changes. I would like to send an standard email automatically to recipients each time the counter changes. I looked at Ron de Bruins example where the cell value is 200. the macro looks like it will work but I would need help in adapting the change event where the target cell contains a formula. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
send email when changes are made to a worksheet
Hi Ron--thanks so much for responding. Yes, I can see the 2nd event is for a
formula, but I'm not sure what to replace the line If Range("a1".Value200 Then YourMacroName with. I want it the email sent every time someone changes a status to firm, but only once since once its firm its done. I thought I'd use a counter cell as my change event, but maybe I don't need it. The formula in this cell is =COUNTIF(G3:G13,"FIRM"). I want an email sent only once each time a row's status is changed to firm. The email will simply say "please check sales sheet for recent status changes". Am I making this too complicated? "Ron de Bruin" wrote: The second change event is for a formula http://www.rondebruin.nl/mail/change.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... I have a spreadsheet that shows sales committments as Firm or Pending. The spreadsheet is used by several people who can change the status of a committment at any time. I have a count field that counts the number of Firm Committments, so when a committment changes from Pending to Firm, the counter obviously changes. I would like to send an standard email automatically to recipients each time the counter changes. I looked at Ron de Bruins example where the cell value is 200. the macro looks like it will work but I would need help in adapting the change event where the target cell contains a formula. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
send email when changes are made to a worksheet
Hi Pam
G3:G13 Do they manual change the cells in this range ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... Hi Ron--thanks so much for responding. Yes, I can see the 2nd event is for a formula, but I'm not sure what to replace the line If Range("a1".Value200 Then YourMacroName with. I want it the email sent every time someone changes a status to firm, but only once since once its firm its done. I thought I'd use a counter cell as my change event, but maybe I don't need it. The formula in this cell is =COUNTIF(G3:G13,"FIRM"). I want an email sent only once each time a row's status is changed to firm. The email will simply say "please check sales sheet for recent status changes". Am I making this too complicated? "Ron de Bruin" wrote: The second change event is for a formula http://www.rondebruin.nl/mail/change.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... I have a spreadsheet that shows sales committments as Firm or Pending. The spreadsheet is used by several people who can change the status of a committment at any time. I have a count field that counts the number of Firm Committments, so when a committment changes from Pending to Firm, the counter obviously changes. I would like to send an standard email automatically to recipients each time the counter changes. I looked at Ron de Bruins example where the cell value is 200. the macro looks like it will work but I would need help in adapting the change event where the target cell contains a formula. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
send email when changes are made to a worksheet
yes, they do. So I should then be able to use the manual example and skip
the counter cell, correct? With this, how does vb know to execute the send the email only once? In other words, once a row is changed to FIRM it will never need the email sent again. "Ron de Bruin" wrote: Hi Pam G3:G13 Do they manual change the cells in this range ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... Hi Ron--thanks so much for responding. Yes, I can see the 2nd event is for a formula, but I'm not sure what to replace the line If Range("a1".Value200 Then YourMacroName with. I want it the email sent every time someone changes a status to firm, but only once since once its firm its done. I thought I'd use a counter cell as my change event, but maybe I don't need it. The formula in this cell is =COUNTIF(G3:G13,"FIRM"). I want an email sent only once each time a row's status is changed to firm. The email will simply say "please check sales sheet for recent status changes". Am I making this too complicated? "Ron de Bruin" wrote: The second change event is for a formula http://www.rondebruin.nl/mail/change.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... I have a spreadsheet that shows sales committments as Firm or Pending. The spreadsheet is used by several people who can change the status of a committment at any time. I have a count field that counts the number of Firm Committments, so when a committment changes from Pending to Firm, the counter obviously changes. I would like to send an standard email automatically to recipients each time the counter changes. I looked at Ron de Bruins example where the cell value is 200. the macro looks like it will work but I would need help in adapting the change event where the target cell contains a formula. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
send email when changes are made to a worksheet
Hi Pam
Use this then, no need for the formula cell. Be sure that the macro name = YourMacroName Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("G3:G13"), Target) Is Nothing Then If LCase(Target.Value) = "firm" Then Call YourMacroName End If End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... yes, they do. So I should then be able to use the manual example and skip the counter cell, correct? With this, how does vb know to execute the send the email only once? In other words, once a row is changed to FIRM it will never need the email sent again. "Ron de Bruin" wrote: Hi Pam G3:G13 Do they manual change the cells in this range ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... Hi Ron--thanks so much for responding. Yes, I can see the 2nd event is for a formula, but I'm not sure what to replace the line If Range("a1".Value200 Then YourMacroName with. I want it the email sent every time someone changes a status to firm, but only once since once its firm its done. I thought I'd use a counter cell as my change event, but maybe I don't need it. The formula in this cell is =COUNTIF(G3:G13,"FIRM"). I want an email sent only once each time a row's status is changed to firm. The email will simply say "please check sales sheet for recent status changes". Am I making this too complicated? "Ron de Bruin" wrote: The second change event is for a formula http://www.rondebruin.nl/mail/change.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... I have a spreadsheet that shows sales committments as Firm or Pending. The spreadsheet is used by several people who can change the status of a committment at any time. I have a count field that counts the number of Firm Committments, so when a committment changes from Pending to Firm, the counter obviously changes. I would like to send an standard email automatically to recipients each time the counter changes. I looked at Ron de Bruins example where the cell value is 200. the macro looks like it will work but I would need help in adapting the change event where the target cell contains a formula. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
send email when changes are made to a worksheet
Ron--works beautifully! Just a two quick questions... 1) How do I string multiple email addresses 2) Can I reference the row that caused the change in the email? If not, no big deal. Just thought it would be nice. "Ron de Bruin" wrote: Hi Pam Use this then, no need for the formula cell. Be sure that the macro name = YourMacroName Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("G3:G13"), Target) Is Nothing Then If LCase(Target.Value) = "firm" Then Call YourMacroName End If End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... yes, they do. So I should then be able to use the manual example and skip the counter cell, correct? With this, how does vb know to execute the send the email only once? In other words, once a row is changed to FIRM it will never need the email sent again. "Ron de Bruin" wrote: Hi Pam G3:G13 Do they manual change the cells in this range ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... Hi Ron--thanks so much for responding. Yes, I can see the 2nd event is for a formula, but I'm not sure what to replace the line If Range("a1".Value200 Then YourMacroName with. I want it the email sent every time someone changes a status to firm, but only once since once its firm its done. I thought I'd use a counter cell as my change event, but maybe I don't need it. The formula in this cell is =COUNTIF(G3:G13,"FIRM"). I want an email sent only once each time a row's status is changed to firm. The email will simply say "please check sales sheet for recent status changes". Am I making this too complicated? "Ron de Bruin" wrote: The second change event is for a formula http://www.rondebruin.nl/mail/change.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... I have a spreadsheet that shows sales committments as Firm or Pending. The spreadsheet is used by several people who can change the status of a committment at any time. I have a count field that counts the number of Firm Committments, so when a committment changes from Pending to Firm, the counter obviously changes. I would like to send an standard email automatically to recipients each time the counter changes. I looked at Ron de Bruins example where the cell value is 200. the macro looks like it will work but I would need help in adapting the change event where the target cell contains a formula. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
send email when changes are made to a worksheet
Do you use the Outlook example ?
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... Ron--works beautifully! Just a two quick questions... 1) How do I string multiple email addresses 2) Can I reference the row that caused the change in the email? If not, no big deal. Just thought it would be nice. "Ron de Bruin" wrote: Hi Pam Use this then, no need for the formula cell. Be sure that the macro name = YourMacroName Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("G3:G13"), Target) Is Nothing Then If LCase(Target.Value) = "firm" Then Call YourMacroName End If End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... yes, they do. So I should then be able to use the manual example and skip the counter cell, correct? With this, how does vb know to execute the send the email only once? In other words, once a row is changed to FIRM it will never need the email sent again. "Ron de Bruin" wrote: Hi Pam G3:G13 Do they manual change the cells in this range ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... Hi Ron--thanks so much for responding. Yes, I can see the 2nd event is for a formula, but I'm not sure what to replace the line If Range("a1".Value200 Then YourMacroName with. I want it the email sent every time someone changes a status to firm, but only once since once its firm its done. I thought I'd use a counter cell as my change event, but maybe I don't need it. The formula in this cell is =COUNTIF(G3:G13,"FIRM"). I want an email sent only once each time a row's status is changed to firm. The email will simply say "please check sales sheet for recent status changes". Am I making this too complicated? "Ron de Bruin" wrote: The second change event is for a formula http://www.rondebruin.nl/mail/change.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... I have a spreadsheet that shows sales committments as Firm or Pending. The spreadsheet is used by several people who can change the status of a committment at any time. I have a count field that counts the number of Firm Committments, so when a committment changes from Pending to Firm, the counter obviously changes. I would like to send an standard email automatically to recipients each time the counter changes. I looked at Ron de Bruins example where the cell value is 200. the macro looks like it will work but I would need help in adapting the change event where the target cell contains a formula. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
send email when changes are made to a worksheet
Yes,
"Ron de Bruin" wrote: Do you use the Outlook example ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... Ron--works beautifully! Just a two quick questions... 1) How do I string multiple email addresses 2) Can I reference the row that caused the change in the email? If not, no big deal. Just thought it would be nice. "Ron de Bruin" wrote: Hi Pam Use this then, no need for the formula cell. Be sure that the macro name = YourMacroName Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("G3:G13"), Target) Is Nothing Then If LCase(Target.Value) = "firm" Then Call YourMacroName End If End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... yes, they do. So I should then be able to use the manual example and skip the counter cell, correct? With this, how does vb know to execute the send the email only once? In other words, once a row is changed to FIRM it will never need the email sent again. "Ron de Bruin" wrote: Hi Pam G3:G13 Do they manual change the cells in this range ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... Hi Ron--thanks so much for responding. Yes, I can see the 2nd event is for a formula, but I'm not sure what to replace the line If Range("a1".Value200 Then YourMacroName with. I want it the email sent every time someone changes a status to firm, but only once since once its firm its done. I thought I'd use a counter cell as my change event, but maybe I don't need it. The formula in this cell is =COUNTIF(G3:G13,"FIRM"). I want an email sent only once each time a row's status is changed to firm. The email will simply say "please check sales sheet for recent status changes". Am I making this too complicated? "Ron de Bruin" wrote: The second change event is for a formula http://www.rondebruin.nl/mail/change.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... I have a spreadsheet that shows sales committments as Firm or Pending. The spreadsheet is used by several people who can change the status of a committment at any time. I have a count field that counts the number of Firm Committments, so when a committment changes from Pending to Firm, the counter obviously changes. I would like to send an standard email automatically to recipients each time the counter changes. I looked at Ron de Bruins example where the cell value is 200. the macro looks like it will work but I would need help in adapting the change event where the target cell contains a formula. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
send email when changes are made to a worksheet
Try this Pam
It wil use the value in H as name rng.Offset(0, 1).Value and tell you which cell is changed You can use any cell in the row in your text this way Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("G3:G13"), Target) Is Nothing Then If LCase(Target.Value) = "firm" Then Call Mail_with_outlook(Target) End If End If End Sub Copy the macro in a normal module It show the mail for testing, change display to send if you want to send it directly Sub Mail_with_outlook(rng As Range) 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 = " strcc = "" strbcc = "" strsub = "please check sales sheet for recent status changes" strbody = "Hi " & rng.Offset(0, 1).Value & vbNewLine & vbNewLine & _ rng.Address & " is changed" With OutMail .To = strto .CC = strcc .BCC = strbcc .Subject = strsub .Body = strbody .Display 'Or .Send End With Set OutMail = Nothing Set OutApp = Nothing End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... Yes, "Ron de Bruin" wrote: Do you use the Outlook example ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... Ron--works beautifully! Just a two quick questions... 1) How do I string multiple email addresses 2) Can I reference the row that caused the change in the email? If not, no big deal. Just thought it would be nice. "Ron de Bruin" wrote: Hi Pam Use this then, no need for the formula cell. Be sure that the macro name = YourMacroName Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("G3:G13"), Target) Is Nothing Then If LCase(Target.Value) = "firm" Then Call YourMacroName End If End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... yes, they do. So I should then be able to use the manual example and skip the counter cell, correct? With this, how does vb know to execute the send the email only once? In other words, once a row is changed to FIRM it will never need the email sent again. "Ron de Bruin" wrote: Hi Pam G3:G13 Do they manual change the cells in this range ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... Hi Ron--thanks so much for responding. Yes, I can see the 2nd event is for a formula, but I'm not sure what to replace the line If Range("a1".Value200 Then YourMacroName with. I want it the email sent every time someone changes a status to firm, but only once since once its firm its done. I thought I'd use a counter cell as my change event, but maybe I don't need it. The formula in this cell is =COUNTIF(G3:G13,"FIRM"). I want an email sent only once each time a row's status is changed to firm. The email will simply say "please check sales sheet for recent status changes". Am I making this too complicated? "Ron de Bruin" wrote: The second change event is for a formula http://www.rondebruin.nl/mail/change.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... I have a spreadsheet that shows sales committments as Firm or Pending. The spreadsheet is used by several people who can change the status of a committment at any time. I have a count field that counts the number of Firm Committments, so when a committment changes from Pending to Firm, the counter obviously changes. I would like to send an standard email automatically to recipients each time the counter changes. I looked at Ron de Bruins example where the cell value is 200. the macro looks like it will work but I would need help in adapting the change event where the target cell contains a formula. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
send email when changes are made to a worksheet
Ron, this is so slick. I was able to adapt this with the offset to include
the project name and number as well. You're the best. Thank you so much for your help. "Ron de Bruin" wrote: Try this Pam It wil use the value in H as name rng.Offset(0, 1).Value and tell you which cell is changed You can use any cell in the row in your text this way Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("G3:G13"), Target) Is Nothing Then If LCase(Target.Value) = "firm" Then Call Mail_with_outlook(Target) End If End If End Sub Copy the macro in a normal module It show the mail for testing, change display to send if you want to send it directly Sub Mail_with_outlook(rng As Range) 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 = " strcc = "" strbcc = "" strsub = "please check sales sheet for recent status changes" strbody = "Hi " & rng.Offset(0, 1).Value & vbNewLine & vbNewLine & _ rng.Address & " is changed" With OutMail .To = strto .CC = strcc .BCC = strbcc .Subject = strsub .Body = strbody .Display 'Or .Send End With Set OutMail = Nothing Set OutApp = Nothing End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... Yes, "Ron de Bruin" wrote: Do you use the Outlook example ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... Ron--works beautifully! Just a two quick questions... 1) How do I string multiple email addresses 2) Can I reference the row that caused the change in the email? If not, no big deal. Just thought it would be nice. "Ron de Bruin" wrote: Hi Pam Use this then, no need for the formula cell. Be sure that the macro name = YourMacroName Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("G3:G13"), Target) Is Nothing Then If LCase(Target.Value) = "firm" Then Call YourMacroName End If End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... yes, they do. So I should then be able to use the manual example and skip the counter cell, correct? With this, how does vb know to execute the send the email only once? In other words, once a row is changed to FIRM it will never need the email sent again. "Ron de Bruin" wrote: Hi Pam G3:G13 Do they manual change the cells in this range ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... Hi Ron--thanks so much for responding. Yes, I can see the 2nd event is for a formula, but I'm not sure what to replace the line If Range("a1".Value200 Then YourMacroName with. I want it the email sent every time someone changes a status to firm, but only once since once its firm its done. I thought I'd use a counter cell as my change event, but maybe I don't need it. The formula in this cell is =COUNTIF(G3:G13,"FIRM"). I want an email sent only once each time a row's status is changed to firm. The email will simply say "please check sales sheet for recent status changes". Am I making this too complicated? "Ron de Bruin" wrote: The second change event is for a formula http://www.rondebruin.nl/mail/change.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... I have a spreadsheet that shows sales committments as Firm or Pending. The spreadsheet is used by several people who can change the status of a committment at any time. I have a count field that counts the number of Firm Committments, so when a committment changes from Pending to Firm, the counter obviously changes. I would like to send an standard email automatically to recipients each time the counter changes. I looked at Ron de Bruins example where the cell value is 200. the macro looks like it will work but I would need help in adapting the change event where the target cell contains a formula. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
send email when changes are made to a worksheet
You are welcome
Have a nice day -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... Ron, this is so slick. I was able to adapt this with the offset to include the project name and number as well. You're the best. Thank you so much for your help. "Ron de Bruin" wrote: Try this Pam It wil use the value in H as name rng.Offset(0, 1).Value and tell you which cell is changed You can use any cell in the row in your text this way Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("G3:G13"), Target) Is Nothing Then If LCase(Target.Value) = "firm" Then Call Mail_with_outlook(Target) End If End If End Sub Copy the macro in a normal module It show the mail for testing, change display to send if you want to send it directly Sub Mail_with_outlook(rng As Range) 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 = " strcc = "" strbcc = "" strsub = "please check sales sheet for recent status changes" strbody = "Hi " & rng.Offset(0, 1).Value & vbNewLine & vbNewLine & _ rng.Address & " is changed" With OutMail .To = strto .CC = strcc .BCC = strbcc .Subject = strsub .Body = strbody .Display 'Or .Send End With Set OutMail = Nothing Set OutApp = Nothing End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... Yes, "Ron de Bruin" wrote: Do you use the Outlook example ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... Ron--works beautifully! Just a two quick questions... 1) How do I string multiple email addresses 2) Can I reference the row that caused the change in the email? If not, no big deal. Just thought it would be nice. "Ron de Bruin" wrote: Hi Pam Use this then, no need for the formula cell. Be sure that the macro name = YourMacroName Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("G3:G13"), Target) Is Nothing Then If LCase(Target.Value) = "firm" Then Call YourMacroName End If End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... yes, they do. So I should then be able to use the manual example and skip the counter cell, correct? With this, how does vb know to execute the send the email only once? In other words, once a row is changed to FIRM it will never need the email sent again. "Ron de Bruin" wrote: Hi Pam G3:G13 Do they manual change the cells in this range ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... Hi Ron--thanks so much for responding. Yes, I can see the 2nd event is for a formula, but I'm not sure what to replace the line If Range("a1".Value200 Then YourMacroName with. I want it the email sent every time someone changes a status to firm, but only once since once its firm its done. I thought I'd use a counter cell as my change event, but maybe I don't need it. The formula in this cell is =COUNTIF(G3:G13,"FIRM"). I want an email sent only once each time a row's status is changed to firm. The email will simply say "please check sales sheet for recent status changes". Am I making this too complicated? "Ron de Bruin" wrote: The second change event is for a formula http://www.rondebruin.nl/mail/change.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... I have a spreadsheet that shows sales committments as Firm or Pending. The spreadsheet is used by several people who can change the status of a committment at any time. I have a count field that counts the number of Firm Committments, so when a committment changes from Pending to Firm, the counter obviously changes. I would like to send an standard email automatically to recipients each time the counter changes. I looked at Ron de Bruins example where the cell value is 200. the macro looks like it will work but I would need help in adapting the change event where the target cell contains a formula. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Send worksheet as HTML email | Excel Discussion (Misc queries) | |||
Macro to send worksheet via email | Excel Discussion (Misc queries) | |||
send excel worksheet using email | Excel Discussion (Misc queries) | |||
Send an email to a list of emails in a worksheet | New Users to Excel | |||
Send Worksheet in Email | Excel Programming |