How do I create an email macro to auto fill the email?

J

Justin

I am trying to take a table of values and have a macro run so it will fill
out an email for each person with the corrisponding values.
 
J

JP

Here is something that should get you started. Note that I didn't test
this code - you would have to add validation.

I assumed that the data is in A1:C100 -- column A has the title,
column B has the name, column C has the email address.

First, create a reference to the Outlook library by clicking Tools|
References in the VBE and selecting the appropriate object library.
For Outlook 2003 it would be "Microsoft Outlook 11.0 Object Library".

Sub CreateEmailsFromTable()

Dim Outlook As Outlook.Application
Dim OutlookMsg As Outlook.MailItem
Dim OutlookRecip As Outlook.Recipient
Dim cell as Excel.Range

On Error Resume Next
Set Outlook = GetObject("outlook.application")
If Err.Number <> 0 Then
Set Outlook = CreateObject("outlook.application")
End If
On Error GoTo 0

For each cell in Range("A1:A100")
Set OutlookMsg = Outlook.CreateItem(olMailItem)
With OutlookMsg
.Subject = cell.value
.To = cell.offset(0,2).value
.Body = "Dear" & cell.offset(0,1).value & ", here is the
email you requested."
.Display 'or .Send, once you are comfortable with the code
End With
Next cell


Set OutlookMsg = Nothing
Set Outlook = Nothing
End Sub


HTH,
JP
 
Top