I recommend against using an Access query as the mail merge source; instead export the query to a delimited txt file (I use currentproject.path to get the local front-end folder).
Here is an example of how I used to do this:
Code:
Public Sub vcKillMailMerge(Optional strFolder As String)
Dim strFileName As String
Dim iFolderCount As Integer
Dim strFolders() As String
Dim i As Integer
Dim strFilePattern As String
On Error Resume Next
If strFolder = "" Then strFolder = Forms![MainSwitchboardForm]![MailMergeFolder]
strFilePattern = "*ACCDB_MailMerge*"
'Collect child folders
strFileName = Dir$(strFolder & "\", vbDirectory)
Do Until strFileName = ""
If (GetAttr(strFolder & "\" & strFileName) And vbDirectory) = vbDirectory Then
If Left$(strFileName, 1) <> "." Then
ReDim Preserve strFolders(iFolderCount)
strFolders(iFolderCount) = strFolder & "\" & strFileName
iFolderCount = iFolderCount + 1
End If
End If
strFileName = Dir$()
Loop
'process files in current folder
strFileName = Dir$(strFolder & "\" & strFilePattern)
Do Until strFileName = ""
'*******************************************
Kill strFolder & "\" & strFileName
'*******************************************
strFileName = Dir$()
Loop
End Sub
Private Sub vcMailMerge()
Dim iFileCount As Integer
Dim strMailMergeFolder as string
strMailMergeFolder = nz(Forms![MainSwitchboardForm]![MailMergeFolder],Application.CurrentProject.Path)
vcKillMailMerge (strMailMergeFolder )
If Right(strMailMergeFolder, 1) <> "\" Then strMailMergeFolder = strMailMergeFolder & "\"
iFileCount = 1
On Error GoTo Error_Kill
Kill_File:
If Len(Dir(strMailMergeFolder & "ACCDB_MailMerge" & iFileCount & ".txt")) > 0 Then Kill strMailMergeFolder & "ACCDB_MailMerge" & iFileCount & ".txt"
GoTo relink
Error_Kill:
iFileCount = iFileCount + 1
GoTo Kill_File
relink:
DoCmd.TransferText acExportDelim, , "Letter Source", strMailMergeFolder & "ACCDB_MailMerge" & iFileCount & ".txt", True
RelinkDocMailMergeText strMailMergeFolder & "ACCDB_MailMerge" & iFileCount & ".txt", strDocument, ReadOnlyMode, True
End SUb
Public Sub RelinkDocMailMergeText_Student(strMailMergeFileName As String, strDoc As String, boReadOnlyMode As Boolean, Optional boEmail As Boolean)
Dim WordApp As Object
Dim retcode
Dim strFileName
'the original sub is a lot bigger as it deals with saving to pdf, emailing , embedded pictures....
Dim strDocumentName As String, sSubject As String, sBody As String
On Error Resume Next
strFileName = strDoc
If Dir(Nz(strFileName, "")) = "" Then
MsgBox strFileName & " was not found! Is it hidden?", vbExclamation, "Document not in this folder!"
Exit Sub
End If
Set WordApp = CreateObject("Word.Application")
With WordApp
.Application.Visible = True
' On Error GoTo telluser
.StatusBar = "Preparing to add a new Mail-Merge document in Word format. Please wait..."
.Documents.Open strFileName, ReadOnly:=boReadOnlyMode , AddToRecentFiles:=False, Revert:=True
.ActiveDocument.MailMerge.OpenDataSource Name:= _
strMailMergeFileName, ConfirmConversions:=False, ReadOnly:= _
False, LinkToSource:=True, Revert:=True, AddToRecentFiles:=False
.ActiveDocument.ActiveWindow.View.ShowFieldCodes = False
.ActiveDocument.MailMerge.SuppressBlankLines = True
.ActiveDocument.MailMerge.ViewMailMergeFieldCodes = False
.ActiveDocument.MailMerge.DataSource.ActiveRecord = -4
.ActiveDocument.MailMerge.Destination = 0 'Merge to a New Document
.Activate
End If
WORD_NOTHING:
End With
On Error Resume Next
DoCmd.SetWarnings True
'WordApp.Quit
Set WordApp = Nothing
Exit Sub
Cheers,