PDA

View Full Version : Update mail template an preserve the format



Javier
2012-11-05, 09:29 PM
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

Ken Puls
2012-11-06, 05:01 PM
Hi there,

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

Javier
2012-11-06, 06:19 PM
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
2012-11-06, 06:28 PM
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:


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
2012-11-06, 11:05 PM
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
2012-11-07, 02:49 AM
Hmmm...

Let's see what happens here:


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
2012-11-07, 06:22 PM
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

Ken Puls
2012-11-09, 03:43 AM
Cool, Javier. Glad you got it sorted out!