This past week, I wrote a bit of VBA code to help me automate mail merges with Word and Excel. It works really well, but I ran into a piece of behavior that Word exhibits that I would like to change. I was hoping someone can tell me how to do it, or at least point me in the right direction.
I have 10 Word documents. The documents never change.
For each document, I wrote a query that pulls the mailing information from an Excel spreadsheet. The query never changes.
The only thing that does change, is each week, the Excel spreadsheet is replaced by a newer version.
Here’s the issue:
As long as the query returns at least one record, everything runs fine. If the query returns an “empty” set, meaning nothing matched the query, it returns this information message box:
"Word could not merge the main document with the data source because the records were empty or no data records matched your query options"
Because this dialog box needs a response (click OK) it stops my whole automated process.
How can I suppress this dialog box
I assume it would need to be done from the registry, because I would want it to be a permanent change.
Thank you for your help.
VBA Mail Merge Question
TimBailey99
Hey,
You wouldn't want to go messing with the registry to fix this problem, that doesn't seem like a proper fix to me.
I'm not familar with mail merge, or what you have done to set it up, but if you run your query just prior to the merge and then check the record count you could then see whether or not the mail merge should go ahead.
You could use ADO or DAO to run your query.
Hope that helps.
RWBitters
Here's some info from our support engineer:
Base on my understanding, our buddy get "Word could not merge the main document with the data source because the records were empty or no data records matched your query options" message in his VBA code when the RecordSet retrieved from EXCEL is empty. This is by design behavior. The easiest way to workaround this issue is we pre-detecting whether the RecordSet is empty or not. If nothing returns, we just simply skip the mail merge block. Please refer to following article:
How to merge each record of data source as separate documents in Word
http://support.microsoft.com/kb/840975/en-us
-brenda (ISV Buddy Team)
DONG
With ActiveDocument.MailMerge
If .DataSource.RecordCount > 0 Then
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End If
End With