Problems with VBA mailmerge
 

Hi,

I am having some problems getting a VBA to work. It's supposed to open up a mailmerge template and fill it in from an excel sheet, which it has already been linked to, then print the sheets. Here's my code:


Dim wd As Object
Dim wdocSource As Object

Dim strWorkbookName As String

On Error Resume Next
Set wd = GetObject(, "Word.Application")
If wd Is Nothing Then
Set wd = CreateObject("Word.Application")
End If
On Error GoTo 0

Set wdocSource = wd.Documents.Open("X:\y\z\a.doc")

strWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name

wdocSource.MailMerge.MainDocumentType = wdFormLetters

wdocSource.MailMerge.OpenDataSource _
Name:=strWorkbookName, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
SQLStatement:="SELECT * FROM `sheet$` WHERE `Method`='xyz' And `Amount:` 0"

With wdocSource.MailMerge
.Destination = wdSendToPrinter
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With

wd.Visible = True
wdocSource.Close SaveChanges:=False

Set wdocSource = Nothing
Set wd = Nothing

End Sub


I get the error "Run-Time error 4198: command failed." When i click debug it takes me to this section:

wdocSource.MailMerge.OpenDataSource _
Name:=strWorkbookName, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
SQLStatement:="SELECT * FROM `sheet$` WHERE `Method`='xyz' And `Amount:` 0"


any ideas what could be wrong? I used this macro in another spreadsheet and it worked just fine.

Thanks so much!

Problems with VBA mailmerge
 

mkz wrote:

Connection:="Data Source=" & strWorkbookName & ";Mode=Read",


I did not try that but I am not sure if that is valid connection string.
Try to google some examples or setup connection manually and read
connection property.