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 |
|
Bairns | ||
|
John Jones |
$2,000 | ||||
|
•3 |
Bob Smith |
$3,500 | |||
|
4 |
Fred Simpson |
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: | |
Post a comment