Trying to create personalized emails with multiple attachments

0
votes

Updated per suggestions from Jeeped:

I am looking for a method of creating a set of emails fitting the following parameters:

-each email will be personalized to the recipient and based off a template letter set by my supervisor.

-There will be a greeting line with their name and title, along with the names of the departments they oversee.

-each email will have a set of documents specific to that recipient.

-they should be saved to file for final inspection before they are sent.

-column 5 that is not referenced in the code below is the column containing the department name.

The closest I have come is the following code:

Sub send_template_w/attachments()

On Error Resume Next

Dim o As Outlook.Application

Set o = New Outlook.Application

Dim omail As Outlook.Mailitem

Set omail =.Createitem(olMailitem)

Dim I As Long

For i=2 To Range(“a100”).End(xlUp).Row

With omail

.Body = “Dear “ & Cells(i,1).Value

.To = Cells(i,2).Value

.CC = Cells(i,3).Value

.Subject = Cells(i,4).Values

.Attachments.Add Cells(i,6)

.Attachments.Add Cells(i,7)

.SaveAs Environ("HOMEPATH") &; "\My Documents\" & Cells(i,2).Value

End With

Next

End Sub

So far this code will generate and save an email but what I want to do is use a present email template for these emails--either by adding the greeting at the beginning and department name into the body of the the email to be sent out. Can this be done through a word or Outlook document and if so, how?

1
excelvbaoutlookms-word
Yes there is a way. There is some decent CDO mail code here. Get started and come back if you have a specific coding question. – user4039065
@Jeeped I have updated my request per your suggestions. Can you offer further guidance on my inquiry? – Wesley Young

1 Answers

0
votes

Create a model of the mail. "Save As" to an .oft file. For example MyTemplate.oft

Instead of

Set omail =.Createitem(olMailitem)

there is 

Set omail = o.CreateItemFromTemplate("C:\MyTemplate.oft").

To add the entries from the Excel sheet you could include unique placeholders in the body of the template then Replace with Excel values.