Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Sending Single Worksheets via E-mail.

Sending Single Worksheets via E-mail

by Allen Wyatt
(last updated June 28, 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:

  1. Right-click the tab for the worksheet you want to e-mail.
  2. From the resulting Context menu, choose Move or Copy. Excel displays the Move or Copy dialog box. (See Figure 1.)
  3. Figure 1. The Move or Copy dialog box.

  4. Using the To Book drop-down list, choose New Book.
  5. Make sure the Make a Copy check box is selected.
  6. Click OK.

At this point, you should see a new workbook with a single worksheet in it—a copy of the worksheet you want to send. E-mail this workbook, and you've accomplished what you wanted to do. Once it is e-mailed, you can delete the workbook, 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 = "testuser@test.com"
        '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.

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:

http://www.rondebruin.nl/win/section1.htm

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3273) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Sending Single Worksheets via E-mail.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Using Text Boundaries

Text boundaries can help you better visualize where text can appear in your document. The feature is easy to turn on and off, ...

Discover More

Changing Default Tab Stops

If you don't explicitly set tab stops in a paragraph, Word relies upon a default tab stop distance. You can adjust that ...

Discover More

Showing Only Added Text with Track Changes

Do you want to change how Track Changes displays the markup in your document? Here's how you can completely hide deleted text ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

MORE EXCELTIPS (MENU)

Automatic Text in an E-mail

When creating an e-mail address hyperlink using the Insert Hyperlink dialog box, Excel allows you to enter a subject for the ...

Discover More

Extracting E-mail Addresses from Hyperlinks

If you have a list of hyperlinked e-mail addresses in a worksheet, you may want to extract the addresses from those ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share