January 23rd 09, 08:35 PM
posted to microsoft.public.outlook.program_vba
|
|
|
Attach Excel Worksheet (Not Workbook) to Email
Thanks, Ken.
I'll try that.
--
Will
"Ken Slovak - [MVP - Outlook]" wrote:
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.
--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Professional Programming Outlook 2007.
Reminder Manager, Extended Reminders, Attachment Options.
http://www.slovaktech.com/products.htm
"wpiet" wrote in message
...
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.
--
Will
|