Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending macro based e-mail with built-in "Heading" and "Text"
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending macro based e-mail with built-in "Heading" and "Text"
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 , 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending macro based e-mail with built-in "Heading" and "Text"
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 , 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
How do I change the column heading in Excel to display "A" "B" "C | New Users to Excel | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Macro to concatenate into "B1" B2 thru B"x" based on new data in "Col A" | Excel Discussion (Misc queries) | |||
Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) |