Working with Excel Workbooks and Worksheets in E-Mail
Summary: Microsoft Excel MVP Ron de Bruin provides a number of samples and a handy add-in that enhances your experience when working with e-mail from Excel. From sending individual worksheets in a workbook to sending e-mail to multiple recipients, the code samples and add-in should become a part of your reference library. (12 printed pages)
Ron de Bruin, Microsoft Excel MVP
Frank C. Rice, Microsoft Corporation
Published: January 2007
Updated: October 2010
Applies to: Microsoft Office Excel 2007, Microsoft Office Excel 2003, Microsoft Excel 2002, Microsoft Excel 2000, Microsoft Excel 97, Microsoft Office Outlook 2007, Microsoft Office Outlook 2003, Microsoft Office Outlook 2002, Microsoft Office Outlook 2000, Microsoft Office Outlook Express, Microsoft Windows Mail
Download the SendMail tool add-in and user guide.
Note |
---|
This article is an updated version of the Excel 2003 article, Different Ways to Take Advantage of the E-mail Features of Excel. |
Contents
Background
Sending E-Mail in Excel
Before Sending E-Mail
Sending the Active Workbook by E-Mail
Sending a Single Worksheet by E-Mail
Sending an Array of Worksheets by E-Mail
Sending a Range or Selection by E-Mail
Sending Specific Worksheets by E-Mail
Tips for Modifying the Examples
The SendMail Tool
Conclusion
About the Authors
Background
This article features code samples and an add-in tool that you can use to perform various e-mail functions from Microsoft Office Excel. Ron de Bruin, an Excel Most Valuable Professional (MVP) and a frequent contributor to the newsgroups, provided the samples and add-in. You can find many Microsoft Outlook code samples and sample workbooks on Ron's Excel page.
This article covers the following e-mail programs:
Microsoft Office Outlook (included as part of the Microsoft Office system)
Microsoft Outlook Express (included in Microsoft Windows XP)
Microsoft Windows Mail (included in Microsoft Windows Vista; replaces Outlook Express)
Caution |
---|
If you set a Windows Live Mail version prior to Windows Live Mail 2011 as your default mail program, it is possible that if you attempt to mail from Excel, you may get a general mail failure because versions prior to Windows Live Mail 2011 are not fully MAPI-qualified unlike Windows Mail or Outlook. This was corrected by Microsoft in Windows Live mail 2011. |
Microsoft Office Outlook is a part of the Microsoft Office System. Outlook Express and Windows Mail are a part of the Windows operating system.
Note |
---|
The code in this article is compatible with these three programs and uses the SendMail method in each program. |
Of these programs, the object model for Office Outlook gives you many more options when you also have an account in Outlook. For example, you can:
Programmatically insert text in the body of an e-mail.
Add contacts into the CC or BCC fields.
Add attachments to the e-mail.
You should be aware of the two main security features for Outlook 2002, Outlook 2003, and Outlook 2007. Note that these features are implemented through a security patch for Outlook 2000:
Blocking of a customizable list of file attachments considered unsafe because they can be used to propagate viruses.
Pop-up confirmation dialogs that occur whenever a program accesses address-related properties or attempts to send a message.
For more information about Outlook 2007, see this Code Security Changes in Outlook 2007.
These constraints can affect the way you interact with Outlook in the procedures that appear in this article.
Note |
---|
If you use Outlook Express or Windows Mail, you can turn off the confirmation dialogs. On the Tools menu, click Options, and then click the Security tab. An alternative is to use the samples that use CDO for Microsoft Windows 2000, to download, see Sending Mail from Excel with CDO. |
Sending E-Mail in Excel
There are many ways to send Excel data through e-mail. One way to send Excel data in e-mail is to use the Microsoft Office Word Mail Merge Wizard. This feature is only available in Microsoft Excel 2000, Microsoft Excel 2002, Microsoft Office Excel 2003, and Microsoft Office Excel 2007. In Microsoft Excel 2000, this feature is called the Mail Merge Helper. The Word Mail Merge Wizard simplifies many e-mail related tasks, including creating batch mailings, sending e-mail messages, creating postal mailing labels, labeling envelopes for postal mail, and creating directory lists.
Another way to use the e-mail features of Excel is with the code samples discussed in this article. You can use the samples as a starting point to help you understand how you can send the various objects and data in your own worksheets and workbooks as e-mail. Several samples also include alternate ways of modifying the code to send just the piece of information you want.
Yet another way to send mail is to use the SendMail add-in tool created by Ron de Bruin. To download, see Mail Add-ins for Excel. The SendMail tool gives you the ability to send all or part of a workbook either in working format or with just the values of the current data and formulas. After you install SendMail, to access these features, in previous versions of Excel, click Tools, and then click SendMail. In Excel 2007, click the Microsoft Office Button and then click Send.
Before Sending E-Mail
Before you use the e-mail features of Excel, either by feeding data to the Mail Merge Wizard or through the Microsoft Visual Basic for Applications (VBA) code sample in this article, it is a good idea to ensure your data is structured so that there are no surprises. The following criteria are applicable for using the wizard but also improve predictability when using the code samples:
Add column headers above each column in the first row such as Title, First Name, Last Name, Address1, and so forth.
Ensure that individual pieces of information you want to work with are in separate fields. For example, separating the recipient's name into separate first and last name fields allows you to use just the parts you want for a specific task such as using the last name in a salutation and then combining the first and last fields in the address block in a letter.
Ensure that each field name is unique to avoid ambiguity.
Ensure each row of data refers to a single entity. For example, each row refers to one recipient.
Avoid blank rows in your data.
Sending the Active Workbook by E-Mail
The following subroutine sends the active workbook in an e-mail message:
Sub Mail_Workbook()
' Works with Excel 97-2007.
Dim wb As Workbook
Set wb = ActiveWorkbook
' Check to see if this is Excel 2007 and is not a macro-enabled file.
If Val(Application.Version) >= 12 Then
' The code 51 represents the enumeration for a macro-free Excel 2007 Workbook (.xlsx).
If wb.FileFormat = 51 And wb.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file that will be removed if you try to send this file." & vbNewLine & _
"Save the file first as xlsm and then try the macro again.", vbInformation
Exit Sub
End If
End If
On Error Resume Next
For I = 1 To 3
wb.SendMail "ron@debruin.nl", _
"This is the Subject line"
If Err.Number = 0 Then Exit For
Next I
On Error GoTo 0
End Sub
Note |
---|
To send a workbook other than the active workbook, change the assignment to the wb variable. |
Sending a Single Worksheet by E-Mail
The following example illustrates how to send a single worksheet in e-mail by:
Creating a workbook with just the active worksheet.
Saving the workbook before sending it with a date/time stamp.
Sending the active workbook.
Deleting the file from your hard disk after you send it.
Sub Mail_ActiveSheet()
' Works in Excel 97 through Excel 2007.
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set Sourcewb = ActiveWorkbook
' Using ActiveSheet.Copy creates a new workbook with
' the sheet and the file format is the same as the
' original workbook.
' Copy the worksheet to a new workbook.
ActiveSheet.Copy
Set Destwb = ActiveWorkbook
' Determine the Excel version and file extension/format.
With Destwb
If Val(Application.Version) < 12 Then
' You are using Excel 97-2003.
FileExtStr = ".xls": FileFormatNum = -4143
Else
' You are using Excel 2007.
' When you use ActiveSheet.Copy to create a workbook,
' you are prompted with a security dialog. If you click No
' in the dialog, then the name of Sourcewb is the same
' as Destwb and you exit the subroutine. You only see this
' dialog when you attempt to copy a worksheet from an .xlsm file with macros disabled.
If Sourcewb.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your answer is No in the security dialog."
Exit Sub
Else
Select Case Sourcewb.FileFormat
' Code 51 represents the enumeration for a macro-free
' Excel 2007 Workbook (.xlsx).
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
' Code 52 represents the enumeration for a
' macro-enabled Excel 2007 Workbook (.xlsm).
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
' Code 56 represents the enumeration for a
' a legacy Excel 97-2003 Workbook (.xls).
Case 56: FileExtStr = ".xls": FileFormatNum = 56
' Code 50 represents the enumeration for a
' binary Excel 2007 Workbook (.xlsb).
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End If
End With
' Change all cells in the worksheet to values, if desired.
'' With Destwb.Sheets(1).UsedRange
'' .Cells.Copy
'' .Cells.PasteSpecial xlPasteValues
'' .Cells(1).Select
'' End With
''Application.CutCopyMode = False
'Save the new workbook and then mail it.
TempFilePath = Environ$("temp") & "\"
TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
On Error Resume Next
For I = 1 To 3
.SendMail "someone@somewhere.com", _
"This is the Subject line"
If Err.Number = 0 Then Exit For
Next I
On Error GoTo 0
.Close SaveChanges:=False
End With
' Delete the file you just sent.
Kill TempFilePath & TempFileName & FileExtStr
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Note |
---|
To send a worksheet other than the active worksheet, change ActiveSheet.Copy to copy the worksheet you wish to send. For instance, Sheets("Sheet 5").Copy. |
Looking at the macro, first, we point the workbook variable Sourcewb to the active workbook, and then make a copy of the worksheet, which makes a new workbook and copies the worksheet to that workbook. Next, we make the new workbook the active workbook. This is the workbook that you send in the e-mail message.
Set Sourcewb = ActiveWorkbook
' Copy the worksheet to a new workbook.
ActiveSheet.Copy
Set Destwb = ActiveWorkbook
Then we test to see if the application version is less than "12". If so, we use the file extension ".xls" and the file format enumeration of "-4143." This is the Excel binary workbook format in Excel 97 through Excel 2003.
If you run this code in Excel 2007 it identifies the file format of the parent workbook and saves the new file in that format. If the parent workbook is a macro-enabled (.xlsm) file and there is no code in the new workbook, the new file is saved as a macro-free file (.xlsx). This way, the recipient of the e-mail knows that this is a macro-free file. However, if the parent workbook is not .xlsx, .xlsm, or .xls then it is saved as an Excel binary (.xlsb) file.
The principle file format enumerations in Excel 2007 are:
51 = xlOpenXMLWorkbook (macro-free Excel 2007 workbook, .xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (Excel 2007 workbook with or without macros, .xlsm)
50 = xlExcel12 (Excel 2007 binary formatted workbook, with or without macros, .xlsb)
56 = xlExcel8 (Excel 97 through Excel 2003 formatted files used in Excel 2007, .xls)
If you always want to save in a certain format, you can replace the Select Case block in the previous macro with one of the lines from this list:
FileExtStr = ".xlsb": FileFormatNum = 50
FileExtStr = ".xlsx": FileFormatNum = 51
FileExtStr = ".xlsm": FileFormatNum = 52
FileExtStr = ".xls": FileFormatNum = 56
In addition, you may want to save the file in an alternative format such as Comma Separated Values file (.csv), a text file (.txt), or printer file (.prn). In that case, replace the Select Case block with one of the following lines:
FileExtStr = ".csv": FileFormatNum = 6
FileExtStr = ".txt": FileFormatNum = -4158
FileExtStr = ".prn": FileFormatNum = 36
Getting back to the code, we said that Destwb is the one worksheet workbook that we created. We copy all cells in the first worksheet by using the UsedRange method. The worksheet index is used because we do not know the name of the worksheet. Then we use the PasteSpecial method to paste just the values (and not any formulas) and select just the first cell of the used range because when you use PasteSpecial, the entire range is selected. Next, the CutCopyMode method clears the clipboard.
'' With Destwb.Sheets(1).UsedRange
'' .Cells.Copy
'' .Cells.PasteSpecial xlPasteValues
'' .Cells(1).Select
'' End With
''Application.CutCopyMode = False
Sending an Array of Worksheets by E-Mail
You can use the following sample code to send multiple worksheets in e-mail. It does this by:
Creating a workbook with just the worksheets in the array.
Saving the workbook before sending it with a date/time stamp.
Sending the active workbook.
Deleting the file from your hard disk after you send it.
Sub Mail_Sheets_Array()
' Works in Excel 97 through Excel 2007.
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim sh As Worksheet
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set Sourcewb = ActiveWorkbook
' Copy the worksheets to a new workbook.
Sourcewb.Sheets(Array("Sheet1", "Sheet3")).Copy
Set Destwb = ActiveWorkbook
' Determine the Excel version, file extension, and format.
With Destwb
If Val(Application.Version) < 12 Then
' You are using Excel 97 through Excel 2003.
FileExtStr = ".xls": FileFormatNum = -4143
Else
' You are using Excel 2007.
' When you use ActiveSheet.Copy to create a new workbook,
' you are prompted with a security dialog. If you click No
' in the dialog, then the name of Sourcewb is the same
... ' as Destwb and you exit the subroutine. You only see this
' dialog when you attempt to copy a worksheet from an .xlsm
' file with macros disabled.
If Sourcewb.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your answer is No in the security dialog."
Exit Sub
Else
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End If
End With
' Change all cells in the worksheets to values if desired.
''For Each sh In Destwb.Worksheets
'' sh.Select
'' With sh.UsedRange
'' .Cells.Copy
'' .Cells.PasteSpecial xlPasteValues
'' .Cells(1).Select
'' End With
'' Application.CutCopyMode = False
'' Destwb.Worksheets(1).Select
''Next sh
' Save the new workbook and then mail it.
TempFilePath = Environ$("temp") & "\"
TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
On Error Resume Next
For I = 1 To 3
.SendMail "someone@somewhere.com", _
"This is the Subject line"
If Err.Number = 0 Then Exit For
Next I
On Error GoTo 0
.Close SaveChanges:=False
End With
' Delete the file you just sent.
Kill TempFilePath & TempFileName & FileExtStr
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
You can also use this line if you want to send all of the selected worksheets:
ActiveWindow.SelectedSheets.Copy
This code is similar to the procedure to send a single worksheet, with the exception of the statement that copies the array of worksheets:
Sourcewb.Sheets(Array("Sheet1", "Sheet3")).Copy
The commented code to change all cells in the worksheets to values is also different in this macro.
Sending a Range or Selection by E-Mail
The following subroutine sends a newly created workbook with just the visible cells in the selection. It does this by doing the following:
Creates a workbook with just the visible cells in the range ("A1:K50") from the source workbook.
Adds the cells as values using the Paste Special command into the workbook you send.
Saves the workbook with a date/time stamp before sending it.
Sends the active workbook.
Deletes the file from your hard disk after you send it.
Because it sends only the visible cells, the subroutine also works if you want to send a range with hidden rows or columns in it. The standard Copy and Paste commands make the hidden rows and columns visible.
Sub Mail_Range()
' Works in Excel 2000 through Excel 2007.
Dim Source As Range
Dim Destwb As Workbook
Dim wb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim FileFormatNum As Long
Set Source = Nothing
On Error Resume Next
Set Source = Range("A1:K50").SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Source Is Nothing Then
MsgBox "The source is not a range or the worksheet is protected. Please correct the problem and try again.", vbOKOnly
Exit Sub
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set wb = ActiveWorkbook
Set Destwb = Workbooks.Add(xlWBATWorksheet)
Source.Copy
With Destwb.Sheets(1)
' The number 8 pastes the column width. Because of
' of a bug in Excel 2000, you must use the number
' instead of “xlPasteColumnWidths”.
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial Paste:=xlPasteValues
.Cells(1).PasteSpecial Paste:=xlPasteFormats
.Cells(1).Select
Application.CutCopyMode = False
End With
TempFilePath = Environ$("temp") & "\"
TempFileName = "Selection of " & wb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
If Val(Application.Version) < 12 Then
' You are using Excel 2000 through Excel 2003.
FileExtStr = ".xls": FileFormatNum = -4143
Else
' You are using Excel 2007.
FileExtStr = ".xlsx": FileFormatNum = 51
End If
With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
On Error Resume Next
For I = 1 To 3
.SendMail "ron@debruin.nl", _
"This is the Subject line"
If Err.Number = 0 Then Exit For
Next I
On Error GoTo 0
.Close SaveChanges:=False
End With
' Delete the file you just sent.
Kill TempFilePath & TempFileName & FileExtStr
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
If you run the code in the macro in Excel 2000 through Excel 2003 (versions less than 12), the code saves the file in the legacy Excel binary workbook format:
FileExtStr = ".xls": FileFormatNum = -4143
And if you are use Excel 2007 it saves as an .xlsx file:
FileExtStr = ".xlsx": FileFormatNum = 51
Note For code samples that send the selection, see Example Code for Sending Mail from Excel.
Sending Specific Worksheets by E-Mail
The following procedure illustrates how to send any worksheet with an address in cell A1 by e-mail. This way you can send each worksheet to a different person.
Note |
---|
Use this macro in a module in the workbook with the worksheets you want to send, not in your Personal Macro Workbook (personal.xls(b)). |
Sub Mail_Every_Worksheet()
' Works in Excel 97 through Excel 2007.
Dim sh As Worksheet
Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String
TempFilePath = Environ$("temp") & "\"
If Val(Application.Version) < 12 Then
' You are using Excel 97 through Excel 2003.
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You are using Excel 2007
FileExtStr = ".xlsm": FileFormatNum = 52
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
For Each sh In ThisWorkbook.Worksheets
If sh.Range("A1").Value Like "?*@?*.?*" Then
sh.Copy
Set wb = ActiveWorkbook
TempFileName = "Sheet " & sh.Name & " of " _
& ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
On Error Resume Next
For I = 1 To 3
.SendMail sh.Range("A1").Value, _
"This is the Subject line"
If Err.Number = 0 Then Exit For
Next I
On Error GoTo 0
.Close SaveChanges:=False
End With
Kill TempFilePath & TempFileName & FileExtStr
End If
Next sh
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
The procedure works by cycling through each worksheet in the workbook and checking cell A1 for the @ character. If found, the code makes a copy of the worksheet and then sends the copy to the address specified in cell A1. Finally, the code deletes the file copy from the hard disk.
Tips for Modifying the Examples
The preceding macros are examples that you can use to send e-mail from Excel. You can modify these procedures to suit your own needs. For example:
Changing the Address Line
Use the contents of a cell for the To line:
.SendMail Sheets("mysheet").Range("A1").Value, "This is the Subject line"
You can also use the contents of a cell for the subject:
.SendMail Sheets("mysheet").Range("A1").Value, Sheets("mysheet").Range("B1").Value
To send e-mail to more than one person, you can use an array of addresses as follows:
.SendMail Array("someone@example.com", "someone@example.com"), "Subject_line"
To send e-mail to all addresses within a range, use an array as follows:
Dim MyArr As Variant
MyArr = Sheets("mysheet").Range("C1:C10")
.SendMail MyArr, _
"Subject_line"
You can also leave the address blank, and then manually insert one, either by choosing an address in the address book or by typing one yourself. The following statement leaves the address blank but inserts a subject line in the e-mail message:
.SendMail "", "Subject_line"
Changing the Save Line
You can change the TempFileName string in the preceding code sample to change the SaveAs line. You can also save the new workbook with the name of the source workbook and a date/time stamp:
"Part of " & Sourcewb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
You can also save the file with an arbitrary string with:
"New project" & " " & Format(Now, "dd-mmm-yy h-mm-ss")
Or with the value from cell A1 of the worksheet we send:
Destwb.Sheets(1).Range("A1").Value & " " & Format(Now, "dd-mmm-yy h-mm-ss")
Or with the value from a cell in another worksheet from the original workbook:
Sourcewb.Sheets("YourSheet").Range("A1").Value & " " & Format(Now, "dd-mmm-yy h-mm-ss")
Important |
---|
Use error checking to verify that a file with that name does not already exist or is not already open. In the examples in this article, the file name includes the date and time so that the chance of using a file name that already exists is very small. |
The SendMail Tool
To download the SendMail tool, see Mail Add-ins for Excel.
This tool is an add-in for sending customized Excel workbooks and worksheets by e-mail. In addition, the SendMail tool allows significant customization of the information you send. For example, after you have a workbook open in Excel, you have the ability to send all or part of the workbook, either with the formatting or with just the values of the current data and formulas.
To use the SendMail tool:
Download and extract it to a local directory.
Copy SendMail.xla to the following directory:
local_drive:\Program Files\Microsoft Office\OfficeNumber\Library
Note
Depending on the version of Excel, the OfficeNumber directory may be named Office or may include a version number. For example: local_drive:\Program Files\Microsoft Office\Office\Library -OR- local_drive:\Program Files\Microsoft Office\Office11\Library
After you install the add-in, to access it:
Start Excel and open a workbook.
In Excel 97, Excel 2000, Excel 2003, and Excel 2003, click Tools, click Add-Ins, select SendMail, and then OK.
In Excel 2007, click the Microsoft Office button, click Excel Options, click the Add-Ins tab. In the Manage drop-down, click Excel Add-ins, and click Go. Verify SendMail is checked in this list and then click OK.
Note The SendMail tool was tested in Microsoft Office Outlook, Outlook Express, and Window Mail. It will not work with other e-mail clients.
For additional instructions on using the SendMail tool, see the user guide included in the download.
Conclusion
In this article, we looked at several code samples that you can use to make sending e-mail from Excel much easier. The SendMail add-in can assist you in sending customized Excel workbooks and worksheets by e-mail. Exploring and implementing these tools in your own applications can help make your job as a developer easier and make your solutions more versatile.
About the Authors
Ron de Bruin is an Excel Most Valuable Professional (MVP) and a frequent contributor to the newsgroups. For more information, see Ron's Excel page.
Frank Rice is a Microsoft employee and frequent contributor to the Microsoft Office Developer Center.