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.
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”.
c. Now Give a name for Bookmark and say “Add”
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..”
c. Hit “Formula” button
d. select the Paste Bookmark name and Hit “OK” button.
e. Give a Custom Name as needed.
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.
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.
Could anyone help to send the attachment to me? Thanks. I could not download it.
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.
It's okay now. I can download it from home. I guess there might be some issue with the Internet connection in office.
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
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.
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
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.
Hi Riz,
you can refer the below article to do so:
http://learnexcelmacro.com/wp/2012/06/send-email-automatically/
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)
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!!!!
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 ?
Pretty! This was a really wonderful post. Thank you for your provided information.
You are welcome 🙏
Could you help me to add an extra of file attachment (code)
Hi, you can simply add this line in your email code to add an attachment to the email. you can refer these articles here: https://vmlogger.com/excel/2012/01/how-to-send-activeworkbook-as-attachment-in-email/