MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Is it possible to send just a worksheet, rather than the whole book, through a macro

Posted by Steve on April 29, 2001 1:54 AM

Hello all,
I have a small problem I was hoping somebody may be able to help me with. I am trying to write a macro that sends a worksheet to several e-mail addresses. My problem is that when i use
ActiveWorksheet.SendMail Recipients:="e-mail address here" it sends the whole workbook, rather than just the worksheet. Does anyone know if it is possible at all (i need to do this because the whole workbook is way too large to send, therefore i am just trying to send the 1 worksheet, within the workbook).
Thankyou in advance

Posted by Dave Hawley on April 29, 2001 2:42 AM

Hi Steve

The way you can overcome this is to copy the sheet first. The default for a sheet copy is to a new workbook that will ONLY contain the copied sheet:

Sub SendSingleSheet()
'Written by OzGrid Business Applications
Dim SBookName
'Sends a copy of single Worsheet as an email attachment
ActiveWorkbook.Close SaveChanges:=False
End Sub


OzGrid Business Applications

Posted by Steve on April 29, 2001 6:35 AM

Thankyou Dave - I happened to work it out just before i read your message and that is exactly what i have done - my macro first copies the sheet to a new s/sheet then sends to the appropriate people. Thankyou so much for replying though, i really appreciate your help.