Search code examples
excelvbamailmerge

VBA: Mail Merge - Field not found dialogue box

I've been writing a tool to extract data out of an SQL database via a set of ODBC tables inside Excel VBA and insert the information into some pre-formatted reports using a mail merge and a Word Object. Some of the reports have some optional fields that may not always contain data. In these occasions those optional fields will be absent in the database entirely.

My code has been designed to be dynamic and produce the merge information (by way of a CSV file merged using VBA) using the question details in the database as the field headers. My problem stems from when the option questions are absent from the output merge file and Word prompts the user to "Remove Field". I've struggled to find a programmatic way to essentially answer this dialogue box on behalf of the user (which will always be to remove the field), or have word just know what to do and not require to ask.

I've had no luck finding a module, command or function that will either tell the word object to clean this up automatically, or any handler to answer the question programmatically. Any help would be appreciated.

Solution

  • Just incase someone else falls into a similar problem, here is how i solved this issue:

    1. Instead of opening up a datasource, open up a headersource instead. In my case the headsource and data source are the same file.
    2. Compare the merge fields (< word doc object >.MailMerge.Fields) to the fields inside your file (< word doc object >.MailMerge.DataSource.FieldNames). Delete each merge field when it is not found (< MailMergeField >.delete).
    3. Open the data source as normal and set the first record to 2 if its the same file as your headerfile.