Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and Outlook Email
Howdy,
I am creating an email from excel using the example from www.dicks-clicks.com, but wanted to to add the following twist: I want to define the To, CC, and subject from cells within the workbook and I want to paste the excel range as picture, versus sending an excel object. I am encountering errors with the first part and not sure how to approach the second part. Here is what I have so far: Sub CreateEmail() Dim olApp As Object Dim olMail As Object Dim stRecipient As String Dim stCC As String Dim stSubject As String Set olApp = CreateObject("Outlook.Application") Set olMail = olApp.CreateItem(0) Set stRecipient = ActiveSheet.Range("To_List") Set stCC = ActiveSheet.Range("CC_List") Set stSubject = ActiveSheet.Range("Subject") Set TestBody = Worksheets("Rates").Range("Weekly_Rate_Range").Cop y With olMail .To = stRecipient .cc = stCC .Subject = stSubject .Display End With Set olMail = Nothing Set olApp = Nothing End Sub I get an "Object required" error at "Set stRecipient = ". To_List, CC_List, and Subject are named cells on the active sheet. TIA Regards t |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and Outlook Email
First part, change
Set stRecipient = ActiveSheet.Range("To_List") Set stCC = ActiveSheet.Range("CC_List") Set stSubject = ActiveSheet.Range("Subject") to stRecipient = ActiveSheet.Range("To_List") stCC = ActiveSheet.Range("CC_List") stSubject = ActiveSheet.Range("Subject") -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "tbieri" wrote in message ... Howdy, I am creating an email from excel using the example from www.dicks-clicks.com, but wanted to to add the following twist: I want to define the To, CC, and subject from cells within the workbook and I want to paste the excel range as picture, versus sending an excel object. I am encountering errors with the first part and not sure how to approach the second part. Here is what I have so far: Sub CreateEmail() Dim olApp As Object Dim olMail As Object Dim stRecipient As String Dim stCC As String Dim stSubject As String Set olApp = CreateObject("Outlook.Application") Set olMail = olApp.CreateItem(0) Set stRecipient = ActiveSheet.Range("To_List") Set stCC = ActiveSheet.Range("CC_List") Set stSubject = ActiveSheet.Range("Subject") Set TestBody = Worksheets("Rates").Range("Weekly_Rate_Range").Cop y With olMail .To = stRecipient .cc = stCC .Subject = stSubject .Display End With Set olMail = Nothing Set olApp = Nothing End Sub I get an "Object required" error at "Set stRecipient = ". To_List, CC_List, and Subject are named cells on the active sheet. TIA Regards t |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and Outlook Email
Bob,
Thanks for the reply, that does solve the first part... regards, t -----Original Message----- First part, change Set stRecipient = ActiveSheet.Range("To_List") Set stCC = ActiveSheet.Range("CC_List") Set stSubject = ActiveSheet.Range("Subject") to stRecipient = ActiveSheet.Range("To_List") stCC = ActiveSheet.Range("CC_List") stSubject = ActiveSheet.Range("Subject") -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "tbieri" wrote in message ... Howdy, I am creating an email from excel using the example from www.dicks-clicks.com, but wanted to to add the following twist: I want to define the To, CC, and subject from cells within the workbook and I want to paste the excel range as picture, versus sending an excel object. I am encountering errors with the first part and not sure how to approach the second part. Here is what I have so far: Sub CreateEmail() Dim olApp As Object Dim olMail As Object Dim stRecipient As String Dim stCC As String Dim stSubject As String Set olApp = CreateObject("Outlook.Application") Set olMail = olApp.CreateItem(0) Set stRecipient = ActiveSheet.Range("To_List") Set stCC = ActiveSheet.Range("CC_List") Set stSubject = ActiveSheet.Range("Subject") Set TestBody = Worksheets("Rates").Range("Weekly_Rate_Range").Cop y With olMail .To = stRecipient .cc = stCC .Subject = stSubject .Display End With Set olMail = Nothing Set olApp = Nothing End Sub I get an "Object required" error at "Set stRecipient = ". To_List, CC_List, and Subject are named cells on the active sheet. TIA Regards t . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and Outlook Email
t
Is there more than one cell in "To_List" and "CC_List"? If so, you may benefit from using the Recipients collection Dim olMail as Outlook.MailItem Dim olRecip as Outlook.Recipient Dim cell as Range With olMail For Each cell In ActiveSheet.Range("To_List").Cells Set olRecip = .Recipients.Add(cell.Value) olRecip.Type = olTo Next cell For Each cell in ActiveSheet.Range("CC_List").Cells Set olRecip = .Recipients.Add(cell.Value) olRecip.Type = olCC Next cell 'other properties End With Otherwise, you need to separate the entries in those cells with a semicolon. For Each cell In ActiveSheet.Range("To_List").Cells stRecip = stRecip & cell.Value & ";" Next cell stRecip = Left(stRecip,Len(stRecip)-1) ..To = stRecip I've had absolutely no success with including pictures in email messages. The best way that I've seen is to post those pictures to a website and include the img tag in your HTMLBody property. That's a lot of work. Instead of a picture, you can include a range as HTML in the body by following http://www.dicks-clicks.com/excel/ol...n_Message_Body -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "tbieri" wrote in message ... Howdy, I am creating an email from excel using the example from www.dicks-clicks.com, but wanted to to add the following twist: I want to define the To, CC, and subject from cells within the workbook and I want to paste the excel range as picture, versus sending an excel object. I am encountering errors with the first part and not sure how to approach the second part. Here is what I have so far: Sub CreateEmail() Dim olApp As Object Dim olMail As Object Dim stRecipient As String Dim stCC As String Dim stSubject As String Set olApp = CreateObject("Outlook.Application") Set olMail = olApp.CreateItem(0) Set stRecipient = ActiveSheet.Range("To_List") Set stCC = ActiveSheet.Range("CC_List") Set stSubject = ActiveSheet.Range("Subject") Set TestBody = Worksheets("Rates").Range("Weekly_Rate_Range").Cop y With olMail .To = stRecipient .cc = stCC .Subject = stSubject .Display End With Set olMail = Nothing Set olApp = Nothing End Sub I get an "Object required" error at "Set stRecipient = ". To_List, CC_List, and Subject are named cells on the active sheet. TIA Regards t |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and Outlook Email
Dick,
Thanks for your input. There is only one cell, with multiple names, separated by semi-colons. Removing the "Set" was the key. I guess the short-term solution regarding the picture is to copy the range in the macro; when the email is displayed I use Paste Special. Extra step, but works. Regards, t -----Original Message----- t Is there more than one cell in "To_List" and "CC_List"? If so, you may benefit from using the Recipients collection Dim olMail as Outlook.MailItem Dim olRecip as Outlook.Recipient Dim cell as Range With olMail For Each cell In ActiveSheet.Range("To_List").Cells Set olRecip = .Recipients.Add(cell.Value) olRecip.Type = olTo Next cell For Each cell in ActiveSheet.Range("CC_List").Cells Set olRecip = .Recipients.Add(cell.Value) olRecip.Type = olCC Next cell 'other properties End With Otherwise, you need to separate the entries in those cells with a semicolon. For Each cell In ActiveSheet.Range("To_List").Cells stRecip = stRecip & cell.Value & ";" Next cell stRecip = Left(stRecip,Len(stRecip)-1) ..To = stRecip I've had absolutely no success with including pictures in email messages. The best way that I've seen is to post those pictures to a website and include the img tag in your HTMLBody property. That's a lot of work. Instead of a picture, you can include a range as HTML in the body by following http://www.dicks-clicks.com/excel/ol...n_Message_Body -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "tbieri" wrote in message ... Howdy, I am creating an email from excel using the example from www.dicks-clicks.com, but wanted to to add the following twist: I want to define the To, CC, and subject from cells within the workbook and I want to paste the excel range as picture, versus sending an excel object. I am encountering errors with the first part and not sure how to approach the second part. Here is what I have so far: Sub CreateEmail() Dim olApp As Object Dim olMail As Object Dim stRecipient As String Dim stCC As String Dim stSubject As String Set olApp = CreateObject("Outlook.Application") Set olMail = olApp.CreateItem(0) Set stRecipient = ActiveSheet.Range("To_List") Set stCC = ActiveSheet.Range("CC_List") Set stSubject = ActiveSheet.Range("Subject") Set TestBody = Worksheets("Rates").Range("Weekly_Rate_Range").Cop y With olMail .To = stRecipient .cc = stCC .Subject = stSubject .Display End With Set olMail = Nothing Set olApp = Nothing End Sub I get an "Object required" error at "Set stRecipient = ". To_List, CC_List, and Subject are named cells on the active sheet. TIA Regards t . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and Outlook Email
Hi tbieri
Maybe you can use this If you use Office 2002 or 2003 see this KB article.How to Send a Range of Cells Using VBA. (with shapes)http://support.microsoft.com/default...b;en-us;816644 -- Regards Ron de Bruin http://www.rondebruin.nl "tbieri" wrote in message ... Dick, Thanks for your input. There is only one cell, with multiple names, separated by semi-colons. Removing the "Set" was the key. I guess the short-term solution regarding the picture is to copy the range in the macro; when the email is displayed I use Paste Special. Extra step, but works. Regards, t -----Original Message----- t Is there more than one cell in "To_List" and "CC_List"? If so, you may benefit from using the Recipients collection Dim olMail as Outlook.MailItem Dim olRecip as Outlook.Recipient Dim cell as Range With olMail For Each cell In ActiveSheet.Range("To_List").Cells Set olRecip = .Recipients.Add(cell.Value) olRecip.Type = olTo Next cell For Each cell in ActiveSheet.Range("CC_List").Cells Set olRecip = .Recipients.Add(cell.Value) olRecip.Type = olCC Next cell 'other properties End With Otherwise, you need to separate the entries in those cells with a semicolon. For Each cell In ActiveSheet.Range("To_List").Cells stRecip = stRecip & cell.Value & ";" Next cell stRecip = Left(stRecip,Len(stRecip)-1) ..To = stRecip I've had absolutely no success with including pictures in email messages. The best way that I've seen is to post those pictures to a website and include the img tag in your HTMLBody property. That's a lot of work. Instead of a picture, you can include a range as HTML in the body by following http://www.dicks-clicks.com/excel/ol...n_Message_Body -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "tbieri" wrote in message ... Howdy, I am creating an email from excel using the example from www.dicks-clicks.com, but wanted to to add the following twist: I want to define the To, CC, and subject from cells within the workbook and I want to paste the excel range as picture, versus sending an excel object. I am encountering errors with the first part and not sure how to approach the second part. Here is what I have so far: Sub CreateEmail() Dim olApp As Object Dim olMail As Object Dim stRecipient As String Dim stCC As String Dim stSubject As String Set olApp = CreateObject("Outlook.Application") Set olMail = olApp.CreateItem(0) Set stRecipient = ActiveSheet.Range("To_List") Set stCC = ActiveSheet.Range("CC_List") Set stSubject = ActiveSheet.Range("Subject") Set TestBody = Worksheets("Rates").Range("Weekly_Rate_Range").Cop y With olMail .To = stRecipient .cc = stCC .Subject = stSubject .Display End With Set olMail = Nothing Set olApp = Nothing End Sub I get an "Object required" error at "Set stRecipient = ". To_List, CC_List, and Subject are named cells on the active sheet. TIA Regards t . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and Outlook Email
Ron,
Thanks for the post, that does work pretty slick, but we are running mixed versions, with 2000 and 2002. Regards, Tim -----Original Message----- Hi tbieri Maybe you can use this If you use Office 2002 or 2003 see this KB article.How to Send a Range of Cells Using VBA. (with shapes)http://support.microsoft.com/default...b;en-us;816644 -- Regards Ron de Bruin http://www.rondebruin.nl "tbieri" wrote in message ... Dick, Thanks for your input. There is only one cell, with multiple names, separated by semi-colons. Removing the "Set" was the key. I guess the short-term solution regarding the picture is to copy the range in the macro; when the email is displayed I use Paste Special. Extra step, but works. Regards, t -----Original Message----- t Is there more than one cell in "To_List" and "CC_List"? If so, you may benefit from using the Recipients collection Dim olMail as Outlook.MailItem Dim olRecip as Outlook.Recipient Dim cell as Range With olMail For Each cell In ActiveSheet.Range("To_List").Cells Set olRecip = .Recipients.Add(cell.Value) olRecip.Type = olTo Next cell For Each cell in ActiveSheet.Range("CC_List").Cells Set olRecip = .Recipients.Add(cell.Value) olRecip.Type = olCC Next cell 'other properties End With Otherwise, you need to separate the entries in those cells with a semicolon. For Each cell In ActiveSheet.Range("To_List").Cells stRecip = stRecip & cell.Value & ";" Next cell stRecip = Left(stRecip,Len(stRecip)-1) ..To = stRecip I've had absolutely no success with including pictures in email messages. The best way that I've seen is to post those pictures to a website and include the img tag in your HTMLBody property. That's a lot of work. Instead of a picture, you can include a range as HTML in the body by following http://www.dicks-clicks.com/excel/ol...n_Message_Body -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "tbieri" wrote in message ... Howdy, I am creating an email from excel using the example from www.dicks-clicks.com, but wanted to to add the following twist: I want to define the To, CC, and subject from cells within the workbook and I want to paste the excel range as picture, versus sending an excel object. I am encountering errors with the first part and not sure how to approach the second part. Here is what I have so far: Sub CreateEmail() Dim olApp As Object Dim olMail As Object Dim stRecipient As String Dim stCC As String Dim stSubject As String Set olApp = CreateObject("Outlook.Application") Set olMail = olApp.CreateItem(0) Set stRecipient = ActiveSheet.Range("To_List") Set stCC = ActiveSheet.Range("CC_List") Set stSubject = ActiveSheet.Range("Subject") Set TestBody = Worksheets("Rates").Range("Weekly_Rate_Range").Cop y With olMail .To = stRecipient .cc = stCC .Subject = stSubject .Display End With Set olMail = Nothing Set olApp = Nothing End Sub I get an "Object required" error at "Set stRecipient = ". To_List, CC_List, and Subject are named cells on the active sheet. TIA Regards t . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel and Email/Outlook | Excel Worksheet Functions | |||
How do I get excel to create an email using Outlook? | Excel Discussion (Misc queries) | |||
Email from Excel WITHOUT Outlook | Excel Programming | |||
Email from Excel WITHOUT Outlook | Excel Programming | |||
Excel contents to Outlook email | Excel Programming |