Sending macro based e-mail with built-in "Heading" and "Text"

Dear friends,

I am desperately trying to figure out how to send an e-mail with updated
worksheet to individual field officers without having to type in the
"SUBJECT" and "MESSAGE or BODY" in the e-mail. (i.e. built-in Heading and
Message)

The only possibility I've found so far is when using a "Routing Slip".
Which is as shown below:

Workbooks("salpietro.xls").HasRoutingSlip = True
With Workbooks("salpietro.xls").RoutingSlip
.Delivery = xlOneAfterAnother
.Recipients = Array("Thomas Windsor", "Silvia Seizt", "Bernard Teuchner")
.Subject = "The Completed Workprogramme"
.Message = "Here is the completed Workprogramme. What do you think?"
End With
Workbooks("salpietro.xls").Route

This works fine as a Routing Message, however, this is not exactly what I
want. I need to send different Worksheets to individual Responsible Officers
and therefore, I do not need to "Routing Slip" function.

===========================

THIS IS WHAT I HAVE PREPARED:

Sub SendMail2()

'Extract and Save Individual WorkSheet in the Public Folder and then Send to
Responsible Officer their individual files separately:

Sheets("Salpietro").Select
Sheets("Salpietro").Copy
ActiveWorkbook.SaveAs Filename:="V:\current tables\Salpietro.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
With Workbooks("Salpietro.xls")
.SendMail Recipients:="David Campbell"
.Subject = "Art 7 Contracts"
.Message = "Here is the Workbook for you. What do you think?"
End With
ActiveWorkbook.Close
Windows("Art7-Update 29Dec04.xls").Activate
End Sub

When I run the macro, I am however, still prompted to provide the e-mail
address, the Subject and the Message. Once I've done that I do receive the
the e-mail but without the "Subject" and the "Message".

Could you please, please help me solve this problem.

Many thanks in advance,
Prabha

