I have a working Mail Merge Code which is contained in a Module. Then I use the call function to run the mail merge code on the document. Please find the working code below.
Sub MailMergeARDS()
Dim wd As Object
Dim wdocSource As Object
Dim wdInputName As String
Dim strWorkbookName As String
wdInputName = ThisWorkbook.Path & "\A00F200eAuditReferenceDataSheet.docx"
On Error Resume Next
Set wd = GetObject(wdInputName, "Word.Application")
Set wd = GetObject(, "Word.Application")
If wd Is Nothing Then
Set wd = CreateObject("Word.Application")
End If
On Error GoTo 0
Set wdocSource = wd.Documents.Open("E:\Multisite\A00F200eAuditReferenceDataSheet.docx")
strWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name
wdocSource.MailMerge.MainDocumentType = wdFormLetters
wdocSource.MailMerge.OpenDataSource _
Name:=strWorkbookName, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
SQLStatement:="SELECT * FROM `Database$`"
With wdocSource.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultLastRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
wd.Visible = True
wd.Quit
wdocSource.Close SaveChanges:=False
Set wdocSource = Nothing
Set wd = Nothing
End Sub
Display More
Now Here are the issues I am facing.
1) Currently I have created separate set of codes for each word document template (which contains mailmerge fields linked to excel file) by changing filenames in code lines 7 & 17. It would be helpful if a dialog box opens up asking for the Word document template which needs to be used for mail merge.
2) Currently I was not gettion a proper code for saving the resultant merge document and closing the instance of the word application. I was using code line 41. I need a code for the same.
3) Though the Word template uses field codes like \@ dd-MMM-yyyy to force the word to output dates in that format.. it does not seem to work for random dates which appears in several places. I need help in resolving this issue