Email Worksheet Question

[MLD]

Have an Excel File with several worksheets. I want to attach only one of the
Worksheet to an email. So far it seems as if it's got to be the whole file
(with all the worksheets) or nothing. The only work around that I can think
of right now is to open a new Workbook, copy the Worksheet that I want to
email to it--give it a file name and then use the new file for my
attachment. Is there another easier way?
MLD

 

[Claus Busch]

Hi,

Am Mon, 26 May 2014 09:12:33 -0400 schrieb MLD:

Have an Excel File with several worksheets. I want to attach only one of the
Worksheet to an email. So far it seems as if it's got to be the whole file
(with all the worksheets) or nothing. The only work around that I can think
of right now is to open a new Workbook, copy the Worksheet that I want to
email to it--give it a file name and then use the new file for my
attachment. Is there another easier way?

no, you are on the right way. Please have a look:
http://www.rondebruin.nl/win/s1/outlook/amail2.htm


Regards
Claus B.

 

[MLD]

Hi,

Am Mon, 26 May 2014 09:12:33 -0400 schrieb MLD:


no, you are on the right way. Please have a look:
http://www.rondebruin.nl/win/s1/outlook/amail2.htm


Regards
Claus B.

Thanks for the reply--I did take a look at the link you listed-------it gave
me a headache <g>. I did run a test case for myself---opened a new
Workbook, copied the Worksheet I was interested in and then pasted it into
the new Workbook. Used " Save As"--gave it a name. Created an email,
inserted the File associated with the new Workbook and sent it to myself.
Came through nicely, just what I wanted. Deleted the New Workbook file since
it served it's purpose. Actually, it took me almost as long to type this
out as it did to do all the steps to accomplish the deed.
Since this an infrequent need I think I'll skip the Macro. Thanks again for
your comments.
MLD

 

[Claus Busch]

Hi,

Am Mon, 26 May 2014 10:12:44 -0400 schrieb MLD:

Thanks for the reply--I did take a look at the link you listed-------it gave
me a headache <g>.

try:

Sub SendActiveSheet()
Dim objOutlook As Object
Dim objMail As Object

Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.createitem(0)

ActiveSheet.Copy
ActiveWorkbook.SaveAs "C:\Users\Claus\Desktop\Mail.xlsx"

With objMail
.to = "(e-mail address removed)"
.Subject = "TestMail"
.Body = "Hallo Claus"
.Attachments.Add ActiveWorkbook.FullName
.display
End With

ActiveWorkbook.Close
Kill ("C:\Users\Claus\Desktop\Mail.xlsx")
End Sub


Regards
Claus B.