Sending Conditional EMails from Excel Which Copies Information from One Worksheet
 

Quick Background: Excel 2000, Outlook 2003. I have a worksheet with 7
small tables, each one represents a reconcilement for a different
office. If there is an out of balance condition it is reflected in one
cell (say E16) which contains a simple formula (for instance
"=e14-e15"). Any time the reconcilement doesn't balance I have
to email a copy of the table to the appropriate persons (2 in each
case). The process I have now requires me to open Outlook, key in the
email addresses, subject, body and cut and paste the corresponding
table from Excel into it. Example:

To: ;
Subject: Office Reconcilement
Body:

Hi,

Attached is a screenshot of your branch reconcilement which
reflects a difference of $420.00, please research and clear.

Thanks,

Phillip


What I would like is a button that when clicked does the same and pulls
the email information (addresses, subject, body) from designated cells
in the spreadsheet. The cells might look like this:

Cell M2:
Cell M3:
Cell M4: "Office Reconcilement"
Cell M5: "Hi, Attached is a screenshot of your branch reconcilement
reflecting a difference of"
Cell M6: =E16 (cell which shows amount of difference)
Cell M7: "Please research and clear. Thanks, Phillip

The last part of the routine would cut and paste the table into the
bottom of the body of the email.

I don't care about the warning message and if adding the total into
the text of the body complicates matters I can leave it out.

Thanks in advance!

Sending Conditional EMails from Excel Which Copies Information from One Worksheet
 

Hi

Try this
http://www.rondebruin.nl/mail/folder3/mail4.htm

You can use a cell address for the To lne and Subject line
Click on the Tip link on the page

--
Regards Ron de Bruin
http://www.rondebruin.nl



wrote in message oups.com...
Quick Background: Excel 2000, Outlook 2003. I have a worksheet with 7
small tables, each one represents a reconcilement for a different
office. If there is an out of balance condition it is reflected in one
cell (say E16) which contains a simple formula (for instance
"=e14-e15"). Any time the reconcilement doesn't balance I have
to email a copy of the table to the appropriate persons (2 in each
case). The process I have now requires me to open Outlook, key in the
email addresses, subject, body and cut and paste the corresponding
table from Excel into it. Example:

To: ;
Subject: Office Reconcilement
Body:

Hi,

Attached is a screenshot of your branch reconcilement which
reflects a difference of $420.00, please research and clear.

Thanks,

Phillip


What I would like is a button that when clicked does the same and pulls
the email information (addresses, subject, body) from designated cells
in the spreadsheet. The cells might look like this:

Cell M2:
Cell M3:
Cell M4: "Office Reconcilement"
Cell M5: "Hi, Attached is a screenshot of your branch reconcilement
reflecting a difference of"
Cell M6: =E16 (cell which shows amount of difference)
Cell M7: "Please research and clear. Thanks, Phillip

The last part of the routine would cut and paste the table into the
bottom of the body of the email.

I don't care about the warning message and if adding the total into
the text of the body complicates matters I can leave it out.

Thanks in advance!

Sending Conditional EMails from Excel Which Copies Information from One Worksheet
 

i use something similar, but use outlook express, so i'll just present this as
an idea.

in thisworkbook's code sheet code i have the following and a few other lines of
code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Worksheets("Reports").Range("G2").Value = "Approvals" Then
Call Mail_Text_in_Body
End If
End Sub

which checks to see if the sheet for approvals has been saved, you could check
for your value being negative. i don't think i would use the worksheet change
function because every time the value change it would fire. that's why i do mine
on save.

then i call a userform to enter a description, you may not need this if you have
a canned description.

anyway then this code runs:

arr = Worksheets("emp").Range("M2:M" & lastrow).Value
Recipient = arr(1, 1)
For i = lastrow - 1 To 2 Step -1
Recipientcc = arr(i, 1) & ";" & Recipientcc
Next
Recipientbcc = ""

