If you have to send the same worksheet to a number of people every week, it probably takes you quite awhile to prepare the e-mail. If you have to do this for a number of reports, it can take even longer. Follow these steps to have Excel e-mail the reports for you:
- Open the workbook containing the report you want to send.
- Press [Alt][F11].
- In the Project-VBA Project pane, double-click ThisWorkbook.
- Go to Insert | Procedure.
- Click in the Name text box and enter WeeklyReportEmail. Click OK.
- At the prompt, enter the following code:
Dim DistList As Variant
DistList = Array("firstname.lastname@example.org", "email@example.com", "firstname.lastname@example.org")ActiveWorkbook.Sendmail Recipients: = DistList
- Press [Alt]Q.
- Press [Alt][F8].
- Click WeeklyReportEmail in the Macro list.
- Click the Options button.
- In the Ctrl+ box, enter m. Click OK.
Now when you need to send a report to everyone on your distribution list just open the workbook and press [Ctrl]M.
Check out the Microsoft Excel archive, and catch up on other Excel tips.
Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.