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
.