Subj = "Vacation calendar has been updated by " &
Application.Proper(Environ("UserName"))
HLink = "mailto:" & Recipient & "?" & "cc=" & Recipientcc & "&" & "bcc=" &
Recipientbcc & "&"
HLink = HLink & "subject=" & Subj & "&"
HLink = HLink & "body=" & Application.Proper(msg2) & " " & msg
ActiveWorkbook.FollowHyperlink (HLink)

the range contains the email addresses and it populates the array and then the
outlook express recipient and cc's.

the subject shows who updated the calendar
and description entered into the userform is placed in the body

then i also have a form that allows editing/adding email addresses.

like i mentioned, just an idea

--


Gary


wrote in message
oups.com...
Quick Background: Excel 2000, Outlook 2003. I have a worksheet with 7
small tables, each one represents a reconcilement for a different
office. If there is an out of balance condition it is reflected in one
cell (say E16) which contains a simple formula (for instance
"=e14-e15"). Any time the reconcilement doesn't balance I have
to email a copy of the table to the appropriate persons (2 in each
case). The process I have now requires me to open Outlook, key in the
email addresses, subject, body and cut and paste the corresponding
table from Excel into it. Example:

To: ;
Subject: Office Reconcilement
Body:

Hi,

Attached is a screenshot of your branch reconcilement which
reflects a difference of $420.00, please research and clear.

Thanks,

Phillip


What I would like is a button that when clicked does the same and pulls
the email information (addresses, subject, body) from designated cells
in the spreadsheet. The cells might look like this:

Cell M2:
Cell M3:
Cell M4: "Office Reconcilement"
Cell M5: "Hi, Attached is a screenshot of your branch reconcilement
reflecting a difference of"
Cell M6: =E16 (cell which shows amount of difference)
Cell M7: "Please research and clear. Thanks, Phillip

The last part of the routine would cut and paste the table into the
bottom of the body of the email.

I don't care about the warning message and if adding the total into
the text of the body complicates matters I can leave it out.

Thanks in advance!

Sending Conditional EMails from Excel Which Copies Information from One Worksheet
 

For OE I have a few code examples on my site Gary
http://www.rondebruin.nl/mail/oebody.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Gary Keramidas" <GKeramidasATmsn.com wrote in message ...
i use something similar, but use outlook express, so i'll just present this as an idea.

in thisworkbook's code sheet code i have the following and a few other lines of code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Worksheets("Reports").Range("G2").Value = "Approvals" Then
Call Mail_Text_in_Body
End If
End Sub

which checks to see if the sheet for approvals has been saved, you could check for your value being negative. i don't think i
would use the worksheet change function because every time the value change it would fire. that's why i do mine on save.

then i call a userform to enter a description, you may not need this if you have a canned description.

anyway then this code runs:

arr = Worksheets("emp").Range("M2:M" & lastrow).Value
Recipient = arr(1, 1)
For i = lastrow - 1 To 2 Step -1
Recipientcc = arr(i, 1) & ";" & Recipientcc
Next
Recipientbcc = ""

Subj = "Vacation calendar has been updated by " & Application.Proper(Environ("UserName"))
HLink = "mailto:" & Recipient & "?" & "cc=" & Recipientcc & "&" & "bcc=" & Recipientbcc & "&"
HLink = HLink & "subject=" & Subj & "&"
HLink = HLink & "body=" & Application.Proper(msg2) & " " & msg
ActiveWorkbook.FollowHyperlink (HLink)

the range contains the email addresses and it populates the array and then the outlook express recipient and cc's.

the subject shows who updated the calendar
and description entered into the userform is placed in the body

then i also have a form that allows editing/adding email addresses.

like i mentioned, just an idea

--


Gary


wrote in message oups.com...
Quick Background: Excel 2000, Outlook 2003. I have a worksheet with 7
small tables, each one represents a reconcilement for a different
office. If there is an out of balance condition it is reflected in one
cell (say E16) which contains a simple formula (for instance
"=e14-e15"). Any time the reconcilement doesn't balance I have
to email a copy of the table to the appropriate persons (2 in each
case). The process I have now requires me to open Outlook, key in the
email addresses, subject, body and cut and paste the corresponding
table from Excel into it. Example:

