Excel Macro to Auto Fill an Outlook Template Based on Sheet Values.

[samc123]

I am trying to automate a template in outlook to automatically fill out certain fields based on data provided within the spreadsheet.

The spread sheet is called "Progress Report"
I want to grab the clients name, which is located in Column A
I want to insert the clients membership number which is located in Column G

The email template looks like this: https://imgur.com/a/jyuiM

The current macro I have only opens the template (which is still okay, would just like it to do a little more and unsure how). Here is the code:

Sub LoadEmail()


Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItemFromTemplate("W:\Client Letter Templates\Colonial Email Template\Colonial_Details.oft")
With otlNewMail
vTemplateBody = otlNewMail.HTMLBody
vTemplateSubject = otlNewMail.Subject
.Close 1
End With


Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(0)
With otlNewMail
BodyWithoutSignature = .HTMLBody
    .Display
    .HTMLBody = BodyWithoutSignature
.SentOnBehalfOfName = vFrom
.Bcc = vToList
.Subject = vTemplateSubject
.HTMLBody = vTemplateBody
End With


End Sub

Is there anyway I can set variables within the outlook template that can load from the excel spreadsheet?

 

[samc123]

**Unsure where edit button is**

Basically I would like my function to do the follow:

* check if the clients application is complete (column j contains the word "complete" when this happens)
* if its completed
Open the template (W:\Client Letter Templates\Email Template\Template.otf) and populate the following fields within the template:
1. Client's Name: Taken from column A
2. Client's Membership #: Taken from Column G
3. Auto populate client's email which would be listed in Column B
> if the client's email isn't listed then msgbox ("please enter clients email")
4. Send the email.
5. Once sent change the column "O" to "EMAILED"

A few questions/concerns I have with this though is:

1. How could I get this function to automatically run? or can't I?
2. How could I write the function so that it doesn't send an email to the already ~1,000 completed entries?
3. Is it easier not to include the email column as It would be a new column I am adding?

 

[samc123]

I've started using the

.HTMLbody = Replace

as shown:

.HTMLbody = Replace(.HTMLbody, "First Name Only", "A" & xlrow)
.HTMLbody = Replace(.HTMLbody, "123456", "G" & xlrow)

This replaces the data, but not with the correct output.

​Still unsure where the edit button is.