Mailmerge from Excel to premade template in Word

Question

I've written the following code to take data from the Excel file "Data" and use it for a mailmerge in a prepare Word template named "Template." I'm new to using VBA in word or Access, so any feedback in these areas is very welcome

Sub AccessTransfer()

Dim strPath As String
Dim objAccess As Access.Application
Dim strExcelPath As String
Dim lastRow As Long
lastRow = Sheets("Data").Cells(Rows.Count, 1).End(xlUp).row
Dim rng As Range
Set rng = Sheets("Data").Range("A" & lastRow, "M" & lastRow)
strPath = "L:\...\MailMergeTEMPDB.accdb"
Set objAccess = CreateObject("Access.Application")
Call objAccess.NewCurrentDatabase(strPath)
objAccess.Visible = False
strExcelPath = Application.ActiveWorkbook.FullName
Set objAccess = New Access.Application
Call objAccess.OpenCurrentDatabase(strPath, False)
objAccess.Visible = False
Call objAccess.DoCmd.TransferSpreadsheet(acImport, _
acSpreadsheetTypeExcel8, "MailMergeTable", strExcelPath, _
True, Range:="Data!")
Access.Application.CloseCurrentDatabase

Dim wdApp As Object, newDoc As Object
    Dim strFile As String
    strFile = "L:\...Template.docm"
    Set wdApp = CreateObject("Word.Application")
    wdApp.Documents.Open strFile, ReadOnly:=False
    wdApp.Visible = True
    wdApp.Run "Project.Module1.WordMailMerge"
    Kill "L:\....MailMergeTEMPDB.accdb"
End Sub

And then Macro called from within Word is:

Sub WordMailMerge()
Dim strFile As String
strFile = "L:\...MailMergeTEMPDB.accdb"
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        strFile _
        , 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=strFile;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path=""""" _
        , SQLStatement:="SELECT * FROM `MailMergeTable`", SQLStatement1:="", _
        SubType:=wdMergeSubTypeAccess
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
    Windows("AH_MacroMailMergeTemplate").Activate
    ActiveDocument.Close
End Sub

I'm also new to using VBA to navigate trough directories, so feedback in this aspect would be appreciated too.

Answer 1

There's no need to Call anything, it's obsolete. Instead just use Sub argument, argument.

What's this

Kill "L:\....MailMergeTEMPDB.accdb"

Is this closing the access object? Is this deleting the file? Be explicit - Kill isn't something you generally want to see in any code.

FSO.Deletefile strPath