Emailing range in message body using VBA - problem

[Steve]

I used Article ID: 816644 and copied and pasted to set up in a macro. I get
error message 430 - Class does not support automation or does not support
expected interface. The code >

Sub mail1()
Dim CSR As String
Set Location = Worksheets("CSRs").Range("Schedules")
CSR = Application.WorksheetFunction.Index(Location, 1, 7)
ActiveSheet.Range("rpt01").Select
ActiveWorkbook.EnvelopeVisible = True
With ActiveSheet.MailEnvelope
.Item.To = CSR
.Item.Subject = "Daily Report"
.Item.Send
End With
End Sub

With other programming I was able to send the whole worksheet as an
attachment in an email. I was also able to send the range in the message
body using SendKeys but that gave erratic results.

I just can't seem to send a range as the message body using VBA without
SendKeys. Suggestions?

 

[Jacob Skaria]

Are you getting the error while referring the range directly instead of using
the named ranges.

If this post helps click Yes

 

[Ron de Bruin]

Hi Steve

Is your Outlook version the same as your other Office programs ?

You can use this also

Mail worksheet in the body
http://www.rondebruin.nl/mail/folder3/mail2.htm

Mail Range or Selection in the body
http://www.rondebruin.nl/mail/folder3/mail4.htm


Seee also my Envelope page
http://www.rondebruin.nl/mail/folder3/mailenvelope.htm

 

[Steve]

Ron,

I copied/adapted your "mail selection in the body" code, using another
subroutine to preset the range and pass the email address to your subroutine.
I worked great. You probably got an email from an interim adaptation I
tried before I realized your email address was in there by default.

One problem > At work we normally have Outlook open all the time. Your
programming puts the focus on Outlook over Excel and sends the email after
you acknowledge the confirmation box. But then Outlook stays on top. We
need it to at least go back to to the worksheet location, and ideally never
shift the focus away from Excel except for the acknowledgment box that pops
up.

Please advise. We use Office 2003.

Thanks,

Steve

 

[Steve]

Based on buttons, I have about 20 small macros that select a particular named
range ("rpt01" in the example below) and select the appropriate email address.

If a long subroutine is required (such as Ron de Bruin's, another responder)
I would use the small macro to preset the named range and pass the email
address to the long subroutine.

We use Office 2003 and normally have email open all day.

Thanks,

Steve

 

[Steve]

Based on buttons, I have about 20 small macros that select a particular named
range ("rpt01" in the example below) and select the appropriate email address.

If a long subroutine is required (such as Ron de Bruin's, another responder)
I would use the small macro to preset the named range and pass the email
address to the long subroutine.

We use Office 2003 and normally have email open all day.

Thanks,

Steve

 

[Faysal Chaudhary]

Hi Steve

Is your Outlook version the same as your other Office programs ?

You can use this also

Mail worksheet in the body
http://www.rondebruin.nl/mail/folder3/mail2.htm

Mail Range or Selection in the body
http://www.rondebruin.nl/mail/folder3/mail4.htm


Seee also my Envelope page
http://www.rondebruin.nl/mail/folder3/mailenvelope.htm

Have Found your articles very helpful and used your suggested code to automate my routine tasks. Wonderful and thanks again.