Need help with QueryString in Mail Merge
The following code is VB code I'm using to conduct a Mail Merge.
The code is running on XP under MSO 03 (Word VBA6)
This code simply opens the template file (if it is not already open) and sets up the merge properties and runs the merge.
The extra piece you see, in bold, in the .DataSource.QueryString is my attempt to get the code to filter the contents of the data file to just those entries who have 0 for their Owners value.
This block of code you see below can run from 0 to 16 contacts so I need the Mail Merge to edit the data source before performing the merge.
The part in bold is my problem. It continues to give me a 4198 run-time error and simply says 'Command Failed'.
[code]
Sub MergeTest()
Err.Number = 0
On Error GoTo ErrorHandler
Windows("New Ltr-C01.doc").Activate
ErrorHandler:
If Err.Number = 5941 Then
Documents.Open ("X:\Letters\English\Templates\New\New Ltr-C01.doc")
End If
Set letterfile = Documents("New Ltr-C01.doc")
sourcefile = "X:Letters\Data\Test Source File.xls"
connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=sourcefile;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:"
' connectionstring = "Merge"
' connectionstring = ""
letterfile.MailMerge.MainDocumentType = wdFormLetters
letterfile.MailMerge.OpenDataSource Name:=sourcefile, _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:="", _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:=connectionstring, _
SQLStatement:="SELECT * FROM `Merge$`", _
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
With ActiveDocument.MailMerge
.DataSource.QueryString = "SELECT * FROM `Merge$` WHERE `Owners`=`0`" & ""
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
End Sub
[/code]
The 1st connectionstring variable above was given to me when I recorded the Mail merge. However, the recording would not record any code about the filtering process.
I have tried a variety of ways to state the part in bold:
[code]
.DataSource.QueryString = "SELECT * FROM `Merge$` WHERE Owners=0"
.DataSource.QueryString = "SELECT * FROM Merge WHERE `Owners`=`0`" & ""
.DataSource.QueryString = "SELECT * FROM `Merge` WHERE `Owners`=`0`" & ""
.DataSource.QueryString = "SELECT * FROM `Merge$` WHERE ((Owners=0))" & ""
[/code]
So far my search for answers has proved unfruitful, anyone have any ideas?
Thanks! JT
