Vba access mailerge.opendatasource

[roberto21]

I would lijke very much to know what stupid thing I am missing here. This code stores the result of a query in an excel .xslx file, which is used as input to opendatasource. I tried several variations, but it seems I cannot go past the mailmerge.opendatasource instruction.

Sub PrintLetter2(NumeroTessera As Double)
'Mail merge and printo to dialog printer

'On Error GoTo ErrorHandler


' open template in Word
Dim filepath As String
Dim WordApp As Word.Application
Dim WordDoc As Word.Document
Dim strConnection As String, myquery As String
Dim bPrintBackgroud As Boolean
Dim oMerged As Document
Dim presentDB As String
Dim qd as DAO.QueryDef


Set WordApp = New Word.Application
filepath = CurrentProject.path
Debug.Print filepath, NumeroTessera
With WordApp
    .Visible = True
    Set WordDoc = .Documents.Open(filepath & "\LetteraNuoviSoci.docx")
End With


' Name:=filepath & "\RicevuteConPrimaNotaFE.accdb"
presentDB = CurrentProject.FullName
'MailMerge selected records from table to Word document


myquery = "SELECT * FROM [LibroSoci] WHERE [Numero tessera] = " & NumeroTessera

On Error resume next   
 DoCmd.DeleteObject acQuery, "myExportQry"
    kill filepath & "\testexcel.xslx"


 qd = CurrentDb.CreateQueryDef("myExportQry")
qd.Sql = myqueryDoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, qd.Name,  filepath & "\testexcel.xslx",True

' Docmd.OutputTo acOutputQuery, qd.name, acFormatXLSX, filepath & "\testexcel.xslx"


With WordApp
    .ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    .ActiveDocument.MailMerge.OpenDataSource _
        Name:=filepath & "\testexcel.xslx", _
        ConfirmConversions:=False, ReadOnly:=True, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatText, Connection:="RicevuteCPNCompletoBackup", _
        SQLStatement:="", SQLStatement1:="", _
        SubType:=wdMergeSubTypeOther
       


        bPrintBackgroud = .Options.PrintBackground
        .Options.PrintBackground = False
        .Application.DisplayAlerts = wdAlertsNone
        
   With .ActiveDocument.MailMerge
              
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        

        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        
        .Execute Pause:=False
    End With
           Set oMerged = .ActiveDocument
            'Show the Print dialog box
            .Visible = True
            .Activate
                .Dialogs(wdDialogFilePrint).Show
            oMerged.Close 0
            'Restore all the alerts
            .Application.DisplayAlerts = wdAlertsAll
            Options.PrintBackground = bPrintBackgroud
End With


WordDoc.Close SaveChanges:=False
WordApp.Quit


ErrorHandlerExit:
Exit Sub
ErrorHandler:
If Err = 429 Then
'word is not running; open word with CreateObject
Set appWord = CreateObject(Class:="Word.Application")
End If

End Sub

I tried both with docmd.transferspreadsheet and with docmd.outputTo with same result. if I don't write anything in Connection and SQLStatement, word stops and asks for the table. If I press OK, the mailmerge process proceeds to the end without errors



If I write SqlStatement := "SELECT * FROM 'myExportQry' or put a reference to the sheet in Connection I get this



where \\desktop..-. is the filepatH, and the file appears as .xls. What am I doing wrong?
oFFICE VERSION 2021

[June7]

HTML tags don't work within CODE tags of post. So those color codes accomplished nothing but confusion. You should edit your post if still allowed.

What data type is [Numero tessera] field?

[roberto21]

Sorry, I don't know what happened, I only put

 and

(#) around the code.
Numero Tessera is a double. Anyhow, as I said, the query ("myquery") works perfectly, the data are correctly inserted into the xlsx file.

[Welshgasman]

You use code and /code surround by [ & ]

Why not ask ChatGPT?

Sub MailMergeFromExcel()
    Dim wdDoc As Document
    Dim wdApp As Word.Application
    Dim strExcelFile As String
    Dim strSQL As String


    ' Path to the Excel file
    strExcelFile = "C:\path\to\your\ExcelFile.xlsx" ' <-- Update this path


    ' Open Word and the main document
    Set wdApp = New Word.Application
    wdApp.Visible = True ' Set to False if you don't want Word to be visible


    ' Open the Word document that contains the merge fields
    Set wdDoc = wdApp.Documents.Open("C:\path\to\your\WordTemplate.docx") ' <-- Update this path


    ' SQL statement to select the entire sheet (Change "Sheet1$" to match your sheet name)
    strSQL = "SELECT * FROM [Sheet1$]"


    ' Attach the Excel file as the data source
    wdDoc.MailMerge.OpenDataSource _
        Name:=strExcelFile, _
        ConfirmConversions:=False, _
        ReadOnly:=True, _
        LinkToSource:=True, _
        AddToRecentFiles:=False, _
        Format:=wdOpenFormatAuto, _
        Connection:="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strExcelFile & ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';", _
        SQLStatement:=strSQL


    ' Execute the mail merge
    With wdDoc.MailMerge
        If .MainDocumentType = wdNotAMergeDocument Then
            .MainDocumentType = wdFormLetters
        End If
        .Execute
    End With


    ' Clean up
    wdDoc.Close False
    wdApp.Quit
    Set wdDoc = Nothing
    Set wdApp = Nothing


    MsgBox "Mail Merge Completed!", vbInformation
End Sub

[roberto21]

All right, it works. Thanks a lot, really, it was driving me nuts. So what I was missing was the correct Connection parameter (and the sqlstatement with it). Any advantage in using transferspreadsheet or outputTo to fill the excel file?
Thank you again

[Welshgasman]

Not that I am aware of.
There is also CopyFromRecordset ?

[madpiet]

If all you're doing is using a query or table from Access as a data source for mail merge, why not just use Albert Kallal's "Super Easy Word merge" code? It's older than the hills, but I'd bet it still works. The nice thing is that you can create your templates in Word and then they're not tightly bound to your database anymore. (so if your database gets renamed or moved, Word won't throw a fit.)


