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
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