Excel and Outlook Email

[tbieri]

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").Copy

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

 

[Bob Phillips]

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]

Bob,

Thanks for the reply, that does solve the first part...

regards,
t

 

[Dick Kusleika]

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/olSending.htm#Using_HTML_in_Message_Body

 

[tbieri]

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

 

[Ron de Bruin]

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.aspx?scid=kb;en-us;816644
--
Regards Ron de Bruin
http://www.rondebruin.nl


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

 

[tbieri]

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.aspx?scid=kb;en-us;816644
--
Regards Ron de Bruin
http://www.rondebruin.nl


"tbieri" <[email protected]> wrote in

message news:[email protected]...