Albert!! Where'd your code go??? Looks like that reaaaaaalllly old website may be gone.

[jojowhite]

Do you know that it is simpler to Create a similar Report in Access?
You do not need to import anything nor do any Automations.

[madpiet]

It's easier to build a report to show the stuff you want, but it depends on the formatting you need. You can do crazy stuff by automating Word, but I don't think it's a beginner topic. =(

[roberto21]

Wow, thank you very much for your interest and suggestions. I unsestannd that there is a lot to be learned.
Yes, I tried with CopyFromRecordset, In my limited understanding, this does not copy the header row, so you have to insert in "manually"

 With rst
        If .RecordCount >= 1 Then
            For iCols = 0 To rst.Fields.Count - 1
                oExcelWrSht.Cells(1, iCols + 1).Value = rst.Fields(iCols).Name
            Next


            oExcelWrSht.Range("A2").CopyFromRecordset rst
        End If

but I can be wrong, of course.
I am intrigued by the report suggestion. What my code does is to fill a welcome letter to new members of our little "elder club", and talking about "letter" what comes to mind is Word, and I did not think of access report. Is really possible to turn the "letter" into a report? Itr is true that all the mailmerge stuff is eliminated, but the formatting?
madpiet: I found this code from dr. Kalla. Is this you are referring to?

Function RidesMergeWord(strDocName As String, _
strDataDir As String, _
Optional strOutDocName As String)
' This code takes a word document that has been setup as a MERGE document.
' This merge document is opened, then mailmerge is executed. The original
' document is then closed. The result is a raw word document with no connectons
' to the merge.txt (a csv source data file).
 
'Parms:
' strDocName - full path name of word doc (.doc)
' strDataDir - dir (full path) where docuemnts and the merge.888 file is placed
' strOutDocName - full path name of merged document (saved).
'
' The above parms are suppled by other routines. You likey should not need to call this
' routine directly. See the sub called MergeNoPrompts.
 
' Albert D. Kallal (c) 2001
' kalla@msn.com
'
 
Dim WordApp As Object ' running instance of word
Dim WordDoc As Object ' one instance of a word doc
Dim strActiveDoc As String ' doc name (no path)
Dim lngWordDest As Long ' const for dest, 0 = new doc, 1 = printer
Dim MyPbar As New clsRidesPBar ' create a instance of our Progress bar.
 
 
MyPbar.ShowProgress
MyPbar.TextMsg = "Launching Word...please wait..."
MyPbar.Pmax = 4 ' 4 steps to inc
MyPbar.IncOne ' step 1....start!
 
On Error GoTo CreateWordApp
Set WordApp = GetObject(, "Word.Application")
On Error Resume Next
 
MyPbar.IncOne ' step 2, word is loaded.
 
Set WordDoc = WordApp.Documents.Open(strDocName)
 
MyPbar.IncOne ' step 3, doc is loaded
 
strActiveDoc = WordApp.ActiveDocument.Name
 
WordDoc.MailMerge.OpenDataSource _
Name:=strDataDir & TextMerge, _
ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=0, _
Connection:="", SQLStatement:="", SQLStatement1:=""
 
 
 
 
With WordDoc.MailMerge
.Destination = 0 ' 0 = new doc
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .datasource
.FirstRecord = 1
' .LastRecord = 1
End With
.Execute Pause:=True
End With
MyPbar.IncOne ' step 4, doc is merged
WordDoc.Close (False)
 
WordApp.Visible = True
WordApp.Windows(WordApp.Windows.Count).Activate
If strOutDocName <> "" Then
WordApp.ActiveDocument.SaveAs strOutDocName
End If
 
MyPbar.HideProgress
 
' AppActivate "Microsoft Word"
WordApp.Activate
WordApp.WindowState = 0 'wdWindowStateRestore
 
Set WordApp = Nothing
Set WordDoc = Nothing
Set MyPbar = Nothing
DoEvents
' If bolShowMerge = True Then
' WordApp.Dialogs(676).Show 'wdDialogMailMerge
' End If
 
Exit Function
 
CreateWordApp:
' this code is here to use the EXISTING copy of
' ms-access running. If getobject fails, then
' ms-word was NOT running. The below will then
' launch word
Set WordApp = CreateObject("Word.Application")
Resume Next
 
End Function

Thank you again to all.

[Welshgasman]

You can set up a report to look like a letter.
Yes, CopyFromRecordset needs the headers added, but that is easier enough to do, as you have seen. Sometimes headers are not required?, as you are using a Template.

[jojowhite]

see the Welcome letter on this demo db.

[moke123]

If all you're doing is using a query or table from Access as a data source for mail merge, why not just use Albert Kallal's "Super Easy Word merge" code? It's older than the hills, but I'd bet it still works. The nice thing is that you can create your templates in Word and then they're not tightly bound to your database anymore. (so if your database gets renamed or moved, Word won't throw a fit.)


Albert!! Where'd your code go??? Looks like that reaaaaaalllly old website may be gone.

It's still around. http://www.kallal.ca/msaccess/msaccess.html