Attache Excel Worksheet (Not Workbook) to Email

[wpiet]

Is there a way to attach a single worksheet from an Excel workbook to an
Outlook email?
I have a workbook with multiple worksheets, each of which must attach to a
different email. The following code is in Outlook (to avoid the dreaded
Outlook OMG) and works fine as far as creating & sending the emails but, for
each sheet in the array, I need to attach a copy of the sheet.
I'm hoping to avoid saving each sheet as a separate workbook but suspect I'm
tilting at windmills.
The "Attachments.Add" line is one of many vain attempts I've made. This one,
as many others, returns Run-time error '438': Object doesn't support this
property or method.

I posted this to the Outlook group but had an MVP there suggest I try the
Excel group.

Dim XL As Object
Dim Sht As Worksheet
Dim EmlMsg As MailItem

On Error Resume Next
Set XL = GetObject(, "Excel.Application")

If XL Is Nothing Then
Set XL = CreateObject("Excel.Application")
End If

On Error GoTo 0
XL.Visible = True
XL.Workbooks.Open FileName:="Whatever.xls"

' Send e-mails

For Each Sht In XL.Sheets(Array("OPC", "BP", "WH", "CR", "Oper", "Eng"))
Sht.Activate
Set EmlMsg = CreateItem(0)

With EmlMsg
.To = XL.VLookup(XL.Range("I1"), XL.Range("DstMgrEml"), 2, False)
.Subject = "Something Clever"
.Body = XL.VLookup(XL.Range("I1"), XL.Range("DstMgrEml"), 3,
False) _
& "," & XL.Worksheets("Managers").Range("D8")
.Save
.Attachments.Add XL.Workbook.ActiveSheet
.Send
End With

Set EmlMsg = Nothing
Next Sht

Thanks.

 

[Mike H]

Hi,

All the methods can be found here

http://www.rondebruin.nl/sendmail.htm

Mike

 

[wpiet]

Thanks, Mike.
--
Will

Hi,

All the methods can be found here

http://www.rondebruin.nl/sendmail.htm

Mike