Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Sending Single Worksheets via E-mail.
by Allen Wyatt
(last updated October 4, 2014)
German asked if there is a way to send one worksheet in a workbook as an e-mail attachment without sending the entire workbook. The short answer is that you cannot—a worksheet, by itself, cannot exist as an "entity" that you can attach to an e-mail. You can only send files as attachments to e-mails, which means that you must have a workbook file to send.
Of course, it is easy to make a workbook from a single worksheet. If you only need to do this once in a while, then the easiest way is to follow these steps:
Figure 1. The Move or Copy dialog box.
At this point, you should see a new workbook with a single worksheet in it—a copy of the worksheet you want to send. Save this workbook and e-mail it, and you've accomplished what you wanted to do. Once it is e-mailed, you can delete the workbook from your system, as your worksheet is still in the original workbook, as well.
If you need to routinely e-mail the current worksheet to someone else, you may want to create a macro that will do the task for you. The macro you create will vary, depending on the e-mail program you are using. For this reason, it is not possible to provide a comprehensive macro-based answer in this tip. However, it may be instructive to provide an example of a macro that can e-mail a worksheet using Outlook as the mail program.
Sub EmailWithOutlook() Dim oApp As Object Dim oMail As Object Dim WB As Workbook Dim FileName As String Dim wSht As Worksheet Dim shtName As String Application.ScreenUpdating = False ' Make a copy of the active worksheet ' and save it to a temporary file ActiveSheet.Copy Set WB = ActiveWorkbook FileName = WB.Worksheets(1).Name On Error Resume Next Kill "C:\" & FileName On Error GoTo 0 WB.SaveAs FileName:="C:\" & FileName 'Create and show the Outlook mail item Set oApp = CreateObject("Outlook.Application") Set oMail = oApp.CreateItem(0) With oMail 'Uncomment the line below to hard code a recipient '.To = "firstname.lastname@example.org" 'Uncomment the line below to hard code a subject '.Subject = "Subject Line" 'Uncomment the lines below to hard code a body '.body = "Dear John" & vbCrLf & vbCrLf & _ '"Here is the file you asked for" .Attachments.Add WB.FullName .Display End With 'Delete the temporary file WB.ChangeFileAccess Mode:=xlReadOnly Kill WB.FullName WB.Close SaveChanges:=False 'Restore screen updating and release Outlook Application.ScreenUpdating = True Set oMail = Nothing Set oApp = Nothing End Sub
Note that the macro does effectively what was done in the earlier steps: it copies the worksheet to a new workbook and then e-mails that workbook. It then deletes the workbook and returns you to your normal use of Excel.
The macro saves the temporary workbook (the one being e-mailed) to the C: drive. If you would prefer to have the workbook saved elsewhere (or if your system restrictions stop you from saving files to the root directory of your system), simply change the location in the two lines of code that reference the C: drive.
There is one potential drawback to using the approach detailed in this tip: When you create a copy of the worksheet to a new workbook, Excel can do some funny things with some references that may be in the copied worksheet. For instance, let's say that you want to copy and e-mail Sheet1 from the original workbook. However, Sheet1 includes some references, in formulas, to cells in Sheet2 and Sheet3. When you make the copy of Sheet1 to the new workbook, Excel maintains those references back to the sheets in the original workbook. While the copied Sheet1 can be e-mailed to someone else, when that person tries to open it, the references will no longer work properly because he or she doesn't have the original workbook being referenced.
There are three potential solutions to this. The first, obviously, is to make sure you don't e-mail a single worksheet in the new workbook, but all the worksheets that the recipient may need. The second solution is to use copy and paste techniques to paste values into the new workbook. Finally, you can copy the original worksheet and then, in the copy, search for all the external references and eliminate them.
If you are looking for a more in-depth discussion of how to e-mail a worksheet using various programs, then you will definitely want to visit the following Web page:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8508) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Sending Single Worksheets via E-mail.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Do you use special characters (such as the pound sign) in your worksheet names? If so, you could run into problems creating ...Discover More
You can store all sorts of information in a worksheet, including Web addresses. If you want to open those addresses in a ...Discover More
You can configure images in Excel so that if someone clicks on them, a macro is executed. You cannot, however, have a macro ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.