Outlook Template replace text 2

[Andrzejek]

A user has an Outlook Template (*.oft file) formatted as Rich Text where they have some text they would like to replace with the data from the data base (instead of typing or copy-n-paste). So I set the text in the Template that needs to be replaced matching the fields in the data base., i.e. If the field in the DB is PrimaryPhaseNumber then in the Template there is a text @PrimaryPhaseNumber@

I can replace the text with the data from DB, but that also messes up the other formatting - in the Template there are checkboxes, simple tables, etc. and Signature also gets messed up.

Is there a way to replace text in the Outlook template (as a new email) without messing everything else up?

 

[MathiasB]

A user has an Outlook Template (*.oft file) formatted as Rich Text where they have some text they would like to replace with the data from the data base (instead of typing or copy-n-paste). So I set the text in the Template that needs to be replaced matching the fields in the data base., i.e. If the field in the DB is PrimaryPhaseNumber then in the Template there is a text @PrimaryPhaseNumber@

I can replace the text with the data from DB, but that also messes up the other formatting - in the Template there are checkboxes, simple tables, etc. and Signature also gets messed up.

Is there a way to replace text in the Outlook template (as a new email) without messing everything else up?

MathiasB​

Programmer​

Aug 21, 20191301GB
From what I recall, we had to use courier new or one of those plain text format fonts

 

[Andrzejek]

I can deal with the plain text, but those Templates were done in Rich Text with all Bolds, Italics, tables (rows and columns, cells, etc.)

 

[OzFoxy]

Hi Andy, what do you mean by 'messes up the other formatting'? Is it just messing up the alignment, changing fonts / font weights, converting everything to plain text?
The first step in problem solving is to understand the problem. Can you be more specific?

 

[mintjulep]

You'll need to add the RTF encoding to the ASCII strings you're working with.

Or maybe you can read the RTF template inclusive of encoding, then change only the text and drop the whole thing back in.

Rich Text Format - Wikipedia

en.m.wikipedia.org

 

[Andrzejek]

To show the 'Before' and 'After' shots maybe will explain better.



and after running some text replacement:



I may try to persuade them to use just simple text, but it would be nice to do it right.

 

[combo]

Without testing, can you try to use Word as editor to edit the mail body, as gmayor suggests in http://www.vbaexpress.com/forum/archive/index.php/t-64933.html ?

 

[Andrzejek]

Interesting code, combo

Tried this:

With olEmail
    .BodyFormat = olFormatRichText
    Set olInsp = .GetInspector
    Set wdDoc = olInsp.WordEditor
    Set oRng = wdDoc.Range
    oRng.Collapse 1
    'oRng.Text = "Dear someone," & vbCr
    oRng.Text = Replace(oRng.Text, "@PrimaryPhaseNumber@", "My Project Number")
    oRng.Collapse 0
    oRng.Select
    .Display
End With

I could place "Dear someone, " at the top of the message, but Replacing the Text did not do anything.
Although the Signature stayed in place, which is great!

 

[strongm]

I guess you are doing a replace against MailItem.Body

Try against MailItem.RTFBody

There are some wrinkles working with RTFBody, though ...

 

[combo]

I will test this tomorrow, but basing on https://learn.microsoft.com/en-us/o...Word/finding-and-replacing-text-or-formatting I would try:

    ...
    Set oRng = wdDoc.Content
    With oRng.Find
        .Text = "@PrimaryPhaseNumber@"
        .Replacement.Text = "My Project Number"
        .Execute Replace:=wdReplaceAll, Forward:=True,  Wrap:=wdFindContinue
    End With

 

[Andrzejek]

Nice progress, thank you combo
Your suggestion works - I had to replace some constants since I use Late Binding (wdReplaceAll = 2, wdFindContinue = 1) and I need to eliminate some extra code

Option Explicit

Sub outlookmail()
'Use the code from http://www.rondebruin.nl/win/s1/outlook/openclose.htm to start Outlook
Dim olApp As Object
Dim olEmail As Object
Dim olInsp As Object
Dim wdDoc As Object
Dim oRng As Object
Const olFormatPlain As Long = 1
Const olFormatHTML As Long = 2
Const olFormatRichText As Long = 3

'Set olApp = OutlookApp()
Set olApp = CreateObject("outlook.application")
Set olEmail = olApp.CreateItemFromTemplate("C:\Andy\EASE\TEST.oft")

With olEmail
    .BodyFormat = olFormatRichText
    Set olInsp = .GetInspector
    Set wdDoc = olInsp.WordEditor
  
    Set oRng = wdDoc.Content
    With oRng.Find
        .Text = "@PrimaryPhaseNumber@"
        .Replacement.Text = "My Project Number"
        .Execute Replace:=2, Forward:=True, Wrap:=1
    End With

    .Display
End With

Set olEmail = Nothing
Set olInsp = Nothing
Set wdDoc = Nothing
Set oRng = Nothing
Set olApp = Nothing
End Sub

 

[strongm]

Pretty sure we can do better than dragging in Word, using RTFBody. But not right now, as I am in the pub...

 

[strongm]

Try this:

Public Sub OutlookMail()
    Dim olApp As Object 'Outlook.Application
    Dim olEmail As Variant
    Dim rtfText() As Byte
    Dim rtfVBAString As String
    
    Set olApp = CreateObject("outlook.application")
    Set olEmail = olApp.CreateItemFromTemplate("d:\downloads\deleteme\test3.oft")
    
    rtfVBAString = StrConv(olEmail.RTFBody, vbUnicode) ' convert RTFBody byte array to string VBA understands
    rtfVBAString = Replace(rtfVBAString, "@NepaDocumentID@", "My Project Number")
    rtfText = StrConv(rtfVBAString, vbFromUnicode) ' convert back
    olEmail.Body = rtfText 'Yes .Body, NOT .RTFBody; poorly documented quirk
    olEmail.Display
End Sub

 

[Andrzejek]

​

strongm, I will give it a shot and report back
As a side note, I've just got the message that my original code started working all of the sudden. Everybody is surprised (me included) and they (users) give me a credit for fixing it (I will take it Who knows what had happened? Gremlins?

Edit - code works like a charm, no surprise coming from you