Attach 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 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.

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.

 

[Ken Slovak - [MVP - Outlook]]

As far as I know you will need to save each sheet as a separate workbook,
but you might want to post in an Excel group and see if they have any
suggestions.

 

[wpiet]

Thanks, Ken.
I'll try that.
--
Will

As far as I know you will need to save each sheet as a separate workbook,
but you might want to post in an Excel group and see if they have any
suggestions.