Sending Personalized E-Mail via Outlook | Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

The example in this section demonstrates automation with Microsoft Outlook.

Figure 20-10 shows a worksheet that contains data used in the e-mail messages: name , e-mail address, and bonus amount. The SendMail procedure loops through the rows in the worksheet, retrieves the data, and creates an individualized message (stored in the Msg variable).

Figure 20-10: This information is used in the Outlook e-mail messages.

 Sub SendEmail()   'Uses early binding   'Requires a reference to the Outlook Object Library   Dim OutlookApp As Outlook.Application   Dim MItem As Outlook.MailItem   Dim cell As Range   Dim Subj As String   Dim EmailAddr As String   Dim Recipient As String   Dim Bonus As String   Dim Msg As String   'Create Outlook object   Set OutlookApp = New Outlook.Application   'Loop through the rows   For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)     If cell.Value Like "*@*" Then       'Get the data       Subj = "Your Annual Bonus"       Recipient = cell.Offset(0, -1).Value       EmailAddr = cell.Value       Bonus = Format(cell.Offset(0, 1).Value, "  Sub SendEmail() 'Uses early binding 'Requires a reference to the Outlook Object Library Dim OutlookApp As Outlook.Application Dim MItem As Outlook.MailItem Dim cell As Range Dim Subj As String Dim EmailAddr As String Dim Recipient As String Dim Bonus As String Dim Msg As String 'Create Outlook object Set OutlookApp = New Outlook.Application 'Loop through the rows For Each cell In Columns("B"). Cells .SpecialCells(xlCellTypeConstants) If cell.Value Like "*@*" Then 'Get the data Subj = "Your Annual Bonus" Recipient = cell.Offset(0, -1).Value EmailAddr = cell.Value Bonus = Format( cell .Offset(0, 1).Value, "$0,000.") 'Compose message Msg = "Dear " & Recipient & vbCrLf & vbCrLf Msg = Msg & "I am pleased to inform you that your annual bonus is " Msg = Msg & Bonus & vbCrLf & vbCrLf Msg = Msg & "William Rose" & vbCrLf Msg = Msg & "President" 'Create Mail Item and send it Set MItem = OutlookApp.CreateItem(olMailItem) With MItem .To = EmailAddr .Subject = Subj .Body = Msg .Send End With End If Next End Sub 
 ,000.")       'Compose message       Msg = "Dear " & Recipient & vbCrLf & vbCrLf       Msg = Msg & "I am pleased to inform you that your annual bonus is "       Msg = Msg & Bonus & vbCrLf & vbCrLf       Msg = Msg & "William Rose" & vbCrLf       Msg = Msg & "President"       'Create Mail Item and send it       Set MItem = OutlookApp.CreateItem(olMailItem)       With MItem         .To = EmailAddr         .Subject = Subj         .Body = Msg         .Send       End With     End If   Next End Sub

Figure 20-11 shows one of the e-mail messages displayed in Outlook.

Figure 20-11: An Outlook e-mail message created by Excel.

This example uses early binding, so it requires a reference to the Outlook Object Library. Notice that two objects are involved: an Outlook object and a MailItem object. The Outlook object is created with this statement:

 Set OutlookApp = New Outlook.Application

The MailItem object is created with this statement:

 Set MItem = OutlookApp.CreateItem(olMailItem)

The code sets the To , Subject , and Body properties and then uses the Send method to send each message.

Tip	To save the messages in your Draft folder (rather than send them), use the `Save` method instead of the `Send` method. This change is particularly useful while you're testing and debugging the code.

Unless you've changed your security settings, you'll probably see the dialog box shown in Figure 20-12 for each message that's sent. To eliminate this dialog box, activate Outlook and choose Tools Trust Center. In the Trust Center dialog box, click the Programmatic Access tab and choose the option labeled Never Warn Me about Suspicious Activity (Not Recommended). But do this at your own risk.

Figure 20-12: Using Excel to send e-mail via Outlook normally causes a warning message from Outlook.

CD-ROM

This example, named personalized email - outlook.xlsm , is available on the companion CD-ROM. You must have Microsoft Outlook installed. The CD also contains a slightly modified version that uses late binding: personalized email - outlook (late binding).xlsm .

Note	Subsequent sections in this chapter describe other ways of sending e-mail through Excel. See "Sending E-Mail Attachments from Excel" and "Using SendKeys."