Sending E-Mail Attachments from Excel

As you probably know, Excel has the ability to send a workbook via e-mail as an attachment. And, of course, you can use VBA to automate these types of tasks . The procedure below uses the SendMail method to send the active workbook (as an attachment) to [e-mail]. The e-mail message has the subject My Workbook .

 Sub SendWorkbook()     ActiveWorkbook.SendMail "[e-mail]", "My Workbook" End Sub 

Note  

The SendMail method uses the default e-mail client.

If you would like to e-mail only a single sheet from a workbook, you need to copy the sheet to a new (temporary) workbook, send that workbook as an attachment, and then close the temporary file. Here's an example that sends Sheet1 from the active workbook.

 Sub Sendasheet()     ActiveWorkbook.Worksheets("sheet1").Copy     ActiveWorkbook.SendMail "[e-mail]", "My Sheet"     ActiveWorkbook.Close False End Sub 

In the preceding example, the file will have the default workbook name (for example, Book2.xlsx ). If you would like to give the single-sheet workbook attachment a more meaningful name, you need to save the temporary workbook and then delete it after it's sent. The following procedure saves Sheet1 to a file named my file.xlsx . After sending this temporary workbook as an e-mail attachment, the code uses VBA's Kill statement to delete the file.

 Sub SendOneSheet()     Dim Filename As String     Filename = "my file.xlsx"     ActiveWorkbook.Worksheets("sheet1").Copy     ActiveWorkbook.SaveAs Filename     ActiveWorkbook.SendMail "[e-mail]", "My Sheet"     ActiveWorkbook.Close False     Kill Filename End Sub 

Excel 2007 includes a new command to send a workbook as a PDF file (the command is Office Send Email as PDF Attachment).

Note  

Because of some legal issues with Adobe Systems Incorporated, the ability to save a workbook as a PDF file is not built into Office 2007. This feature is available only if you've downloaded and installed the PDF add-in from Microsoft's Web site.

Unfortunately, Excel does not provide a way to automate saving a workbook as a PDF file and sending it as an attachment. You can, however, automate part of the process. The SendSheetAsPDF procedure below saves the active sheet as a PDF file and then displays the compose message window from your default e-mail client (with the PDF file attached) so you can fill in the recipient's name and click Send:

 Sub SendSheetAsPDF()     CommandBars.ExecuteMso ("FileEmailAsPdfEmailAttachment") End Sub 

When Excel is lacking powers, it's time to call on Outlook. The procedure that follows saves the active workbook as a PDF file and automates Outlook to create an e-mail message with the PDF file as an attachment.

 Sub SendAsPDF() '   Uses early binding '   Requires a reference to the Outlook Object Library     Dim OutlookApp As Outlook.Application     Dim MItem As Object     Dim Recipient As String, Subj As String     Dim Msg As String, Fname As String '   Message details     Recipient = "[e-mail]"     Subj = "Sales figures"     Msg = "Hey boss, here's the PDF file you wanted."     Msg = Msg & vbNewLine & vbNewLine & "-Frank"     Fname = Application.DefaultFilePath & "\" & _       ActiveWorkbook.Name & ".pdf" '   Create the attachment     ActiveSheet.ExportAsFixedFormat _         Type:=xlTypePDF, _         Filename:=Fname '   Create Outlook object     Set OutlookApp = New Outlook.Application '   Create Mail Item and send it     Set MItem = OutlookApp.CreateItem(olMailItem)     With MItem       .To = Recipient       .Subject = Subj       .Body = Msg       .Attachments.Add Fname       .Save 'to Drafts folder       '.Send     End With     Set OutlookApp = Nothing '   Delete the file     Kill Fname End Sub 

CD  

This example, named  send pdf via outlook.xlsm , is available on the companion CD-ROM.