To: ;
Subject: Office Reconcilement
Body:

Hi,

Attached is a screenshot of your branch reconcilement which
reflects a difference of $420.00, please research and clear.

Thanks,

Phillip


What I would like is a button that when clicked does the same and pulls
the email information (addresses, subject, body) from designated cells
in the spreadsheet. The cells might look like this:

Cell M2:
Cell M3:
Cell M4: "Office Reconcilement"
Cell M5: "Hi, Attached is a screenshot of your branch reconcilement
reflecting a difference of"
Cell M6: =E16 (cell which shows amount of difference)
Cell M7: "Please research and clear. Thanks, Phillip

The last part of the routine would cut and paste the table into the
bottom of the body of the email.

I don't care about the warning message and if adding the total into
the text of the body complicates matters I can leave it out.

Thanks in advance!

Sending Conditional EMails from Excel Which Copies Information from One Worksheet
 

that's where i got what i used, thanks

--


Gary


"Ron de Bruin" wrote in message
...
For OE I have a few code examples on my site Gary
http://www.rondebruin.nl/mail/oebody.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i use something similar, but use outlook express, so i'll just present this as
an idea.

in thisworkbook's code sheet code i have the following and a few other lines
of code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Worksheets("Reports").Range("G2").Value = "Approvals" Then
Call Mail_Text_in_Body
End If
End Sub

which checks to see if the sheet for approvals has been saved, you could
check for your value being negative. i don't think i would use the worksheet
change function because every time the value change it would fire. that's why
i do mine on save.

then i call a userform to enter a description, you may not need this if you
have a canned description.

anyway then this code runs:

arr = Worksheets("emp").Range("M2:M" & lastrow).Value
Recipient = arr(1, 1)
For i = lastrow - 1 To 2 Step -1
Recipientcc = arr(i, 1) & ";" & Recipientcc
Next
Recipientbcc = ""

Subj = "Vacation calendar has been updated by " &
Application.Proper(Environ("UserName"))
HLink = "mailto:" & Recipient & "?" & "cc=" & Recipientcc & "&" & "bcc=" &
Recipientbcc & "&"
HLink = HLink & "subject=" & Subj & "&"
HLink = HLink & "body=" & Application.Proper(msg2) & " " & msg
ActiveWorkbook.FollowHyperlink (HLink)

the range contains the email addresses and it populates the array and then
the outlook express recipient and cc's.

the subject shows who updated the calendar
and description entered into the userform is placed in the body

then i also have a form that allows editing/adding email addresses.

like i mentioned, just an idea

--


Gary


wrote in message
oups.com...
Quick Background: Excel 2000, Outlook 2003. I have a worksheet with 7
small tables, each one represents a reconcilement for a different
office. If there is an out of balance condition it is reflected in one
cell (say E16) which contains a simple formula (for instance
"=e14-e15"). Any time the reconcilement doesn't balance I have
to email a copy of the table to the appropriate persons (2 in each
case). The process I have now requires me to open Outlook, key in the
email addresses, subject, body and cut and paste the corresponding
table from Excel into it. Example:

To: ;
Subject: Office Reconcilement
Body:

Hi,

Attached is a screenshot of your branch reconcilement which
reflects a difference of $420.00, please research and clear.

Thanks,

Phillip


What I would like is a button that when clicked does the same and pulls
the email information (addresses, subject, body) from designated cells
in the spreadsheet. The cells might look like this:

Cell M2:
Cell M3:
Cell M4: "Office Reconcilement"
Cell M5: "Hi, Attached is a screenshot of your branch reconcilement
reflecting a difference of"
Cell M6: =E16 (cell which shows amount of difference)
Cell M7: "Please research and clear. Thanks, Phillip

The last part of the routine would cut and paste the table into the
bottom of the body of the email.

I don't care about the warning message and if adding the total into
the text of the body complicates matters I can leave it out.

Thanks in advance!