Results 1 to 3 of 3

Thread: VBA Excel MailMerge to Word

  1. #1
    VBAX Newbie
    Jul 2020

    VBA Excel MailMerge to Word

    Dear guys

    I am making an Excel file with VBA which is allowed me to Find Text in a Word Template file and Replace with correspondent data (table) in Excel like MailMerge then save to docx files.

    I have a code below. When I run it, the docx files do come out with Names as I want but the content of them just keep <<Name>> of the Word Template file without replacing at all. Please see the attached file for details.

    Could you please tell me where should I change in these codes to get Word files replaced with my Data on Excel Files??

    Thank you.

    Sub Test()

    Dim DocRow As Long, DocCol As Long
    Dim i As Long, j As Long
    Dim Template As Object
    Dim t As Object

    DocCol = 8

    DocRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row - 1

    With CreateObject("word.application")
    .Visible = False

    For i = 1 To DocRow
    Set Template = .documents.Open("C:\Test\MailMergeTest.docx")
    Set t = Template.Content
    For j = 1 To DocCol
    t.Find.Execute _
    FindText:=Sheet1.Cells(1, j).Value, _
    ReplaceWith:=Sheet1.Cells(i + 1, j).Value, _
    Template.SaveAs FileName:=ThisWorkbook.Path & Application.PathSeparator & Cells(i + 1, 1).Value & ".docx"
    End With
    Set t = Nothing
    Set Template = Nothing

    End Sub

    Moreover, I am trying to make Excel Replace Text in Word and preserve its Format. Ex: 30,000 or 5%. However, Word just displays as 30000 and 5 instead now.
    Attached Images Attached Images
    • File Type: jpg 1.JPG (164.2 KB, 0 views)
    • File Type: jpg 2.JPG (84.4 KB, 0 views)
    Last edited by milton92; 07-25-2020 at 12:02 PM.

  2. #2
    Why not simply use mail merge? From your description that is what mail merge does.

    With reference to the formatting, Word reads the underlying data from Excel, but you can address that with formatting switches -

    If you want separate documents see or

    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes

  3. #3
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Jul 2008
    You can, of course, run the mailmerge via VBA directly from Excel. See, for example, Run a Mailmerge from Excel, Sending the Output to Individual Files in the Mailmerge Tips and Tricks thread at:
    Paul Edstein
    [Fmr MS MVP - Word]

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