M A

asked on 2/17/2020

Automate email based on email address from excel

I have an excel file with Name email address and html file location.
I need to automate this process.
i.e. Send email based on the email address and html content in the email.
GTMarketing.xlsx

HTMLOutlookVBAMicrosoft ExcelMicrosoft Office

Last Comment

M A

8/22/2022 - Mon

Daniel Pineault

2/17/2020

You can either get into Outlook Automation or using CDO Mail. Although the example is for Access, both are demonstrated and would work in Excel as well, so look at https://www.devhut.net/2018/02/16/ms-access-how-to-send-an-email/

M A

2/17/2020

ASKER

Thanks for your reply.

I found similar to this but I cannot import this HTML and send email.
How to import/embed this HTML and send email from outlook.

Benjamin Lu

2/17/2020

Hi
in the mail body, you can compose the content with HTML format, like:
HTML = "<!DOCTYPE html>"
HTML = HTML & "<html>"
HTML = HTML & "<style>table, th, td {border: 1px solid black;border-collapse: collapse;}</style>"
HTML = HTML & "<body style=background-color:#F2F5A9>" ' silver background-color:coralstyle="background-color:#E6E6FA"
HTML = HTML & "<font size=1 face=Arial>"
HTML = HTML & "<table>"

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

M A

2/17/2020

ASKER

I already have HTML file ready. I want to embed that HTML file and send. Below is the excel format.

Daniel Pineault

2/17/2020

You don't have HTML, You have an HTML file, so you first need to read the file's content into a string variable and pass that, raw HTML, to your e-mail function and use it as the body of your message. See: https://www.devhut.net/2012/05/14/vba-read-file-into-memory/

Bill Prew

2/17/2020

You could use a technique similar to this prior recent question...

VBA - How to add dynamic range to an email body? | Experts Exchange

»bp

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

M A

2/17/2020

ASKER

Thanks Bill,
I found it useful. :))
Now to insert html file (i.e. \\192.168.0.100\shares\template1.html) to outlook new message and send.

Bill Prew

2/17/2020

What I typically do is use "placeholder" strings in the HTML template, that I can replace with data when I read in the template.

Are you comfortable reading in the template(s) to a string and doing a Replace(), or need help/example?

Also, if you are using a template file for the full body of the email, you might want to make the Excel rows a separate template file since those will repeat multiple times based on the data.

Just a few thoughts...

»bp

Bill Prew

2/17/2020

A couple of other questions that you might want to browse also, in case there is anything useful you want to borrow...

»bp

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy

M A

2/22/2020

ASKER

How to replace this with the HTML file content?
' Generate the HTML for this email body
emailBody = BodyMain(emailData.Name, emailTable)

Private Sub SendEmails(ws As Worksheet, emailQueue As Object)
    ' Local variables
    Dim emailTo As Variant
    Dim emailData As OwnerData
    Dim emailBody As String
    Dim emailTable As String
    Dim emailDetail As String
    Dim row As Variant
    Dim outlookApp As Object
    Dim total As Double
    
    ' Connect to Outlook
    Set outlookApp = CreateObject("Outlook.Application")
    
    ' Process each email that needs a reminder sent
    For Each emailTo In emailQueue.Keys
    
        ' Get the email address
        Set emailData = emailQueue(emailTo)

        emailTable = ""
        
        ' Add second reminders (if there are any)
        If emailData.ExpiredSecond <> "" Then
            emailDetail = ""
            total = 0
        
            ' Add details for each license
            For Each row In Split(emailData.ExpiredSecond, ",")
                ' Generate the HTML for this detail line and add to this table
                emailDetail = emailDetail & BodyDetail(ws, CLng(row))
                total = total + ws.Cells(row, colTotalPayment).value
            Next

            ' Generate the HTML for this table and add it to the tables for this email
            emailTable = emailTable & BodyTable(SecondReminderDays, Format(total, "Currency"), emailDetail)
        End If
        
        ' Add first reminders (if there are any)
        If emailData.ExpiredFirst <> "" Then
            emailDetail = ""
            total = 0
            
            ' Add details for each license
            For Each row In Split(emailData.ExpiredFirst, ",")
                ' Generate the HTML for this detail line and add to this table
                emailDetail = emailDetail & BodyDetail(ws, CLng(row))
                total = total + ws.Cells(row, colTotalPayment).value
            Next

            ' Generate the HTML for this table and add it to the tables for this email
            emailTable = emailTable & BodyTable(FirstReminderDays, Format(total, "Currency"), emailDetail)
        End If

        ' Generate the HTML for this email body
        emailBody = BodyMain(emailData.Name, emailTable)
        
        ' Create an email and send it (via Outlook)
        With outlookApp.CreateItem(0)
           .to = emailTo
           .Subject = "Software Maintenance license(s)"
           .HtmlBody = emailBody
           .Send
        End With
        
    Next
    
    ' Disconnect from Outlook
    Set outlookApp = Nothing

End Sub

Open in new window

I got the codes from this thread
https://www.experts-exchange.com/questions/29150989/Send-email-alerts-based-off-of-col-Value-with-message-in-email-body.html

Bill Prew

2/22/2020

Take a look at the attached Word document, I took some code from one of the prior questions I mentioned above, and highlighted in yellow the things I think you should be looking at / adding. I also highlighted in blue the section where I replace "place holder" test in the template with actual data values, you may or may not want to do something like that.

EE29172887.docx

»bp

M A

2/22/2020

ASKER

I managed to make it working by this as well. But how to take HTML file as body.

Sub BulkMail()
Application.ScreenUpdating = False

ThisWorkbook.Activate
Dim outApp As Outlook.Application
Dim outMail As Outlook.MailItem

Dim sendTo, subj, atchmnt, msg, ccTo, bccTo As String

Dim lstRow As Long

ThisWorkbook.Sheets("marketing").Activate
lstRow = Cells(Rows.Count, 3).End(xlUp).Row

Dim rng As Range
Set rng = Range("C2:C" & lstRow)

Set outApp = New Outlook.Application
On Error GoTo cleanup 'to handle any error during creation of object.

For Each cell In rng
    sendTo = Range(cell.Address).Offset(0, 0).Value2
    subj = Range(cell.Address).Offset(0, 1).Value2 & "-MS"
    msg = Range(cell.Address).Offset(0, 2).Value2
    atchmnt = Range(cell.Address).Offset(0, -1).Value2
    ccTo = Range(cell.Address).Offset(0, 3).Value2
    bccTo = Range(cell.Address).Offset(0, 4).Value2

    On Error Resume Next 'to hand any error during creation of below object
    Set outMail = outApp.CreateItem(0)
    
    With outMail
        .To = sendTo
        .cc = ccTo
        .BCC = bccTo
        .Body = msg
        .Subject = subj
        .Attachments.Add atchmnt
        .Send 'this send mail without any notification. If you want see mail
              
    End With
    On Error GoTo 0 'To clean any error captured earlier
    Set outMail = Nothing 'nullifying outmail object for next mail
 Next cell 'loop ends

cleanup: 'freeing all objects ceated
        Set outApp = Nothing
        Application.ScreenUpdating = True
Application.ScreenUpdating = True
End Sub