Mail Merge with Merged File Name in Footer

[DM2]

We often have a need to create cover pages for technical documents submitted to clients. Mail Merge to a PDF seems to be the simplest solution. I've read several posts on the subject:



But ended up using a AddIn from "Greg Mayor" (http://www.gmayor.com/individual_merge_letters.htm) as it has provided me the a "best fit" solution.

The AddIn permits me to "...run a macro before saving the documents..." with a caveat that...

In order to ensure that macros applied to this process actually run on the document they must pass a document parameter to the macro e.g. Sub MacroName(oDoc as Document). The variable name itself does not matter. In the following code example, a ® characters are formatted with the Segoe UI font and are superscripted. (The sample document includes such characters so that you may evaluate the results).

...and provides the below example:

Sub FormatTM(oDoc As Document)
     Dim oRng As Range
     Set oRng = oDoc.Range
     With oRng.Find
          Do While .Execute(FindText:=ChrW(174))
               With oRng.Font
                    .name = "Segoe UI"
                    .Superscript = True
               End With
          Loop
     End With
End Sub

Doug Robbins (Word MVP) offered some code to do this (https://answers.microsoft.com/en-us/...f-e67f916252f9), which I've highlighted in red below (with my additions in Blue, and the additional requirements from Greg Mayor, in Green):

Sub FooterTx()(oDoc As Document)
    Dim i As Long, Source As Document, Target As Document, Letter As Range
    Dim fname As Range
    Set Source = ActiveDocument
    With Source
        For i = 1 To .Sections.Count
            Set fname = .Sections(i).Footers(wdHeaderFooterPrimary).Range
            fname.End = fname.End - 1
            Set Letter = .Sections(i).Range
            Letter.End = Letter.End - 1
            Set Target = Documents.Add
            With Target
                .Range.FormattedText = Letter.FormattedText
                .Sections(1).Footers(wdHeaderFooterPrimary).Range.Text = ""
                .SaveAs FileName:=fname.Text & " - Contributions", FileFormat:=wdFormatPDF
                .Close wdDoNotSaveChanges
            End With
        Next i
    End With
End Sub

I know I'm missing the setting the variable "Odoc", but i'm not sure where to place it in the macro.

Doug Robbins code by itself works for me, but not in the Addit.

Anyone's help would be appreciated.

[gmayor]

While my friend Greg Maxey and I have similar initials, we are not the same person and it is he who is 'Greg'.

The macro Doug supplied is not compatible with the add-in process as even if it was, the macro runs before the document filename is created and thus before it is saved so it cannot reflect the name of that document in its footer.

If the aim is to add the filename to the footer then you can get the name of the document (not the PDF file) with

Sub FooterTx(oDoc As Document)
Dim i As Long
Dim fname As Range
    With oDoc
        Set fname = .Sections(1).Footers(wdHeaderFooterPrimary).Range
        fname.End = fname.End - 1
        fname.Collapse 0
        fname.Fields.Add fname, wdFieldFileName, " \p", False
    End With
End Sub

[macropod]

You might be interested in the Send Mailmerge Output to Individual Files topic in the Mailmerge Tips & Tricks 'Sticky' thread: https://www.msofficeforums.com/mail-...ps-tricks.html. The code there allows you to nominate any suitable data field for the file name and, if you follow the instructions in the notes below that topic, the process is entirely transparent to the user. As for having the filename in the footer, all you need do is add a FILENAME field to the mailmerge main document's footer.

[DM2]

While my friend Greg Maxey and I have similar initials, we are not the same person and it is he who is 'Greg'.

Sorry Graham for the misquote...it was late at night when I posted. Thanks for the feedback.

Is there anyway, using your addin, to merge the output with an existing PDF?

As for having the filename in the footer, all you need do is add a FILENAME field to the mailmerge main document's footer.

Paul...I tried that, and maybe it's because of how the Graham's add in works, but all I got on the outputs was "document1..."

I did read your sticky on Mailmerge and again, it was late at night, and my brain was getting lazy.

[macropod]

Originally Posted by DM2

Paul...I tried that, and maybe it's because of how the Graham's add in works, but all I got on the outputs was "document1..."

My reference to the FILENAME field concerns how my process would work, not Graham's addin.

[DM2]

If the aim is to add the filename to the footer then you can get the name of the document (not the PDF file) with

Graham...The aim is to place the output file name in the footer. Based on your comment it doesn't appear as the addin can add the field that holds the output file name (<<filename>>) to the output PDF.

I noticed that when the <<filename>> field is in the body of the mail merge document, the macro you provided, does it's job on the output word document. I also noted that if the <<filename>> is placed in the footer, the macro does NOT add the file name to the output word document.

In both cases the <<filename>> doesn't make it to the PDF as you mentioned.

[DM2]

My reference to the FILENAME field concerns how my process would work, not Graham's addin.

Paul thanks...I'll have a closer look at your Mailmerge Tips & Tricks and give that a try. I'll post any comments using your info, in a separate post.

[gmayor]

Originally Posted by DM2

Graham...The aim is to place the output file name in the footer. Based on your comment it doesn't appear as the addin can add the field that holds the output file name (<<filename>>) to the output PDF.

I noticed that when the <<filename>> field is in the body of the mail merge document, the macro you provided, does it's job on the output word document. I also noted that if the <<filename>> is placed in the footer, the macro does NOT add the file name to the output word document.

In both cases the <<filename>> doesn't make it to the PDF as you mentioned.

The macro adds the field to the document before it is saved and the field will reflect the name of the document. The document is then saved as PDF and the filename in that PDF will be the filename of the Document, i.e. it will have the DOCX extension and not PDF. This will be the same for any document with a filename field and that document is saved as PDF.

The macro inserts a filename field in the footer of the created documents. Any pre-existing filename field in the footer from the merge document will not reflect the names of the documents after the merge and should be removed before running the merge.

The process assumes that you are using the current version of the add-in and that you have entered the macro name on the macro tab.

[macropod]

Originally Posted by DM2

Paul thanks...I'll have a closer look at your Mailmerge Tips & Tricks and give that a try.

I've now added a line of VBA code to the example there to populate the footer prgrammatically.

[DM2]

added a line of VBA code to the example

Paul,
I think you're talking about the below code, which I think is new under the heading "
Send Mailmerge Output to Individual Files"?

      With ActiveDocument
        'Add the name to the footer
        '.Sections(1).Footers(wdHeaderFooterPrimary).Range.InsertBefore StrName
        .SaveAs FileName:=StrFolder & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
        ' and/or:
        .SaveAs FileName:=StrFolder & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
        .Close SaveChanges:=False
      End With

[macropod]

Only:

        'Add the name to the footer
        '.Sections(1).Footers(wdHeaderFooterPrimary).Range.InsertBefore StrName

is new