VBA to Email Range in body and attach sheets concurrently

[sowzees31]

Hi everyone,

I am looking for some code that will do two things at once, Ive managed the codes to do both of these functions seperately but not together. I'm attempting write a macro that will send an email through excel that pastes a range of cells in the body of the message and creates a workbook attaching 3 sheets from the same active workbook at the same time.

Is this possible?
Any ideas on what the code would look like?

Thanks, John

[Rob342]

Take a look at this site might be what you are looking for

www.rondebruin.com (http://www.rondebruin.com)

Rob

[sowzees31]

Yes Rob I've been through Ron's site it seems to be the comprehensive source on this stuff. But none of his macros show how to attach and sheet and put a range of cells in the body on the same email.

[Rob342]

John

Can you attach a copy of your workbook & where you are now
Take a look tomorrow if that ok

Rob

[sowzees31]

OK here is what I'm using to send the range from active sheet. It works perfectly for what i'm trying to do, now I just need to figure out how to attach the "Master Inclus & Exclus_a" and "Master Inclus & Exclus_b" sheets onto that email. I would be very grateful for any help you can give this newb on this.

[Rob342]

try this not tested.
I always pefer to use outlook to send this sort of data.


Sub Send_Range()

' Select the range of cells on the active worksheet.
ActiveSheet.Range("A26:b46").Select

' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = True

' Set the optional introduction field thats adds
' some header text to the email body. It also sets
' the To and Subject lines. Finally the message
' is sent.
With ActiveSheet.MailEnvelope
.Introduction = "This is a sample worksheet."
.Item.To = "xxxxxx@gmail.com"
.Item.Subject = "Go Notification"
.Item.Add Worksheets("Master Inclus & Exclus_a")
.Item.Add Worksheets("Master Inclus & Exclus_b")
.Item.Send
End With
End Sub

[sowzees31]

hmm getting an error on:
.Item.Add Worksheets("Master Inclus & Exclus_a")
.Item.Add Worksheets("Master Inclus & Exclus_b")
Object not Supported.
Should I try and find something that selects the workbook as object, and send the range and sheets that way?

[Rob342]

Hi John

Have been doing some reading & i dont think you can do what you are asking, it can be either 1 way or another, but not both using the routine you have now.
You can do it in Outlook, is that the way to go?

Rob