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

by Allen Wyatt
(last updated September 5, 2019)


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
    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 = ""
        '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
    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:


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 Office 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. ...


Selecting Fonts for a Chart

Microsoft Chart uses text to label items in a chart. Here's how to change the font used for that text.

Discover More

Saving Common Formulas

It is not uncommon to reuse formulas in a variety of workbooks. If you develop some "gotta keep" formulas, here are some ...

Discover More

Table Won't Flow to Second Page

Have you ever created a table that never seems to flow to a second page, even when it should? This could be caused by any ...

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 (ribbon)

Converting to Hyperlinks in a Shared Workbook

When you enter a URL or e-mail address in a worksheet, Excel usually converts it to a clickable hyperlink. This doesn't ...

Discover More

Pulling Apart a URL

Excel provides a good number of worksheet functions that can help you pick apart text strings in various ways. In this ...

Discover More

Converting a Range of URLs to Hyperlinks

Converting a single URL into a hyperlink is easy. Converting hundreds or thousands can be much harder if you have to rely ...

Discover More

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

View most recent newsletter.


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}] 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 four more than 9?

2019-11-24 23:35:37

Edward Williams

Did exactly what you programmed it to. Thank you for being better at this then I am.

2019-02-23 18:55:03


Code to potect sheet and unprotect email and then send email

Sub Button5_Click()
Dim R As Range

ActiveSheet.UsedRange.FormulaHidden = False
Set R = SpecialCells(ActiveSheet.UsedRange, xlCellTypeFormulas)
If R Is Nothing Then Exit Sub
R.FormulaHidden = True
R.Locked = True
' R.Value = R.Value
' R.Hidden = True
ActiveSheet.Protect Password:="password" ', UserInterfaceOnly:=True

End Sub
Sub Button6_Click()
Dim R As Range
ActiveSheet.Unprotect Password:="password"
ActiveSheet.UsedRange.FormulaHidden = False
Set R = SpecialCells(ActiveSheet.UsedRange, xlCellTypeFormulas)
If R Is Nothing Then Exit Sub
R.FormulaHidden = False
R.Locked = False
' R.Hidden = FALSE
ActiveSheet.Unprotect Password:="password"

End Sub
Private Function SpecialCells(ByVal R As Range, ByVal Typ As XlCellType, _
Optional ByVal Value As XlSpecialCellsValue = &H17) As Range
'Avoid the SpecialCells-BUG to return all cells from the current region
On Error Resume Next
Select Case Typ
Case xlCellTypeConstants, xlCellTypeFormulas
Set SpecialCells = Intersect(R, R.SpecialCells(Typ, Value))
Case Else
Set SpecialCells = Intersect(R, R.SpecialCells(Typ))
End Select
End Function

2019-02-23 18:53:36


Code to email entire book

Sub Mail_workbook_Outlook_1()

Dim OutApp As Object
Dim OutMail As Object
Dim myValue2 As Variant
myValue2 = ThisWorkbook.ActiveSheet.Range("b2").Value2
'Dim myValue As Variant
'myValue = InputBox("File Name")
Dim myValue3 As Variant
myValue3 = InputBox("Email Subject")
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.to = myValue2
'.CC =
' .BCC =
.Subject = myValue3
.Body = "Type the Body of your mail"
.Attachments.Add ActiveWorkbook.FullName

End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

2019-02-23 18:52:18


Code to email Sheet only
====\Sub Email_One_ActiveSheeeet()

Dim OlApp As Object
Dim NewMail As Object
Dim TempFilePath As String
Dim FileExt As String
Dim TempFileName As String
Dim FileFullPath As String
Dim FileFormat As Variant
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Dim myValue2 As Variant
myValue2 = ThisWorkbook.ActiveSheet.Range("b2").Value2
Dim myValue As Variant
myValue = InputBox("File Name", "SKD", "Book1")
'InputBox("File Name")
' InputBox("File Name", "SKD", Book1)
Dim myValue3 As Variant
myValue3 = InputBox("Email Subject", "SKD", "-")

With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False

End With
Set Wb1 = ThisWorkbook
Set Wb2 = ActiveWorkbook

With Wb2
If Val(Application.Version) < 12 Then
FileExt = ".xls": FileFormat = -4143
Select Case Wb1.FileFormat
Case 51: FileExt = ".xlsx": FileFormat = 51
Case 52:
If .HasVBProject Then
FileExt = ".xlsm": FileFormat = 52
FileExt = ".xlsx": FileFormat = 51
End If
Case 56: FileExt = ".xls": FileFormat = 56
Case Else: FileExt = ".xlsb": FileFormat = 50
End Select
End If
End With

TempFilePath = Environ$("temp") & "\"

TempFileName = myValue

FileFullPath = TempFilePath & TempFileName & FileExt

Wb2.SaveAs FileFullPath, FileFormat:=FileFormat

Set OlApp = CreateObject("Outlook.Application")
Set NewMail = OlApp.CreateItem(0)

On Error Resume Next
With NewMail
.to = myValue2
'.CC =
' .BCC =
.Subject = myValue3
.Body = "Type the Body of your mail"
.Attachments.Add FileFullPath
End With
On Error GoTo 0

Wb2.Close SaveChanges:=False
Kill FileFullPath

Set NewMail = Nothing
Set OlApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

2017-09-26 16:37:20

Michael Reese

For sheets with data that are displaced as the result of external or formula references, rather than copying actual sheet to an external workbook, I actually copy the data on the sheet, the use the "PasteSpecial" function using the switch "xlValuesOnly."

From The Spreadsheet Guru, here is the code that demonstrates this process:

'Copy A Range of Data

'PasteSpecial Values Only
Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues

