Sending a HTML Email using Word Email Template and Excel Macros

.

How to Send a HTML Email using Word Email Template using Excel Macro

Hi All,

 

This is my very first post for learnexcelmacro.com. Thanks to Vishwa for roping me in for this good cause.

Your Help/ comments / suggestions for improving this better.

Things Needed:

1. Word Document that can serve as a template, with field maps and bookmarks that can help us in getting our custom Data

2. Excel Macro that can have this Word Document as an object and which has details that needs to be pasted / brought in the Template, to form a custom mailer.

How to Do:

Creating Word Template:

1. Open an Empty Word Document and type your message as needed. For Example, following is my template message. I would append and insert text and Tables later using Excel macros in places as needed.

Template as Needed

Hi All, 

Please find the below status about Marks secured by candidates of  Standard, during  Examinations.

 

Thanks and Regards,

Management Team – XXX School

P.S: This is an Auto Generated Email. You are receiving this email, since you are part of the Parent Teachers Association for this Year.

Please help us improving the quality of this process, through your Inputs and suggestions.

 


 2. Now Insert Bookmarks and then field maps in places, where you need custom input text from Excel Macro. For Example, in the Above mentioned template, I would like to get the Standard Name, Examination name and the Result Table, so  that the Email looks something like below.

 

Email as Needed

Hi All, 

Please find the below status about Marks secured by candidates of XII Standard, during Quarterly Examinations.

 

 

S.No Student Name Register No Mark 1 Mark 2 Total Status
1 Name 1 XXYYYZZ01 60 60 120 PASS
2 Name 2 XXYYYZZ02 40 60 100 FAIL

 

Thanks and Regards,

Management Team, XXX School.

P.S: This is an Auto Generated Email. You are receiving this email, since you are part of the Parent Teachers Association for this Year.

Please help us improving the quality of this process, through your Inputs and suggestions.

 3. Follow the Below Instructions to insert a bookmark.

a. Place the cursor at the position intended to create a bookmark.

b.  Go To “Insert” Tab and Hit “Bookmark”.

 

Insert Bookmark

Insert Bookmark

c. Now Give a name for Bookmark and say “Add

 

InsertBookMarks wherever needed

Click Add Button

d.  Follow the same procedure to insert bookmarks wherever needed.

4. Follow the below instructions to insert a field.

a.      Place the cursor at the position intended to create a bookmark.

b.      Go To “Insert” Tab and Hit “Quick Parts”. Then from Dropdown, select “Field..

Insert Field

Insert Field

 

c.      Hit “Formula” button

d.      select the Paste Bookmark name and Hit “OK” button.

Formula - Paste Bookmark

Formula - Paste Bookmark

 

e.      Give a Custom Name as needed.

Formula - Paste Bookmark - Custom Name

Formula - Paste Bookmark - Custom Name

 

f.     Follow the same procedure to insert fields wherever needed.

 

Creating Excel Macro:

1. Have necessary Input fields as needed and Insert this Created Word as an Object in your Macro.

Create Macro

Create Macro

2.  Now Use the following code to Mail your custom Template.

 

Code to Send HTML Email

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'	Function Name 	: 	fnSendResultMail
'	Parameters	 	: 	rng 			-    Range to be pasted in the Email Template
'						strTOReceipent	-    To Recipient Email Id
'						strCCReceipent  -    CC Recipient Email Id
'						strBCCReceipent	-    BCC Recipient Email Id
'						strStandard	    -    Class Standard name of the students
'						strExamName	    -    Examination name
'						strMailerName	-    Mailer Title
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function fnSendResultMail(ByVal rng, strTOReceipent, strCCReceipent, strBCCReceipent, strStandard, strExamName, strMailerName)

    On Error Resume Next
    'Select the Range
    rng.Select

    'Send Email
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
        .To = strTOReceipent
        .CC = strCCReceipent
        .BCC = strBCCReceipent
        .Subject = strMailerName
        .HTMLBody = WordToOutlook(rng, strStandard, strExamName)
        '.Send
        .Display
    End With

	With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing  

End Function

