Word Automation (VBA): Mail Merge Rich Text Format

Question

I'm trying to do a Word MailMerge via VBA from my Access project. I created a clsWordMerge class so I could declare the Word application WithEvents, and take advantage of Word's MailMerge events, mainly the AfterMerge event.

Everything works fine, and I get the finished Word documents created, except that the source fields containing RTF data end up in the document not as formatted text, but instead the RTF codes and data:

<div><font face="Times New Roman" size=3 color=black>This is my <strong><em>test </em></strong>paragraph.</font></div>

Where I would expect to see: This is my test paragraph

This happens whether I do a mail merge using a CSV file for my data source or an Access table.

So is there any way to correct this, and show the formatted data? I have access to all of the MailMerge events that Word provides.

Thanks..

Answer 1

No, there's no way to merge in RTF and have it display as Word content. RTF is not Word's native file format - a converter is required to display RTF as Word content.

Mail merge literally displays the data text, as it appears in the data source. There are no "advanced features" that enable selectively formatting the mail merge result.

Also, based on painful experience, relying on MailMergeAfterMerge is not advisable. When it was introduced, I tried it, was enthusiastic... until it started failing. The event is unpredictable and not reliable.

Given your requirements, a fully VBA-driven data transfer from Access to Word is a better investment of time and energy.

Answer 2

It probably can be done in certain circumstances, but I agree with Cindy Meister that the Mail Merge Events have not proven reliable (unless they have been fixed - I haven't actually used them for years). The following description of real and likely problems that I have previously encountered when trying this may help:

1. Not sure any of it can be done if you are merging to Email.

2. AFAICR the event you are likely to need (MailMergeBeforeRecordMerge) only fires each time Word processes the Main Document, not each time it processes a record in the data source. So if your Mail Merge Main Document "consumes" more than one Data Source record, e.g. because it uses { NEXT } or { NEXTIF } fields, it may be very difficult to get MaiMergeBeforeRecordMerge to do what you need. If I am right about that, that would be enough to put me off making the attempt.

3. in order to insert your "RTF", you must either

   a. Have code that can interpret the "RTF" encoding and do all the right things necessary to insert it in your document, or b. Have code that saves the "RTF" to an external file, then uses (say) Range.InsertFile to insert it and have Word interpret its contents, or perhaps c. Use the clipboard to help you do the conversion.

   If any of your rich text fields actually contained RTF, (a) would be difficult unless you could find a suitable library to help you. But in fact your sample shows a typical Access rich text field value, which is HTML-like. In fact, I think it is all standard HTML tagging that Word can interpret, but I don't know for sure. That could be much easier to interpret, especially if you only need the plain text (at its simplest, you might be able to throw away the tagging and insert the result.

4. If your rich text is longer than 255 characters (including the markup), Word's Document.MailMerge.DataSource.DataFields("the case-sensitive field name as Word sees it").Value will be truncated. So if you need the whole of the text or more of it, you'll have to get it somewhere else

5. The value inserted in the document using a { MERGEFIELD } field is not truncated to 255 characters so you may be able to get the value from the document. Word MailMerge may impose another limit (can't remember, perhaps 64Kb for an OLE DB connection, perhaps less, or perhaps there is a length limit for the data as a whole.

6. If you can't get the data from the document, you can get it directly from Access. Probably rather easily if your code is running in Access, but it can be done by using ADODB or perhaps ADO from Word VBA code. Your Mail Merge Data Source will need to retrieve the key fields of the record if you want to do that reliably. During development, if your application is running from Access but you are using VBA code in Word, you will probably also need to make sure that you save your Access database each time you modify your Access VBA code, otherwise Access opens the database exclusively and Word won't be able to retrieve data from it.

7. If you need to use (b) or (c) to save your HTML to a file then you may need to surround the HTML that you get from Access with tags and possibly tags to get Word to recognise the HTML. You could use Scripting.FileSystemObject to save the text, or perhaps ADODB.Stream if you are already using ADODB to retrieve Access data.

8. You should be able to use VBA Range.InsertFile to insert it, as long as you have some placeholder that tells you what to put it. Or you could use an INCLUDETEXT field and ensure that your Event code updates that field. A snag with the INCLUDETEXT approach is that if you merge to a new document, the INCLUDETEXT fields remain in the document so if you update them, they will all end up with the same result if you do not also create a new file for each source record.

i.e. quite a lot to think about!