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.
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.
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.
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
Was this article helpful?