Creating Custom Report Worksheets - Automated Data Analysis Using Excel - page 244

6.7 AUTOMATICALLY EMAILING REPORTS

The reader can have the greatest report in the world, but it is meaningless if the right people cannot
obtain it. Reports can be sent by one of three ways utilizing Excel VBA. They are listed here in
order of increasing versatility.

1. A Single Worksheet can be mailed from within Excel.

2. An Entire Workbook can be mailed from within Excel.

3. Microsoft Outlook ® can be harnessed from within Excel to mail reports.

Sample Excel scripts will be provided to accomplish each of these tasks. Mailing a Worksheet
is the way to go in terms of raw simplicity. There is no file attachment, and the data is sent in a
space/tab delimited format. If a report consists of a single sheet within a Workbook, this is not a
bad option. In order to do this, a range of cells must be specified that are to be sent. The simplest
way to accomplish this is via the . UsedRange property. An active Worksheet within Excel can be
mailed by selecting A DA->Chapter 6 -> M ail Worksheet. (The address will need to be changed in
VBA code from “[e-mail]” to the desired destination.)
(Note: This subroutine will not work with Excel versions prior to 2002.)

Sub Mail_Worksheet()
'This will mail the Active Sheet as Text
'Select the range of cells used on the active Worksheet.
ActiveSheet.UsedRange.Select
' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = True
With ActiveSheet.MailEnvelope
.Introduction = "This is a sample Worksheet."
.item.To = "[e-mail]"
.item.Subject = "Email Subject Line Text Here"
.item.Send
End With
End Sub

If necessary, the entire Workbook can be mailed to anyone right from within Excel. The
Workbook is sent as an attachment within the message. If a Workbook is open within Excel, it can
be mailed by selecting A DA->Chapter 6 ->Mail Workbook . (The address will need to be changed
in VBA code from “[e-mail]” to the desired destination.)

Sub Mail_Workbook()
'This will mail the Active Workbook as an Attachment
ActiveWorkbook.SendMail "[e-mail]", "Email Subject
Line Text Here"
End Sub
The ultimate in flexibility is to use Microsoft Outlook to mail a report. This method gives
developers an additional degree of freedom. Not only can they mail an Excel Workbook but any
file that resides on the user's system can be mailed as well by simply specifying its full path.
In order to harness Outlook from Excel, a Reference to the Outlook Object Library (msoutl9.olb
for version 9.0) must exist within the current project as shown in Figure 6.11.