Sending a HTML Email using Word Email Template and Excel Macros

December 17, 2012| Posted by : | Filled in Excel Macro, Excel Tips, Popular Articles

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

Related posts:

  1. How to Send Email Automatically from Excel Hello friends, In the previous Article, you learnt how to...
  2. How to send email from Excel Macro from Outlook This is continuation of my Previous Article you learnt How...
  3. How to Copy content from Word using VBA As many of us want to deal with Microsoft Word...
  4. How to Send an Email using Excel Macro from Gmail or Yahoo Dear Readers, In this article we are going to learn...
  5. Macro to Copy from Excel in to Word Document In Previous Article we saw how to Copy Content from...

5 Responses to “Sending a HTML Email using Word Email Template and Excel Macros”

  1. Judy says:

    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.

  2. Judy says:

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

    • Prasanna Narayanan Srinivasan Prasanna Narayanan S says:

      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.

      • Judy says:

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

    • Vishwamitra Mishra Vishwamitra Mishra says:

      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

Leave a Reply

Comments

comments