In this blog post I will illustrate an easy approach for emailing a desired tab of an Excel Spreadsheet without needing to log into Outlook and attaching an entire workbook to an email message.
Consider the MS Excel spreadsheet below containing three tabs (i.e. Sheet1, Sheet2, and Sheet3). The goal is to send out an email containing as an attachment only the active tab, i.e. Sheet1, to select recipients (without needing to delete sheet2 and sheet3 manually before sending it).
STEP 1] Go to the “Developer” on the ribbon and under the “Controls” section select the button symbol under “Form Control”. Note: If the Developer section is not visible on the ribbon of your MS Excel spreadsheet, click here for steps on how to activate it.
STEP 2] The “Assign Macro” screen will appear after inserting a button in the workbook. Input a name the Button. I this case I’ve named it “Email_This Sheet”.
STEP 3] after entering the name of the button click on “New”. The VBA editor page appears. Insert the code within the attached Notepad document below in the code page.
STEP 4] Save the page by clicking on the save symbol on the corner left hand side of the VBA editor screen. Ensure that that you are saving it as a “Macro-Enabled Workbook”.
STEP 5] Click on the command button that has been created. The outlook page should appear with a MS Excel attachment containing ONLY the active tab (i.e. Sheet1) to your recipients. The attachment is now ready to be sent through Outlook.
In order to ensure that the procedure has been performed correctly open the attachment from Outlook. As you could observe in the screenshot below, the opened attachment only contains the first tab (i.e. Sheet1) of the original Excel file and sheet2 and sheet3 have been suppressed in this attachment.
Source for VBA Code: http://www.techonthenet.com/excel/macros/email_sheet2007.php