Sending Personalized Email Using Outlook

The example in this section demonstrates automation with Microsoft Outlook. The code creates personalized e-mail messages by using data stored in an Excel worksheet.

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

Figure 23-6:

This information is used in the Outlook Express e-mail messages.

peisunalized email oullook.xls

. mm

A'

B C

D

1

Name

Email

Bairns

John Jones

[email protected]

$2,000

•3

Bob Smith

[email protected]

$3,500

4

Fred Simpson

[email protected]

SI 250

5

6-

7

fl

9

*

:* 1

► wK Sheet li

«

!>:

Dim OutlookApp As Object Dim MItem As Object 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 = CreateObject("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 "

Msg = Msg & "William Rose" & vbCrLf

Msg = Msg & "President"

'Create Mail Item and send it

Set MItem = OutlookApp.CreateItem(0)

With MItem

.To = EmailAddr .Subject = Subj .Body = Msg .Display End With End If Next End Sub

This example uses the Display method, which simply displays the email messages. To actually send the messages, use the Send method instead.

Notice that two objects are involved: Outlook and MailItem. The Outlook object is created with this statement:

Set OutlookApp = CreateObject("Outlook.Application")

The MailItem object is created with this statement:

0 0

Post a comment