Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 65
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,123
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 65
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,123
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 65
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,123
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 65
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,123
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 65
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,123
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 65
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,123
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Send worksheet as HTML email RDE2 Excel Discussion (Misc queries) 1 June 2nd 10 08:27 PM
Macro to send worksheet via email Julie Excel Discussion (Misc queries) 2 January 22nd 09 07:41 AM
send excel worksheet using email Steve Bennett Excel Discussion (Misc queries) 0 October 5th 07 03:44 PM
Send an email to a list of emails in a worksheet cexarsiado New Users to Excel 2 August 22nd 06 03:04 PM
Send Worksheet in Email Nigel Excel Programming 1 July 22nd 05 07:26 PM


All times are GMT +1. The time now is 03:49 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"