Consulting

Results 1 to 3 of 3

Thread: Excel VBA - Call Word to run single Mail Merge with DDE option

  1. #1

    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.

    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

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Cross-posted at: http://www.msofficeforums.com/word-v...le-create.html
    Please read VBA Express' policy on Cross-Posting in item 3 of the rules: http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    Thanks Paul, my apologies.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •