Programming Excel with VBA and .NET

Work with Mail Items

In the preceding SendActiveSheet example, the Item property returns a MailItem object. That object is part of the Microsoft Outlook object library —not Excel’s. The MailItem object is very useful in Excel, since it allows you to attach files and control all aspects of the message.

To use the MailItem object:

1. In the Visual Basic Editor, choose Tools → References. Visual Basic displays the References dialog box.

2. Select the Microsoft Outlook 11.0 Object Library and click OK.

3. Declare a variable using the MailItem type.

4. Get a reference to the MailItem object.

Figure 21-2. Composing an email in Excel

The following code creates a mail item and attaches the current workbook:

 Sub SendAsMailItem( ) ' Requires reference to Microsoft Outlook Dim ws As Worksheet, env As MsoEnvelope, mi As MailItem ' Get the active worksheet. Set ws = ActiveSheet ' Save the workbook before mailing as attachment. ws.Parent.Save ' Show email header. ws.Parent.EnvelopeVisible = True ' Get the MsoEnvelope object Set env = ws.MailEnvelope ' Set the email header fields. env.Introduction = "Please revew attached file." ' Get the MailItem object. Set mi = env.Item ' Clear the MailItem properties. ClearMessage mi ' Set MailItem properties. mi.Importance = olImportanceHigh mi.To = "[e-mail]" mi.CC = "[e-mail]" mi.Subject = "Subject text." ' Attach this workbook. mi.Attachments.Add ...