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.

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.