Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

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 joeblow@zx-prrtgfw.com. The e-mail message has the subject My Workbook .

Sub SendWorkbook() ActiveWorkbook.SendMail "joeblow@zx-prrtgfw.com", "My Workbook" End Sub

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 "joeblow@zx-prrtgfw.com", "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 "joeblow@zx-prrtgfw.com", "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).

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 = "myboss@xrediyh.com" 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