Programming Excel with VBA and .NET

Send Mail

Table 21-1 lists the different ways to send mail within Excel.

Table 21-1. Sending mail from Excel

To	Use	Notes
Compose a text email message	The FollowHyperLink method with the mailto: protocol	Doesn’t support attachments.
Compose an email with an attached workbook	The Dialogs method to display the email dialog box	User must fill in addresses and subject on the message.
Send a workbook	The SendMail method	Doesn’t display message before send; shows security warning.
Send a worksheet or chart	The MailEnvelope property	Unlike SendMail, this allows access to CC and BCC lines; avoids security warning. (Requires Outlook.)
Collect review comments	The SendForReview method	Allows you to link to a shared workbook for collecting comments.
Route for approval	The RoutingSlip object and Route method	Routes to addresses in sequence.

I’ve used FollowHyperlink technique a few times already in this book. In case you missed it, here’s short sample:

    Sub SendTextMail( )
        ThisWorkbook.FollowHyperlink "mailto:[e-mail]" & _
          "?Subject=Test message.&Body=The message goes here..."
    End Sub

The mailto: protocol starts the user’s default email client and creates a new message. It’s up to the user to send the message, so there are no real security hurdles to this approach. You can’t attach files using mailto: however. To create a quick email with the current workbook attached, use the Dialogs method as shown here:

 Sub SendAsAttachment( ) Application.Dialogs(xlDialogSendMail).Show ...