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
- wdocSource.Close SaveChanges:=False
- Set wdocSource = Nothing
- Set wd = Nothing
- End Sub
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