Consulting

Results 1 to 3 of 3

Thread: VBA Excel MailMerge to Word

  1. #1
    VBAX Newbie
    Joined
    Jul 2020
    Posts
    1
    Location

    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.

    Code:
    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, _
    Replace:=wdReplaceAll
    Next
    Template.SaveAs FileName:=ThisWorkbook.Path & Application.PathSeparator & Cells(i + 1, 1).Value & ".docx"
    Next
    .Quit
    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, 4 views)
    • File Type: jpg 2.JPG (84.4 KB, 4 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 - https://www.gmayor.com/formatting_word_fields.htm

    If you want separate documents see https://www.gmayor.com/MergeAndSplit.htm or https://www.gmayor.com/email_merge_addin.html



    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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: https://www.msofficeforums.com/mail-...ps-tricks.html
    Cheers
    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
  •