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.

 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.

 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

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

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

c.      Hit “Formula” button

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

Formula - Paste Bookmark

e.      Give a Custom Name as needed.

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

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.