Update mail template an preserve the format

Javier

New member
Joined
Nov 5, 2012
Messages
4
Reaction score
0
Points
0
Location
Spain
Hello,

I am using a Excel macro (VBA) to open a mail template, update some labels an then send it. The problem araise when I try to do any change in the body section. As soon as I update it, all formats are lost, bold and colours fonds, ....

Any idea to solve this?

Kind regards
Javier
 

Javier

New member
Joined
Nov 5, 2012
Messages
4
Reaction score
0
Points
0
Location
Spain
I am using Microsoft Outlook 2010 and Excel 2010 with VBA

Hi there,

We'd need to know what email program, and see the code you're using.

I am using Microsoft Outlook 2010 and Excel 2010 with VBA

The code is more or less
Private Sub openTemplate(ByVal strTemplateName As String, ByRef MailItem As Object)
Dim myOlApp
Dim olMailItem
Dim StrTo As String
Dim StrCC As String
Dim StrSubject As String

Set myOlApp = CreateObject("Outlook.Application")
Set MailItem = myOlApp.CreateItemFromTemplate("C:\Shell\Macros_templates\SendMails\Templates\EMEA Project - Project ID - Project Name.oft")
MailItem.Display
StrTo = MailItem.TO
StrCC = MailItem.CC
StrSubject = MailItem.Subject

MailItem.body = Replace(MailItem.body, strKey, strValue, , , vbTextCompare)

so the idea is to replace a fix key for a variable value that is read from the excel
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
So here's the issue... In order to hold formatting, your email is most likely in HTML format. The replace code you're using is working on plain text.

I don't know for sure that this will work, but try modifying the mailitem.body line to:

Code:
MailItem.HTMLBody = Replace(MailItem.HTMLBody, strKey, strValue, , , vbTextCompare)

It that doesn't work, we'll need to see if we can bring back all the HTML source first, I guess. Let's see if the simple fix works first before we go that route though.
 

Javier

New member
Joined
Nov 5, 2012
Messages
4
Reaction score
0
Points
0
Location
Spain
Hi again,

Many thanks, this is working in the HMTL mails, so a first step. However, I am using rich text format, as far as, we have some standard atachments that are placed in some particular areas of the mail. So if possible I would prefer to continue using that format.
Any idea?

Thanks for your help
Javier
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hmmm...

Let's see what happens here:

Code:
Private Sub openTemplate(ByVal strTemplateName As String, ByRef MailItem As Object)
Dim myOlApp
Dim olMailItem
Dim StrTo As String
Dim StrCC As String
Dim StrSubject As String

   Set myOlApp = CreateObject("Outlook.Application")
   Set MailItem = myOlApp.CreateItemFromTemplate("C:\Shell\Macros_templates\SendMails\Templates\EMEA Project - Project ID - Project Name.oft")
   MailItem.Display
   StrTo = MailItem.TO
   StrCC = MailItem.CC
   StrSubject = MailItem.Subject

   with MailItem
          .bodyformat = olFormatRichText
          .body = Replace(.body, strKey, strValue, , , vbTextCompare)
   end with

Let me know how that works... if not, I'll see if I can call in some help from an Outlook expert.
 

Javier

New member
Joined
Nov 5, 2012
Messages
4
Reaction score
0
Points
0
Location
Spain
First of all, MANY THANKS for your help.

I have found a solution in between that it is working. The last one you sent is not working as far as the mail format is automatically change when the Body property is updated

this is my solution
Use the RFTBody property, but as this one is not in text, it is necessary to do some "weird" conversions.
The solution is tested and working, although there are some issues. When the RTF info is converted to unicode, it includes lots of formatting codes in between, that means that if you are looking for a key like "<Project>", it might not work as in the unicode text it might be <%%%%%Project%%%%%%> where %%%% represents the formatting code.

StrBody = StrConv(MailItem.RTFBody, vbUnicode)
StrBody = Replace(StrBody, strKey, strValue, , , vbBinaryCompare)
Dim x() As Byte
x = StrConv(StrBody, vbFromUnicode)
MailItem.RTFBody = x
 
Top