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?

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

If this post helps click Yes
---------------
Jacob Skaria


"Steve" wrote:

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?

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Steve" wrote in message ...
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?

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


"Ron de Bruin" wrote:

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Steve" wrote in message ...
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?

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

"Jacob Skaria" wrote:

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

If this post helps click Yes
---------------
Jacob Skaria


"Steve" wrote:

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?

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

"Jacob Skaria" wrote:

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

If this post helps click Yes
---------------
Jacob Skaria


"Steve" wrote:

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?