'Clear Clipboard (removes "marching ants" around your original data set)
Application.CutCopyMode = False

Change the particular worksheets to their pertinent workbooks and worksheets. This will ensure the recipient will see a sheet of data with no conflicting underlying formulas.

2017-06-08 10:31:31

Ben Bourgeault

Hi I have have receipts that are sent out monthly. I would like to put each receipt in an excel worksheet and each of these be in one excel workbook. Is there away to link an individual email to each individual worksheet. The idea is that i would like to update the receipts on a monthly basis and then click one button and they send to their designated email addresses.

Please help

thank you,


2016-12-24 17:49:44


I have 100 rows and 6 columns in sheet1. How it is possible to send email as attach using vba , but this attach to contain only one row specific row or just last row.

2016-03-22 15:37:51

Marlene Heroux

Thank you- made what I wanted to do a breeze!

2016-02-24 12:44:18


Why is it considered an "update" when everything you do now takes more key strokes? and you make us pay for this privilege!

2016-02-23 06:48:10


Thanks for tip Barry as it is a good one.
I was wondering if you could help with the following query.
I want to personalize the body of the email by putting a line in red font and bold. Do you know how can I do that?


2016-01-30 05:35:35



Assuming that the email you want the worksheet to be sent to is in cell A1 then change the line of code:

'.To = ""


.To= WB.Worksheets(1).Range("A1")

You can do a similar thing with the body text if you want to personalise the email a little more. If cell A2 has the person's name then the line:

.Body = "Dear John" & vbCrLf & vbCrLf & "Here is the file you asked for"
.Body = "Dear " & WB.Worksheets(1).Range("A2") & vbCrLf & vbCrLf & "Here is the file you asked for"

Of course if you store the email address and person name in different cells then you just need to change cell references accordingly.

2016-01-29 11:16:51


I got the email macro to work fine but I need to know if there is a way for the macro to pull the email too address(s) from a cell or ask for the email addresses every time, this is a form that will be emailed to different people depending what the form is about. Any help would be greatly appreciated

2016-01-18 14:49:47


I'm not sure why this new 2013 is to be considered an IMPROVEMENT. It was half the key strokes to email a sheet from a workbook in older versions. I have seen VERY LITTLE I like about this newer version.
The most time consuming is the fact I can't "train" a workbook to open in exactly the place and size I want every time so I'm always moving and re sizing. I'd like to bill someone for all this time?!!!

2016-01-16 11:37:50


@ J K Chaudhary
Change to the line ".Display" to .Send" - this will just send the email without displaying the email first.


The above macro just needs to be put into a loop. But you'll need a table of at least email address plus corresponding worksheet name. The macro would work through the table sending the relevant worksheet to each recipient's email address. Probably the easiest way to to do this is to add another worksheet for this table, that way it'll be very easy to add/delete/modify the list of recipients and their respective worksheets.

Extra columns in the table could be used to personalise the email with, say, the recipient's first name.

2016-01-15 05:27:03


Dear Sir,
I have an excel workbook with 30 worksheet and I want to email each sheet to 30 different people.
How could achieve this using Outlook mail application?


2015-10-17 01:16:04

J K Chaudhary

When i run the code. Outlook opens in last. I have to press send button in outlook. it doesnot send e-mail on its own.

2015-10-08 15:39:17


Tim Rooke - while i agree that office has some issues the comparison you are making with apple has no relevance. Office was mainly created for business use and major companies as well as most companies with 100 employees or more are all using office as the mail tool for their analysis and email needs. Outlook may be obsolete for you but thousands of companies use it and in my opinion there isn't anything better at this point in time. Apple users are actually willing to pay for the office suite on their apple products because of the main issues with apple software that is intended for the same purpose. If you look at excel and it's entire capability you will not find a tool that can deliver better business use. So comparing apple products, as good as they are for the general consumer, is a mistake as they have completely different targets.

2015-10-04 13:44:24

Jamerous Macklin

Um....thank you for showing to use the um......printer on my tablet

2015-09-04 08:22:30


Fantastic are genius. Solved my problem.

2015-08-28 18:18:15


Hey Great Code, but I need a help...I want to send this whole workbook enabled for macro view...with this code, it saves and send only one worksheet, and without enabling for macro use. What do I need to change in the code in order to achieve that?


2015-05-13 12:22:40

Tim Rooke

I have to agree with Elvina. As time has gone on Mictosoft has made it more difficult to use its Office programs. It has also kept changing the interfaces of Office for an "improvement" which has resulted in wasted time for many users. If you use Excel for export, it opens Outlook, which is an expired program on my computer. So I am thinking more about getting a generic replacement for Microsoft Office when I next purchase a new computer. Perhaps not with all the bells and whistles of Microsoft Office, but easier to use. The folks at Microsoft don't seem to learn anything from the loss of market to Apple, because Apple is able to make the using their programs less frustrating.
Tim Rooke

2015-05-04 16:12:31


I used to be able to sent just one worksheet from a workbook as an attachment. Why is it considered an improvement that I now have to make a new workbook?? many more key strokes now. Were the programmers paid by number of key strokes they added to every process. They must have gotten rich. Who is going to pay for my extra time?

2015-02-11 07:54:55


Also, can if output as PDF from Excel 2013?

2015-02-11 07:48:20



I am not a coder, is it possible to pre-populate the email with an email address from a cell in the sheet and also define a default Subject line?

2015-01-20 15:00:44


When I try to execute Set oApp = CreateObject("Outlook.Application") I get a Run Time error -2147467229 (80004023) Automatic Error A Microsoft Installer error was encountered. Looked for answers. It works on other people's machines. Help

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

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.