Sending EMail Attachments from Excel

As you probably know, Excel has commands to send worksheets or workbooks via e-mail. And, of course, you can use VBA to automate these types of tasks. The procedure below sends the active workbook (as an attachment) to [email protected] anydomain.com. The e-mail message has the subject My Workbook.

Sub SendWorkbook()

ActiveWorkbook.SendMail "[email protected]", "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 Sheetl from the active workbook.

Sub Sendasheet()

ActiveWorkbook.Worksheets("sheet1").Copy

ActiveWorkbook.SendMail "[email protected]", "My Sheet" ActiveWorkbook.Close False End Sub

In the preceding example, the file will have the default workbook name (for example, Book2.xls). If you would like to give the single-sheet workbook attachment a more meaningfule 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.xls. 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.xls" ActiveWorkbook.Worksheets("sheet1").Copy ActiveWorkbook.SaveAs Filename

ActiveWorkbook.SendMail "[email protected]", "My Sheet"

ActiveWorkbook.Close False Kill Filename End Sub

0 0

Post a comment