Excel VBA - Call Word to run single Mail Merge with DDE option
Hi all,
I've got a Mail Merge Workbook in Excel where a user copies a version and pastes it in their desired folder. After filling out the "Form" worksheet, the data is populated into a single row within the "Data" worksheet in the same Workbook for a Word Document Mail Merge.
Currently, my code copies the location of a "Plan Doc Template" Word Doc (that users utilize to run the mail merge along with the fields in the Mail Merge Workbook) then pastes the document within the folder that the Mail Merge Workbook copy was placed in. The code also renames the "Plan Doc Template" Word Doc according to fields filled out within the Mail Merge Workbook. This all works fine. The code then opens the newly created "Plan Doc Template" and activates the "Data" worksheet which I assume needs to be open to run the mail merge? If not, I'd like to hide this sheet altogether from the user so they only see the "Form" worksheet which requires their input.
I'm now looking to run the mail merge within the newly copied "Plan Doc Template" Word Doc. I typically have users select the DDE option to get format from Excel which has been working great with the many INCLUDETEXT fields within the "Plan Doc Template" so I'd like to run the mail merge with that DDE option, if possible.
Code:
Sub CopyandRename()
Dim str1 As String
Dim str2 As String
str1 = "Q:\IC\New Structure\IC Toolkit\Templates\01 Plan Doc Template\16 Source\IC Plan Doc Template v1.0.docx"
str2 = Application.ActiveWorkbook.Path & "\" & Range("A1").Value & ".docx"
Call FileCopy(str1, str2)
'Opens New Plan Doc Template
Set appWd = CreateObject("Word.Application")
appWd.Visible = True
appWd.Documents.Open Filename:=Application.ActiveWorkbook.Path & "\" & Worksheets("Form").Range("A1").Value & ".docx"
Worksheets ("Data").Activate
End Sub
Assistance on this would be greatly appreciated,
Rich
