Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Word VBA

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 04-30-2019, 07:16 PM
DM2 DM2 is offline Windows 7 64bit Office 2010
Novice
 
Join Date: Apr 2019
Posts: 5
DM2 is on a distinguished road
Default Mail Merge with Merged File Name in Footer

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...
Quote:
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:
Code:
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):

Code:
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.
Reply With Quote
  #2  
Old 04-30-2019, 09:01 PM
gmayor's Avatar
gmayor gmayor is offline Windows 10 Office 2016
Expert
 
Join Date: Aug 2014
Posts: 2,721
gmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nice
Default

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

Code:
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
__________________
Graham Mayor - MS MVP (Word)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 05-01-2019, 01:35 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,392
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

You might be interested in the Send Mailmerge Output to Individual Files topic in the Mailmerge Tips & Tricks 'Sticky' thread: http://www.msofficeforums.com/mail-m...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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #4  
Old 05-01-2019, 04:02 AM
DM2 DM2 is offline Windows 7 64bit Office 2010
Novice
 
Join Date: Apr 2019
Posts: 5
DM2 is on a distinguished road
Default

Quote:
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?

Quote:
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.
Reply With Quote
  #5  
Old 05-01-2019, 04:10 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,392
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by DM2 View Post
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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #6  
Old 05-01-2019, 04:28 AM
DM2 DM2 is offline Windows 7 64bit Office 2010
Novice
 
Join Date: Apr 2019
Posts: 5
DM2 is on a distinguished road
Default

Quote:
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.
Reply With Quote
  #7  
Old 05-01-2019, 04:31 AM
DM2 DM2 is offline Windows 7 64bit Office 2010
Novice
 
Join Date: Apr 2019
Posts: 5
DM2 is on a distinguished road
Default

Quote:
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.
Reply With Quote
  #8  
Old 05-01-2019, 05:30 AM
gmayor's Avatar
gmayor gmayor is offline Windows 10 Office 2016
Expert
 
Join Date: Aug 2014
Posts: 2,721
gmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nice
Default

Quote:
Originally Posted by DM2 View Post
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.
__________________
Graham Mayor - MS MVP (Word)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #9  
Old 05-01-2019, 12:12 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,392
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by DM2 View Post
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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #10  
Old 05-01-2019, 03:15 PM
DM2 DM2 is offline Windows 7 64bit Office 2010
Novice
 
Join Date: Apr 2019
Posts: 5
DM2 is on a distinguished road
Default

Quote:
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"?
Code:
      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
Reply With Quote
  #11  
Old 05-01-2019, 03:18 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,392
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Only:
Code:
        'Add the name to the footer
        '.Sections(1).Footers(wdHeaderFooterPrimary).Range.InsertBefore StrName
is new
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
showing mail merge all in one file lezawang Mail Merge 3 05-24-2018 03:10 PM
Log file for mail merge neilclelland Mail Merge 7 12-08-2016 03:08 PM
some records disappear after emailing mail merged file Dr Ennui Mail Merge 1 06-10-2015 04:17 PM
Mail Merge is Deleting objects in my header and footer during the merge bgranzow Mail Merge 9 06-05-2015 05:03 AM
how do i ad a specific pdf file to a mail merge? mseibel Mail Merge 1 08-28-2012 07:53 PM


All times are GMT -7. The time now is 10:07 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft