Results 1 to 8 of 8

Thread: Update mail template an preserve the format

  1. #1
    Neophyte Javier's Avatar
    Join Date
    Nov 2012
    Location
    Spain
    Posts
    4
    Articles
    0

    Update mail template an preserve the format



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi there,

    We'd need to know what email program, and see the code you're using.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Neophyte Javier's Avatar
    Join Date
    Nov 2012
    Location
    Spain
    Posts
    4
    Articles
    0

    I am using Microsoft Outlook 2010 and Excel 2010 with VBA

    Quote Originally Posted by Ken Puls View Post
    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

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Neophyte Javier's Avatar
    Join Date
    Nov 2012
    Location
    Spain
    Posts
    4
    Articles
    0
    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

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Neophyte Javier's Avatar
    Join Date
    Nov 2012
    Location
    Spain
    Posts
    4
    Articles
    0
    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

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Cool, Javier. Glad you got it sorted out!
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •