none
Mail Merge a document using VBA RRS feed

  • Question

  • Hello all

    I have a word document that I want to mail Merge.

    I have to change the data source frequently so I want a macro that would efficiently do this. Can you help me with this.

    Here is what I got already.

    Sub MergeMyDoc.()
    '
    ' Binder Macro
    '
    '
        ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
        ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
        ActiveDocument.MailMerge.OpenDataSource Name:= _
            "C:\Users\HashMan19\Desktop\SampleListing.xlsx", _
            ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
            AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
            WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
            Format:=wdOpenFormatAuto, Connection:= _
            "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\HashMan19\Desktop\SampleListing.xlsx;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLEDB:" _
            , SQLStatement:="SELECT * FROM `LM$`", SQLStatement1:="", SubType:= _
            wdMergeSubTypeAccess
        ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
            , Text:="""F1"""
        ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
            , Text:="""F3"""
        ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
            , Text:="""F3"""
        ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
            , Text:="""F4"""
        
    End Sub

    This is a recorded macro. when I run it,  it produces the placeholder in the active point of the document instead of where I want the merge field to be.

    How do I place my merge field where I want them? and Is there a way to put Inputbox like in excel for the data source drive?


    Thanks to all that continue to serve as help HashMan





    Friday, July 5, 2013 12:35 AM

Answers

  • Hi Hashman

    It's not really clear what you need from us. Your introductory text says one thing, at the end you say something else... It's also a very good idea to post one question / discussion. People might have some ideas about one thing, but not about another and then won't give you any input.

    Going forward on the assumption that the following is the core question in this message:

    <<How do I place my merge field where I want them? >>

    You have to provide more information about what kind of main merge document you're trying to construct. There's also the question whether you always want to be creating the entire document from scratch, or whether you could use a template that contains the basic information and simply link a data source to it, as required?

    Also, which version of Word is involved?


    Cindy Meister, VSTO/Word MVP, my blog

    • Marked as answer by HashMan19_11 Saturday, August 10, 2013 2:19 PM
    Friday, July 5, 2013 3:12 PM
    Moderator
  • Sub PositionMergeFields()
        Dim doc As word.Document
        Dim rngTarget As word.Range
        Dim rngMField1 As word.Range
        Dim rngMField2 As word.Range
        
        Set doc = ActiveDocument
        Set rngTarget = doc.Sections(1).Headers(wdHeaderFooterPrimary).Range
        Set rngMField1 = rngTarget.Paragraphs(1).Range.Duplicate
        rngMField1.MoveEnd wdCharacter, -1
        rngMField1.Collapse wdCollapseEnd
        rngMField1.Select
        doc.Fields.Add Range:=rngMField1, Type:=wdFieldMergeField _
             , Text:="""F1""", PreserveFormatting:=False
        Set rngMField2 = rngTarget.Paragraphs(2).Range.Characters(15)
        doc.Fields.Add Range:=rngMField2, Type:=wdFieldMergeField _
             , Text:="""F2""", PreserveFormatting:=False
    End Sub
    


    Cindy Meister, VSTO/Word MVP, my blog

    Saturday, July 6, 2013 3:09 PM
    Moderator
  • Thanks for the code earlier Cindy. I believe this snippet will help as it has exactly where I want the merge fields. The gray shade X's represent the header text and the black, the normal text on the other part of the document pane.

    Hope the placeholder in the snippet would make my question clearer. 

    Link to the New Post of the refined question.


    Thanks to all that continue to serve as help HashMan




    Saturday, July 6, 2013 7:49 PM

All replies

  • Hi Hashman

    It's not really clear what you need from us. Your introductory text says one thing, at the end you say something else... It's also a very good idea to post one question / discussion. People might have some ideas about one thing, but not about another and then won't give you any input.

    Going forward on the assumption that the following is the core question in this message:

    <<How do I place my merge field where I want them? >>

    You have to provide more information about what kind of main merge document you're trying to construct. There's also the question whether you always want to be creating the entire document from scratch, or whether you could use a template that contains the basic information and simply link a data source to it, as required?

    Also, which version of Word is involved?


    Cindy Meister, VSTO/Word MVP, my blog

    • Marked as answer by HashMan19_11 Saturday, August 10, 2013 2:19 PM
    Friday, July 5, 2013 3:12 PM
    Moderator
  • Thanks Cindy Meister,

    My question is showing you how rusty I am on this thing. I will follow your advice and deal with the first problem. "How do I place my merge field where I want them?"

    I am doing a normal word document mail Merge, but I want to place merge field at a point on the document where I want it.

    Show I repost this question or leave this one.

    Thanks once more Cindy

    HashMan19_11


    Thanks to all that continue to serve as help HashMan

    Friday, July 5, 2013 4:33 PM
  • version of Word, please...

    <<I want to place merge field at a point on the document where I want it.>>

    You need to provide more detail. An example that illustrates what you have in mind. All we can answer to the above is: use the Range object. Which probably means nothing to you and would only make sense in a context. But you have to give us the context so that what we give you will make sense to you.


    Cindy Meister, VSTO/Word MVP, my blog

    Friday, July 5, 2013 4:40 PM
    Moderator
  • Thanks Cindy My problem the proper use of the range object. I header on my document. But I want to insert the merge field on the at the end of the first line and between two words on two more lines.

    Thanks for the guidance. I do not know but I think I need help on the correct Range parameter for the method    ActiveDocument.Fields.Add so that the merge fields will be in the correct positions.

    Thanks


    Thanks to all that continue to serve as help HashMan

    Friday, July 5, 2013 9:17 PM
  • That information brings us a bit further... The tricky part is still identifying the target locations. Are you able to place bookmarks in the source document your macro will be changing? That would be the "simplest" and "cleanest" approach.

    Cindy Meister, VSTO/Word MVP, my blog

    Saturday, July 6, 2013 1:48 PM
    Moderator
  • Thanks for the further help in this problem. I want the first merge field at the end of the first paragraph and the second merge field after the 15th character of paragraph 2.

    Thanks Cindy.

    HashMan19_11


    Thanks to all that continue to serve as help HashMan


    • Edited by HashMan19_11 Saturday, July 6, 2013 2:40 PM Edit the content
    Saturday, July 6, 2013 2:34 PM
  • Sub PositionMergeFields()
        Dim doc As word.Document
        Dim rngTarget As word.Range
        Dim rngMField1 As word.Range
        Dim rngMField2 As word.Range
        
        Set doc = ActiveDocument
        Set rngTarget = doc.Sections(1).Headers(wdHeaderFooterPrimary).Range
        Set rngMField1 = rngTarget.Paragraphs(1).Range.Duplicate
        rngMField1.MoveEnd wdCharacter, -1
        rngMField1.Collapse wdCollapseEnd
        rngMField1.Select
        doc.Fields.Add Range:=rngMField1, Type:=wdFieldMergeField _
             , Text:="""F1""", PreserveFormatting:=False
        Set rngMField2 = rngTarget.Paragraphs(2).Range.Characters(15)
        doc.Fields.Add Range:=rngMField2, Type:=wdFieldMergeField _
             , Text:="""F2""", PreserveFormatting:=False
    End Sub
    


    Cindy Meister, VSTO/Word MVP, my blog

    Saturday, July 6, 2013 3:09 PM
    Moderator
  • Thanks for the code earlier Cindy. I believe this snippet will help as it has exactly where I want the merge fields. The gray shade X's represent the header text and the black, the normal text on the other part of the document pane.

    Hope the placeholder in the snippet would make my question clearer. 

    Link to the New Post of the refined question.


    Thanks to all that continue to serve as help HashMan




    Saturday, July 6, 2013 7:49 PM