MS Access mailmerge problem

Question

Hi,

I have an Access database which I want to carry out a mailmerge automatically,

I export a table which will only ever contain 1 record at any one time and that record is exported to an xlsx file

I want the database to automatically open the word document and run the mailmerge which has the xlsx file fields set up in it.

The worksheet in the spreadsheet is named Updatedetailsone.

The Word document opens ok but I then get a message box asking which table I want to use Updatedetailsone or Updatedetailsone$.

I want this to be an automatic process which will create the merged Word document.

My code is as follows:

Dim wordApp As Object

Dim wordDoc As Object 

On Error GoTo ErrorHandler 

' Create a new Word application 

Set wordApp = CreateObject("Word.Application") 

wordApp.Visible = True 

' Open the Word template (e.g., "tpl.doc") 

Set wordDoc = wordApp.Documents.Open("C:\xxxxx\Players database\Updates to data\Adult Player Membership Form - textfile.docx") 

' Set up the mail merge data source 

With wordDoc.MailMerge 

    .MainDocumentType = wdMailingLabels 

    .OpenDataSource Name:="C:\xxxxx\Players database\Updates to data\Exportedtable.xlsx", ConfirmConversions:=False, \_ 

        ReadOnly:=False, LinkToSource:=False, AddToRecentFiles:=False 

           .Destination = wdSendToNewDocument 

    .Execute 

    .MainDocumentType = wdNotAMergeDocument 

End With

Answer 1

Hi again,

can anyone help with this please as it is now becoming urgent?

Answer 2

Thanks Sophia for your reply.

I changed my code to :

Dim wordDoc As Object 

On Error GoTo ErrorHandler 

' Create a new Word application 

Set wordApp = CreateObject("Word.Application") 

wordApp.Visible = True 

' Open the Word template (e.g., "tpl.doc") 

Set wordDoc = wordApp.Documents.Open("C:\xxxxx\Players database\Updates to data\Adult Player Membership Form - textfile.docx") 

' Set up the mail merge data source 

With wordDoc.MailMerge 

    .MainDocumentType = wdMailingLabels 

    .OpenDataSource Name:="C:\xxxxx\Players database\Updates to data\Exportedtable.xlsx", \_ 

    ConfirmConversions:=False, \_ 

    ReadOnly:=False, \_ 

    LinkToSource:=False, \_ 

    AddToRecentFiles:=False, \_ 

    Connection:="Updatedetailsone" 

    ' Specify other mail merge settings (e.g., SQLStatement) 

    ' ... 

    .Destination = wdSendToNewDocument 

    .Execute 

    .MainDocumentType = wdNotAMergeDocument 

End With 

ErrorHandler:

' Handle any errors

MsgBox "Error: " & Err.Description

' Clean up 

wordDoc.Close False 

Set wordDoc = Nothing 

Set wordApp = Nothing 

Exit Sub

The code is opening the Word template and then displays this error:

Updatedetailsone is the worksheet name in the spreadsheet Exportedtable.xlsx.

Answer 3

Hi StormingNorman41,

Kindly add 'Connection' argument to MailMerge.OpenDataSource method.

This argument retrieves data from the named range:

With wordDoc.MailMerge
    ' ... some code
    .OpenDataSource Name:="C:\xxxxx\Players database\Updates to data\Exportedtable.xlsx", _
                    ConfirmConversions:=False, _
                    ReadOnly:=False, _
                    LinkToSource:=False, _
                    AddToRecentFiles:=False, _
                    Connection:="Updatedetailsone"
    ' ... some code
End With

MailMerge.OpenDataSource method (Word):

https://learn.microsoft.com/en-us/office/vba/api/word.mailmerge.opendatasource?f1url=%3FappId%3DDev11IDEF1%26l%3Dzh-CN%26k%3Dk(vbawd10.chm153092208)%3Bk(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue 

Best Regards,

Sophia