Automate sending email based on excel data

[shohakin]

Hi Everyone,

I am a novice whom just started on my VBA journey with no programming background.

I am trying to create a macro to send out emails in Outlook automatically with an existing email template.

I have created 2 spreadsheets in the workbook and named the 1st spreadsheet as config file (to store the directory of the email template) and 2nd spreadsheet as data file (to store the list of email addresses and email subjects).

The steps are as follows:
1) Excel open the email template which I store in a local drive (1st spreadsheet, config file)
2) Excel finds the 1st email address in column B of the spreadsheet (2nd spreadsheet, data file)
3) Inserts address into "To" field
4) Insert email subject based on column C and D of the spreadsheet (2nd spreadsheet, data file)
5) send out the email
6) Update in column E of the spreadsheet that the email had been sent (2nd spreadsheet, data file)
7) Repeats until last email entry of Column B

So far, I have created 2 spreadsheets in the workbook and named the 1st spreadsheet as config file (to store the directory of the email template) and 2nd spreadsheet as data (to store the list of email addresses and email subjects). I have the 1st part of the script done to open the email template.


Sub OpenOutlookTemplate()

Dim myoutapp As Object
Dim dirFolder As String
Dim myitem As Object

Sheet1.Select
dirFolder = Range("A2").Value
Set myoutapp = CreateObject("Outlook.Application")
Set myitem = myoutapp.CreateItemFromTemplate(dirFolder)

myitem.Display
End Sub

Hope I can get some expertise help to complete this script.

Thank you!