I'm trying to create some Excel code to eventually produce a mail merge document in Word.
The code works fine, and a mail merged document comes out of other end, but, my problem is that I don't want to return all records in the range that I've defined in the Excel 'table'. This works fine if doing the process manually in Word using the 'select recipients' mail merge option but I can't get the code into Excel. All help I've come across suggests that a SQL statement is the answer, but whatever I put in I still get all rows returned.
An extract from my code (edited to make a bit easer to read) is below
Code
Dim wdApp As Word.Application
Dim WordWasNotRunning As Boolean
Dim wdDoc As Word.Document
'Get existing instance of Word if it's open; otherwise create a new one
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err Then
Set wdApp = New Word.Application
WordWasNotRunning = True
End If
On Error GoTo Err_Handler
wdApp.Visible = True
wdApp.Activate
Dim t
t = "template name.dot"
Set wdDoc = wdApp.Documents.Add(t)
Dim sql
sql = "SELECT * FROM `NamedRange` WHERE `FieldName` IS NOT NULL"
With wdDoc.MailMerge
.OpenDataSource _
Name:="source file.xls", _
Connection:="NamedRange", _
SqlStatement:=sql
.Destination = wdSendToNewDocument
.Execute
End With
Display More
Note that NamedRange is a range named on a worksheet, and FieldName is the name of one of my Columns
Any suggestions would be much appreciated.