Engram 9 VBA Scripts

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: