Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. 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.

Sending Single Worksheets via E-mail

Written by Allen Wyatt (last updated March 27, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


5

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 Create 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. 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 = "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.

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:

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

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8508) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel 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

Finding a Lost Menu Bar

Once in a while Word can get confused and stop displaying an object you need to fully use Word. If your menu bar suddenly ...

Discover More

Sorting Decimal Values

Government and industrial organizations often use a numbering system that relies upon a number both before and after a ...

Discover More

Adding Serial Commas in a Sentence

Part of the job of an editor is to apply standards of grammar to text written by someone else. One standard that may need ...

Discover More

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!

More ExcelTips (ribbon)

Creating a Dynamic Hyperlink

Want to create a hyperlink that will always display a different worksheet in your workbook? There are several ways to do ...

Discover More

Pasting a Hyperlink

Need a quick link within a document to some external data? You can paste information so that Excel treats it just like a ...

Discover More

Hyperlink Doesn't Match Cell Contents

When you add a hyperlink to a worksheet, over time and after doing a bunch of editing, what you see in the cell can get ...

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

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is six minus 4?

2023-10-18 09:49:58

J. Woolley

My latest comment below suggested this VBA statement to send the active workbook as an email attachment:
    Application.Dialogs(xlDialogSendMail).Show
Here is an equivalent statement:
Application.CommandBars.ExecuteMso "FileSendAsAttachment"
The following statement will prepare a PDF of the active workbook for sending as an email attachment:
Application.CommandBars.ExecuteMso "FileEmailAsPdfEmailAttachment"
These match the File > Share menu results.


2023-10-06 15:21:57

J. Woolley

My earlier comment re. ToggleOutlookEnvelope says, "...you must have Outlook installed." Now I believe you only need an email app that is compatible with the Messaging Application Programming Interface (MAPI) such as Windows Mail or Mozilla Thunderbird, but I can only test Outlook. If you have tried the macro with another email app, please describe your result. (To determine your default app, see Windows Settings > Apps > Default Apps > Email.)

Re. the Tip's macro, one of the following two alternate MAPI methods could be used instead of oMail (Outlook) to send the active workbook as an email attachment:
    Application.Dialogs(xlDialogSendMail).Show
    ActiveWorkbook.SendMail Recipients:="...", Subject:="..."
To check if your default mail app supports MAPI:
    If Application.MailSystem <> xlMAPI Then MsgBox "MAPI is not supported"
By the way, you might also consider File > Share which can initiate an email with the active workbook's file or a PDF of its contents attached.


2023-10-02 10:06:47

J. Woolley

Re. my most recent comment below, I forgot to mention you can send the content of the sheet's used range or limit it to a selected contiguous range like B11:H50.


2023-10-01 16:26:04

J. Woolley

https://excelribbon.tips.net/T008508_Sending_Single_Worksheets_via_E-mail.html

My Excel Toolbox's ToggleOutlookEnvelope macro (Ctrl+T O E) makes it easy to send a sheet's content in the body of an email message (see Figure 1 below) , but you must have Outlook installed. The macro supports Undo (Ctrl+Z).
Here is an abbreviated version:

Sub ToggleOutlookEnvelope()
    Const myName As String = "ToggleOutlookEnvelope"
    On Error GoTo ErrHandler
    With ActiveWorkbook
        .EnvelopeVisible = (Not .EnvelopeVisible)
    End With
    Application.OnUndo myName, (ThisWorkbook.Name + "!" + myName)
    Exit Sub
ErrHandler: Beep
End Sub

See https://sites.google.com/view/MyExcelToolbox

Figure 1. 


2021-03-29 08:32:13

Bob Brown

I do the above procedure fequently at work, by printing the worksheet to a PDF or XPS file, using the drpdown list of printers. Then distribute it as an email attachment over the network. This way, the information or formulas cannot be edited easily.

Rgds,
Bob Brown


This Site

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.

Newest Tips
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.