Excel VBA to automate a Word Doc for Mail Merge

[larryjfoster]

I have an excel workbook where the first sheet is a summary of all accounts and each sheet after is a summary per account. I'd like to automate a mail merge with existing templates already established for the merge. The code below successfully opens the appropriate Word template(s) but says that Word could not open the data source. I suspect the error lies in the (SQLStatement:="SELECT * FROM `'` & account & `$'`",) portion of the code but I'm not sure how to correct it.

Sub mbrMailMerge()
Dim Sheet As Worksheet, wsName$, N&
Dim dataSrc As String
Dim hDir As String                      'main folder housing all templates
Dim account As String

dataSrc = ActiveWorkbook.FullName
hDir = "C:\folder\subFolder01\"

'Open the mail merge letter template
Dim wdDoc As Object
For N = 2 To Sheets.Count
    Sheets(N).Activate
    wsName = ActiveSheet.Name
    account = ActiveSheet.Name
        If wsName = "account01" Then
            Set wdDoc = GetObject(hDir & "subFolder02\subFolder03\account01.docx", "Word.document")
            wdDoc.Application.Visible = True
        ElseIf wsName = "account02" Then
            Set wdDoc = GetObject(hDir & "subFolder02\subFolder03\account02docx", "Word.document")
            wdDoc.Application.Visible = True
        'ElseIf wsName = "account03 Then
            'Set wdDoc = GetObject(hDir & "subFolder02\subFolder03\account03docx", "Word.document")
            'wdDoc.Application.Visible = True
        Else
            MsgBox "Could not find " & wsName & " Member Word Doc for Mail Merge. Please complete manually"
            'Go to next iteration of N in For loop
        End If
        
    ' select datasource and complete mail merge
    With wdDoc.MailMerge
        .OpenDataSource Name:=dataSrc, _
            ConfirmConversions:=False, _
            ReadOnly:=False, _
            LinkToSource:=True, _
            AddToRecentFiles:=False, _
            PasswordDocument:="", _
            PasswordTemplate:="", _
            WritePasswordDocument:="", _
            WritePasswordTemplate:="", _
            Revert:=False, _
            Format:=wdOpenFormatAuto, _
            Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=dataSrc;Mode=Read;Extended Properties=""HDR=YES;IME", _
            SQLStatement:="SELECT * FROM `'` & account & `$'`", _
            SQLStatement1:="", SubType:=wdMergeSubTypeAccess
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
        .Destination = wdSendToNewDocument
        .MainDocumentType = wdNotAMergeDocument
    End With
    
    ' show and save output file
    ' cleanup
    wdDoc.Close SaveChanges:=False
Next
Set wdDoc = Nothing
End Sub

*I'm building this code as I go and resolving issues along the way so you may see other inconsistencies that I have yet to consider but I can't get passed this issue so any help will be much appreciated.

 

[larryjfoster]

I've moved this question to the appropriate place Forum
Question Forums
Excel Questions

 

[Macropod]

Duplicate of: Automating Word Mail Merge with Excel 2010 VBA
Please read Mr Excel's policy against Duplicate Posting in rule #12: Forum Rules
Thread closed.