prabha, see if this will help any
http://www.rondebruin.nl/sendmail.htm#Tips
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Prabha" <[email protected]> wrote in message
news:[email protected]...
>
> Dear friends,
>
> I am desperately trying to figure out how to send an e-mail with updated
> worksheet to individual field officers without having to type in the
> "SUBJECT" and "MESSAGE or BODY" in the e-mail. (i.e. built-in Heading and
> Message)
>
> The only possibility I've found so far is when using a "Routing Slip".
> Which is as shown below:
>
> Workbooks("salpietro.xls").HasRoutingSlip = True
> With Workbooks("salpietro.xls").RoutingSlip
> .Delivery = xlOneAfterAnother
> .Recipients = Array("Thomas Windsor", "Silvia Seizt", "Bernard
> Teuchner")
> .Subject = "The Completed Workprogramme"
> .Message = "Here is the completed Workprogramme. What do you think?"
> End With
> Workbooks("salpietro.xls").Route
>
> This works fine as a Routing Message, however, this is not exactly what I
> want. I need to send different Worksheets to individual Responsible
> Officers
> and therefore, I do not need to "Routing Slip" function.
>
> ===========================
>
> THIS IS WHAT I HAVE PREPARED:
>
> Sub SendMail2()
>
> 'Extract and Save Individual WorkSheet in the Public Folder and then Send
> to
> Responsible Officer their individual files separately:
>
> Sheets("Salpietro").Select
> Sheets("Salpietro").Copy
> ActiveWorkbook.SaveAs Filename:="V:\current tables\Salpietro.xls", _
> FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
> ReadOnlyRecommended:=False, CreateBackup:=False
> With Workbooks("Salpietro.xls")
> .SendMail Recipients:="David Campbell"
> .Subject = "Art 7 Contracts"
> .Message = "Here is the Workbook for you. What do you think?"
> End With
> ActiveWorkbook.Close
> Windows("Art7-Update 29Dec04.xls").Activate
> End Sub
>
> When I run the macro, I am however, still prompted to provide the e-mail
> address, the Subject and the Message. Once I've done that I do receive
> the
> the e-mail but without the "Subject" and the "Message".
>
> Could you please, please help me solve this problem.
>
> Many thanks in advance,
> Prabha
>
>

You have done it correct .. almost..
Please note that in case of workbooks("').SendMail
you can add only recipients and subject, not message body.

Also the Subject is the part of .SendMail method
in your code the .Subject will apply to the Workbook
and not the SendMail method.
I am surprised why you are not getting 'Method or Data member not found
error.'
Seems you have On Error Resume Next mentioned earlier in the code.

Anyway, it should be:
With Workbooks("Salpietro.xls")
.SendMail Recipients:[email protected], Subject:="Art 7
Contracts"
.Close
End With

As for it prompting to provide 'E-Mail' address this is NOT Excel which is
asking. It is your mail client (outlook / outlook express) which is asking
this.
Because it could not Resolve "David Campbell" to his e-mail address
Therefore instead of the display name of the Contact, use the e-mail address
of the contact.

Sharad

"Prabha" <[email protected]> wrote in message
news:[email protected]...
>
> Dear friends,
>
> I am desperately trying to figure out how to send an e-mail with updated
> worksheet to individual field officers without having to type in the
> "SUBJECT" and "MESSAGE or BODY" in the e-mail. (i.e. built-in Heading and
> Message)
>
> The only possibility I've found so far is when using a "Routing Slip".
> Which is as shown below:
>
> Workbooks("salpietro.xls").HasRoutingSlip = True
> With Workbooks("salpietro.xls").RoutingSlip
> .Delivery = xlOneAfterAnother
> .Recipients = Array("Thomas Windsor", "Silvia Seizt", "Bernard
> Teuchner")
> .Subject = "The Completed Workprogramme"
> .Message = "Here is the completed Workprogramme. What do you think?"
> End With
> Workbooks("salpietro.xls").Route
>
> This works fine as a Routing Message, however, this is not exactly what I
> want. I need to send different Worksheets to individual Responsible
> Officers
> and therefore, I do not need to "Routing Slip" function.
>
> ===========================
>
> THIS IS WHAT I HAVE PREPARED:
>
> Sub SendMail2()
>
> 'Extract and Save Individual WorkSheet in the Public Folder and then Send
> to
> Responsible Officer their individual files separately:
>
> Sheets("Salpietro").Select
> Sheets("Salpietro").Copy
> ActiveWorkbook.SaveAs Filename:="V:\current tables\Salpietro.xls", _
> FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
> ReadOnlyRecommended:=False, CreateBackup:=False
> With Workbooks("Salpietro.xls")
> .SendMail Recipients:="David Campbell"
> .Subject = "Art 7 Contracts"
> .Message = "Here is the Workbook for you. What do you think?"
> End With
> ActiveWorkbook.Close
> Windows("Art7-Update 29Dec04.xls").Activate
> End Sub
>
> When I run the macro, I am however, still prompted to provide the e-mail
> address, the Subject and the Message. Once I've done that I do receive
> the
> the e-mail but without the "Subject" and the "Message".
>
> Could you please, please help me solve this problem.
>
> Many thanks in advance,
> Prabha
>
>

Dear Sharad,
Appreciated you help. Many thanks indeed.
It works brill, just a shame that there isn't any other way to include the
"Body Text" as well. That would've completely taken the pain of retyping the
"Body" many times.
Thank you once again.
Prabha


"Sharad Naik" wrote:

> You have done it correct .. almost..
> Please note that in case of workbooks("').SendMail
> you can add only recipients and subject, not message body.
>
> Also the Subject is the part of .SendMail method
> in your code the .Subject will apply to the Workbook
> and not the SendMail method.
> I am surprised why you are not getting 'Method or Data member not found
> error.'
> Seems you have On Error Resume Next mentioned earlier in the code.
>
> Anyway, it should be:
> With Workbooks("Salpietro.xls")
> .SendMail Recipients:[email protected], Subject:="Art 7
> Contracts"
> .Close
> End With
>
> As for it prompting to provide 'E-Mail' address this is NOT Excel which is
> asking. It is your mail client (outlook / outlook express) which is asking
> this.
> Because it could not Resolve "David Campbell" to his e-mail address
> Therefore instead of the display name of the Contact, use the e-mail address
> of the contact.
>
> Sharad
>
> "Prabha" <[email protected]> wrote in message
> news:[email protected]...
> >
> > Dear friends,
> >
> > I am desperately trying to figure out how to send an e-mail with updated
> > worksheet to individual field officers without having to type in the
> > "SUBJECT" and "MESSAGE or BODY" in the e-mail. (i.e. built-in Heading and
> > Message)
> >
> > The only possibility I've found so far is when using a "Routing Slip".
> > Which is as shown below:
> >
> > Workbooks("salpietro.xls").HasRoutingSlip = True
> > With Workbooks("salpietro.xls").RoutingSlip
> > .Delivery = xlOneAfterAnother
> > .Recipients = Array("Thomas Windsor", "Silvia Seizt", "Bernard
> > Teuchner")
> > .Subject = "The Completed Workprogramme"
> > .Message = "Here is the completed Workprogramme. What do you think?"
> > End With
> > Workbooks("salpietro.xls").Route
> >
> > This works fine as a Routing Message, however, this is not exactly what I
> > want. I need to send different Worksheets to individual Responsible
> > Officers
> > and therefore, I do not need to "Routing Slip" function.
> >
> > ===========================
> >
> > THIS IS WHAT I HAVE PREPARED:
> >
> > Sub SendMail2()
> >
> > 'Extract and Save Individual WorkSheet in the Public Folder and then Send
> > to
> > Responsible Officer their individual files separately:
> >
> > Sheets("Salpietro").Select
> > Sheets("Salpietro").Copy
> > ActiveWorkbook.SaveAs Filename:="V:\current tables\Salpietro.xls", _
> > FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
> > ReadOnlyRecommended:=False, CreateBackup:=False
> > With Workbooks("Salpietro.xls")
> > .SendMail Recipients:="David Campbell"
> > .Subject = "Art 7 Contracts"
> > .Message = "Here is the Workbook for you. What do you think?"
> > End With
> > ActiveWorkbook.Close
> > Windows("Art7-Update 29Dec04.xls").Activate
> > End Sub
> >
> > When I run the macro, I am however, still prompted to provide the e-mail
> > address, the Subject and the Message. Once I've done that I do receive
> > the
> > the e-mail but without the "Subject" and the "Message".
> >
> > Could you please, please help me solve this problem.
> >
> > Many thanks in advance,
> > Prabha
> >
> >
>
>
>