O'Reilly logo

Programming Excel with VBA and .NET by Steve Saunders, Jeff Webb

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

Composing an email in Excel

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 = "someone@microsoft.com" mi.CC = "someoneelse@yourcompany.com" mi.Subject = "Subject text." ' Attach this workbook. mi.Attachments.Add ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required