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