Wednesday, November 14, 2007

Let Excel e-mail your weekly reports

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:

1. Open the workbook containing the report you want to send.
2. Press [Alt][F11].
3. In the Project-VBA Project pane, double-click ThisWorkbook.
4. Go to Insert | Procedure.
5. Click in the Name text box and enter WeeklyReportEmail. Click OK.
6. At the prompt, enter the following code:
Dim DistList As Variant
DistList = Array("", "", "")
ActiveWorkbook.Sendmail Recipients: = DistList
7. Press [Alt]Q.
8. Press [Alt][F8].
9. Click WeeklyReportEmail in the Macro list.
10. Click the Options button.
11. 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.

No comments: