We help IT Professionals succeed at work.

Automate email based on email address from excel

MAS
MAS asked
on
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
Comment
Watch Question

View Solutions Only

Daniel PineaultPresident / Owner CARDA Consultants Inc.
BRONZE EXPERT
Distinguished Expert 2018

Commented:

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/ 

MASEE Solution Guide - Technical Dept Head
SILVER EXPERT
Most Valuable Expert 2017

Author

Commented:
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 LuSAP/ERP Data Processing
BRONZE EXPERT

Commented:
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>"
MASEE Solution Guide - Technical Dept Head
SILVER EXPERT
Most Valuable Expert 2017

Author

Commented:
I already have HTML file ready. I want to  embed that HTML file and send. Below is the excel format.
Capture62.JPG
Daniel PineaultPresident / Owner CARDA Consultants Inc.
BRONZE EXPERT
Distinguished Expert 2018

Commented:

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 PrewTest your restores, not your backups...
SILVER EXPERT
Expert of the Year 2019
Top Expert 2016

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

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

»bp
MASEE Solution Guide - Technical Dept Head
SILVER EXPERT
Most Valuable Expert 2017

Author

Commented:
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 PrewTest your restores, not your backups...
SILVER EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
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 PrewTest your restores, not your backups...
SILVER EXPERT
Expert of the Year 2019
Top Expert 2016

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


»bp
MASEE Solution Guide - Technical Dept Head
SILVER EXPERT
Most Valuable Expert 2017

Author

Commented:
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 PrewTest your restores, not your backups...
SILVER EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:

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

MASEE Solution Guide - Technical Dept Head
SILVER EXPERT
Most Valuable Expert 2017

Author

Commented:
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

Open in new window

Daniel PineaultPresident / Owner CARDA Consultants Inc.
BRONZE EXPERT
Distinguished Expert 2018
Commented:

With the approach you've taken, assuming msg returns a fully qualified path and filename, then you could simply do 


.HTMLBody = ReadFile(msg)

The ReadFile function can be taken from https://www.devhut.net/2012/05/14/vba-read-file-into-memory/ 


You'll also notice, that in the snippet above I changed the .Body to .HTMLBody to allow form HTML formatting...

EE Solution Guide - Technical Dept Head
SILVER EXPERT
Most Valuable Expert 2017
Commented:
I managed to make it working by this

Thanks to all
Send-Bulk-EMails.xlsm