Trying to create personalized emails with multiple attachments

Question

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?

Yes there is a way. There is some decent CDO mail code [here](https://stackoverflow.com/questions/43927471/send-up-arrow-%e2%86%91-character-to-iphone-with-sms-using-vba-and-a-cdo-mail-object). Get started and come back if you have a **specific** coding question. — , Feb 18 '18 at 07:36

score 0 · Answer 1 · answered May 19 '18 at 20:40

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.

Trying to create personalized emails with multiple attachments

1 Answers1