VBA MailMerge length > 255

Question

i'm trying to use MailMerge with Word 2010.

I have a TAB delimited file database.dat which looks like the following:

ID       Name       Street
1        John       FooBar 1
2        Smith      FooBar 2

This file is used in Word with the following VBA Code:

ActiveDocument.MailMerge.OpenDataSource Name:="C:/database.dat", _
    ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
    AddToRecentFiles:=False, Revert:=False, Format:=wdOpenFormatAuto

I can use the fields from the file now in the text. So everything works fine.

The Problem:

I need to do some further elaboration with the data in VBA, so I fetch the MergeField Value with:

Function getInfoField(mergefield As String)
    On Error GoTo MergeFieldNotFound:
    getInfoField = ActiveDocument.MailMerge.DataSource.DataFields(mergefield).Value
    GoTo EndFunc
MergeFieldNotFound:
    getInfoField = ""
EndFunc:
End Function

But if the length of a merge field value exceeds 255 characters it is cut off. So if I insert

MsgBox Len(ActiveDocument.MailMerge.DataSource.DataFields(mergefield).Value)

It outputs 255 for a String with e.g. 500 chars.

But directly in the word document, all 500 chars are shown for the merge field.

Question:

How can I get more than 255 chars out of the merge field in VBA?

Answer 1

I haven't tried it, but the following might work:

1. you create a document variable to hold the full text you use a DOCVARIABLE field in the Mail Merge Main document for this field instead of a MERGEFIELD field

2. in the MailMergeBeforeRecordMerge event, you

   a. temporarily insert the MERGEFIELD field in a place that allows you to extract its result easily (e.g. at the beginning of the document)

   b. if necessary, update the Field (e.g. Doc.Fields(1).Update)

   c. retrieve the text (Doc.Fields(1).Result.Text

   d. delete the field you inserted

   e. manipulate the text as required

   f. put the result in the Document Variable

   g. if necessary, update the DOCVARIABLE field.

Be warned - I can't remember the details but some versions of Word don't play nicely with some characters in DOCVARIABLE fields, particularly if they are inside tables.

Otherwise, I think you would have to try to open another connection to the file (e.g. it might be possible to do this using ADO and the Jet/ACE provider+Text file IISAM), which might make it easier to extract the text, but you would have to have a mechanism (such a s a unique key) to enable you to retrieve the correct record via ADO as is currently selected in the MailMerge.