Mail Merge with OpenRecordset problem

[batteam]

I am trying to use MailMerge with Word from an Access 2007 (project) form, with tables in SQL. I have the following code:

Dim rsMM As ADODB.Recordset
Dim strSQLMM As String
strSQLMM = "Select * from dbo.xxx_LETTERS"

rsMM.Open strSQLMM, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Set rsMM = New ADODB.Recordset

conTemplate = "Y:\CVC USERS\nnnn\GroupLetters\documentName.dotx"

Set wrdApp = New Word.Application
Set Doc = wrdApp.Documents.Add(conTemplate)

With Doc.MailMerge

.OpenDataSource Name:=strSQLMM, _
LinkToSource:=True

.Destination = wdSendToNewDocument
.SuppressBlankLines = True

etc.
etc.

But the code always hangs at the 'OpenDataSource Name' command. I have tried many variations and nothing seems to help. Any ideas? Thanks.

 

[SkipVought]

hi,

Dim rsMM As ADODB.Recordset
Dim strSQLMM As String
strSQLMM = "Select * from dbo.xxx_LETTERS"
[b]
set rsMM = New ADODB.Recordset
[/b]
rsMM.Open strSQLMM, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[batteam]

Thanks for your help. Moving the 'Set rsMM' didn't help. I now get the error message 'File Not Found' at the .OpenDataSource Name command.

I will keep trying. Do you know if my Word document has to be in the Word template form (.dotx)? Thanks again.

 

[SkipVought]

From MS Word VBA Help

Parameters

Name Required/Optional Data Type Description
Name Required String The data source file name. You can specify a Microsoft Query (.qry) file instead of specifying a data source, a connection string, and a query string.

You specify the query string only.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[batteam]

I've given up on MailMerge with a SQL or recordset data source. I am now attempting to create a .csv file in VBA, but cannot get the file saved properly as a .csv file. My code is:

Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")
ExcelSheet.Application.Cells(1, 1).Value = "case_id"
ExcelSheet.SaveAs FileName:="Y:\CVC USERS\Technology\GroupLetters\RePrint_Letter.csv", _FileFormat:=xlCSV

but always get the error: 'SaveAs method of Workbook class failed'.

Any ideas? Do I have to use another method of the CreateObject?

Thanks for your help.

 

[SkipVought]

You need to CREATE the Excel.Application object, NOT a sheet object at this point. Look at the CreateObject method.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[batteam]

OK, will try. Thanks.