Code to Convert Word Template contents into HTML

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'	Function Name 	: 	WordToOutlook
'	Parameters 		: 	rng			-    Range to be pasted in the Email Template
'						strStandard	-    Class Standard name of the students
'						strExamName	-    Examination name
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function WordToOutlook(ByVal rng As Range, strStandard, strExamName)
    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".doc"
    Set selectRange = rng    
    Set WDObj =ThisWorkbook.ActiveSheet.OLEObjects("EmailTemplate")
    WDObj.Activate
    WDObj.Object.Application.Visible = True

    Set WDApp = GetObject(, "Word.Application")     
    Set WDDoc = WDApp.ActiveDocument    
    For Each Item In WDDoc.Fields    
        If InStr(Item, "<NAME>") > 0 Then
            Item.Select            
            WDApp.Selection.Text = strStandard
            WDApp.Selection.Font.Bold = True            
        ElseIf InStr(Item, "<TABLE>") > 0 Then
            Item.Select
            selectRange.Copy
            WDApp.Selection.PasteSpecial xlPasteValues
            Application.CutCopyMode = False
        ElseIf InStr(Item, "<EXAM>") > 0 Then
            Item.Select
            WDApp.Selection.Text = strExamName
            WDApp.Selection.Font.Bold = True
        End If
    Next   

    'Save as HTML
    WDDoc.SaveAs TempFile, FileFormat:=8

    WDDoc.Close savechanges:=False
    WDApp.Quit

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.ReadAll
    ts.Close

    Application.DisplayAlerts = True
    Set WDDoc = Nothing
    Set WDApp = Nothing
    Set oEmbFile = Nothing
    Kill TempFile

    Set ts = Nothing
    Set fso = Nothing

    'Return Value
    WordToOutlook = RangetoHTML

End Function

Here the Function “WordToOutlook “ opens the Word object embedded and checks for  field names. According to the Field name, It identifies and reverts the value as needed.

For Example, we have named the field that points to Examination Name in the Word Template as “<EXAM>ExamName”.  The macro here checks for any field with name having “<EXAM>” and change the text of that as per the input. The Table range is pasted in the word document from Excel.  Finally the Macro save the Document in the Temp location in HTML format, and then reads the same using file system object. This will give the HTML Code of the document. Then the same is fed in as the HTMLBody of the Outlook Email.

 

To Help you creating your own template for Email, I am here uploading the Email Template with macro for your Download.

 

Download Now

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

15 Comments

  1. Judy

    Could anyone help to send the attachment to me? Thanks. I could not download it.

    Reply
    • Prasanna Narayanan S

      Hey Judy,

      It would be great if you can mail us the exact error you are getting, when you tried downloading the attachment.

      We are able to successfully download the attachment from here and seems like no one else is facing a similar issue here.

      Please put in the screenshot of the error that you are getting so that it would be great for us to cross check.

      Kindly mail us at info@learnexcelmacro.com

      We will anyway be sending you the attachment sooner to your email id.

      Thanks,

      Prasanna.

      Reply
      • Judy

        It's okay now. I can download it from home. I guess there might be some issue with the Internet connection in office.

        Reply
    • Vishwamitra Mishra

      Dear Judy,

      Your comment is modified as I removed your email id from the comment. Do not put your email id in comment. It may be spammed.

      Regards,

      Admin-Vish

      Reply
  2. Judy

    If I need to send out several emails to different people, how can I write the macro? I tried to write one, but since there is too many operation on the word part, which makes word file active application, finally make the longer time to run the macro.

    After testing, in average it will take about 3 seconds to send out an email. Is there anyway to improve the efficiency if I need to send out a lot of emails one time.

    Reply
  3. Paolo

    Hello,
    First of all I would like to thank you for this article. It is going to help me a lot.

    Well, I started adapting the code to my needs. The only thing that I did not find clear being in my position of low knowledge, is why do you use the “Quick Parts” and where do you specify the “” and “”. Are these the fields?

    Thanks in advance!

    Regards,
    Paolo

    Reply
  4. Riz

    I have a excel sheet at my shop with all sales records.

    I’m not present at my shop physically, so i need to check sales and stock position after every hour.

    Is that possible to create a simple macro which automatically email me entire excel file as attachment, without any interaction of user/pc operator.

    Reply
  5. João

    Hello Vishwamitra.
    Thank you for this article.

    How do I select the mail “from”. I have four emails in Outlook and I wish I could choose one of them.

    Regards
    João (Brazil)

    Reply
  6. Jesus

    Hi vishwa,
    Hope you are doing well. I need to know if there is a possibility to send mass emails using this template, this will be pretty helpful for me.

    In this example you are using rows but I need to have “TO” “CC” emails in columns, also I have two codes that will be replacing the examname and Standard tags in your file do you know how can I do this?

    Thanks in advance!!!!

    Reply
  7. rananthapad

    Hi Vishwa,
    In many of the articles, the graphics are not appearing. I have tried this with different machines and with different browsers. When I click on the image placehoder, I get a 404 error. Please can you fix this ?

    Reply
  8. Jenelle Kromm

    Pretty! This was a really wonderful post. Thank you for your provided information.

    Reply
  9. Mn0910

    Could you help me to add an extra of file attachment (code)

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest