Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Mail Merge

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 12-21-2018, 07:57 AM
bearcublandon bearcublandon is offline Windows 10 Office 2016
Novice
 
Join Date: Dec 2018
Posts: 24
bearcublandon is on a distinguished road
Default Macro to convert Mail Merge Document to PDF

I have 15 to 20 individual Word Documents that contain over 500 documents that need to be converted to individual PDF files. These documents go to the employees for their annual Stock grants.



I have already linked Word to my Excel file and created all the individual documents. I am now ready to print them to a individual PDF files.

One file may contain 130 individual documents. Each separate word document is dependent upon the number of stocks they are granted (e.g. 1,2,3,4,etc).

One merged Word document may contain 150 separated documents (these are for folks who are granted 1 fund) or 1 document (13 granted stocks).

The idea was to use a DocuSign tool that will split the merged Word document into individual PDF documents. How, we need to create a unique identifier to let the tool tool know when a new document ends or a new one begins.

Is there a macro or add in tool that will print the existing mail merged word documents into a PDF?

I don't need to create the links - this is already done. I am ready to print the files.

I now just need to convert the completed mail merge file to individual PDFs.

If I had all he email addresses could I also automatically create emails for them or would I have to do it individually?

Thank you for your help,

Michael
Reply With Quote
  #2  
Old 12-21-2018, 03:32 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,098
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

See Send Mailmerge Output to Individual Files in the Mailmerge Tips and Tricks 'Sticky' thread at the top of this forum:
http://www.msofficeforums.com/mail-m...ps-tricks.html
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 12-21-2018, 03:50 PM
bearcublandon bearcublandon is offline Windows 10 Office 2016
Novice
 
Join Date: Dec 2018
Posts: 24
bearcublandon is on a distinguished road
Default

thank you for the tips. I"ll definitely study this over the holidays.

Of the several codes you have available there which one would apply to my situation where I have the word document already merged and ready to print but I want to print them to individual PDF files.

Thank you for your help,

Michael
Reply With Quote
  #4  
Old 12-21-2018, 05:02 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,098
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

For an existing mailmerge output document, you'd use the approach described under Split Merged Output to Separate Documents.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 12-21-2018, 05:30 PM
bearcublandon bearcublandon is offline Windows 10 Office 2016
Novice
 
Join Date: Dec 2018
Posts: 24
bearcublandon is on a distinguished road
Default

I ran this following code but nothing happened. I did get a message box "How many Section breaks are there per record?"

Each employee's document consists of 35 pages. Is this a section break or is this total number of employees I have records for in the document.
Code:
Sub SplitMergedDocument()
' Sourced from: http://www.msofficeforums.com/mail-m...ps-tricks.html
Application.ScreenUpdating = False
Dim i As Long, j As Long, k As Long, StrTxt As String
Dim Rng As Range, Doc As Document, HdFt As HeaderFooter
Const StrNoChr As String = """*./\:?|"
j = InputBox("How many Section breaks are there per record?", "Split By Sections", 1)
With ActiveDocument
   ' Process each Section
  For i = 1 To .Sections.Count - 1 Step j
    With .Sections(i)
       '*****
       ' Get the 1st paragraph
      Set Rng = .Range.Paragraphs(1).Range
      With Rng
        ' Contract the range to exclude the final paragraph break
        .MoveEnd wdCharacter, -1
        StrTxt = .Text
        For k = 1 To Len(StrNoChr)
          StrTxt = Replace(StrTxt, Mid(StrNoChr, k, 1), "_")
        Next
      End With
       ' Construct the destination file path & name
      StrTxt = ActiveDocument.Path & Application.PathSeparator & StrTxt
       '*****
       ' Get the whole Section
      Set Rng = .Range
      With Rng
        If j > 1 Then .MoveEnd wdSection, j - 1
         'Contract the range to exclude the Section break
        .MoveEnd wdCharacter, -1
         ' Copy the range
        .Copy
      End With
    End With
     ' Create the output document
    Set Doc = Documents.Add(Template:=ActiveDocument.AttachedTemplate.FullName, Visible:=False)
    With Doc
       ' Paste contents into the output document, preserving the formatting
      .Range.PasteAndFormat (wdFormatOriginalFormatting)
       ' Delete trailing paragraph breaks & page breaks at the end
      While .Characters.Last.Previous = vbCr Or .Characters.Last.Previous = Chr(12)
        .Characters.Last.Previous = vbNullString
      Wend
       ' Replicate the headers & footers
      For Each HdFt In Rng.Sections(j).Headers
        .Sections(j).Headers(HdFt.Index).Range.FormattedText = HdFt.Range.FormattedText
      Next
      For Each HdFt In Rng.Sections(j).Footers
        .Sections(j).Footers(HdFt.Index).Range.FormattedText = HdFt.Range.FormattedText
      Next
       ' Save & close the output document
      .SaveAs FileName:=StrTxt & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
       ' and/or:
      .SaveAs FileName:=StrTxt & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
      .Close SaveChanges:=False
    End With
  Next
End With
Set Rng = Nothing: Set Doc = Nothing
Application.ScreenUpdating = True
End Sub
Where do I specify where to say the document?

Thank you for your help,

Michael
Reply With Quote
  #6  
Old 12-21-2018, 05:31 PM
bearcublandon bearcublandon is offline Windows 10 Office 2016
Novice
 
Join Date: Dec 2018
Posts: 24
bearcublandon is on a distinguished road
Default

Clarification: Is the 35 pages considered the Section break or does that message refer to the number of people that are contained in the merged file? Not sure, thank you
Reply With Quote
  #7  
Old 12-21-2018, 05:52 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,098
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

The prompt regarding Sections is because the mailmerge main document from which the output records are generated may itself have contained Section breaks (e.g. where some pages are in portrait format, whilst others are in landscape format). Each record will consist of at least one Section - you need to tell Word how many Sections each record has, not how many records or pages per record there are.

As the comments relating to that topic say:
Quote:
As coded, it is assumed the output filename consists of the first paragraph in each record.
If you're getting 0 output, that suggests your mailmerge main document starts with an empty paragraph - and so do each of the output records - in which case you need to change the '1' in:
Set Rng = .Range.Paragraphs(1).Range
to point to the paragraph that is to be used for the filenames - or you can use the numbered file approach outlined in the topic's comments.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #8  
Old 12-21-2018, 06:40 PM
bearcublandon bearcublandon is offline Windows 10 Office 2016
Novice
 
Join Date: Dec 2018
Posts: 24
bearcublandon is on a distinguished road
Default

I'm attaching a file with the header for one of them.

thank you for all your help on this. It is going to save us a lot of time if I can get this to work properly.

Michael

Happy holidays
Attached Images
File Type: png Format.PNG (12.2 KB, 13 views)
Reply With Quote
  #9  
Old 12-21-2018, 08:13 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,098
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

Your attachment is just a screenshot, not a document. I'd need a document for this and you'd have to say what part is supposed to be used for the filename.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #10  
Old 12-22-2018, 08:31 PM
bearcublandon bearcublandon is offline Windows 10 Office 2016
Novice
 
Join Date: Dec 2018
Posts: 24
bearcublandon is on a distinguished road
Default

Understood. However, I won't be able to get back to office until Jan 2 and i would like to be able to run it so I can convert the files to PDF then.

In the screensheet, the name file doesn't start until line 5 (two lines below "award").

How would I amend your code to have it look at the "Name of Participant" line for the name of the new PDF?

Do you need an entire document because i could probably make one from scratch but I wouldn't be able to provide the other 34 pages in the document.

Thank you for your help,

Michael
Reply With Quote
  #11  
Old 12-22-2018, 09:39 PM
gmayor's Avatar
gmayor gmayor is offline Windows 10 Office 2016
Expert
 
Join Date: Aug 2014
Posts: 2,670
gmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nice
Default

It might help to look at
http://www.gmayor.com/MergeAndSplit.htm
__________________
Graham Mayor - MS MVP (Word)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #12  
Old 12-22-2018, 09:54 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,098
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 bearcublandon View Post
How would I amend your code to have it look at the "Name of Participant" line for the name of the new PDF?
For that, you'd need to change:
Set Rng = .Range.Paragraphs(1).Range
to, say:
Set Rng = .Range.Paragraphs(5).Range
and change:
StrTxt = .Text
to:
StrTxt = Split(.Text,":" & vbTab)(1)
but, as I said, I'd need to see an actual document to be sure.
Quote:
Originally Posted by bearcublandon View Post
Do you need an entire document because i could probably make one from scratch but I wouldn't be able to provide the other 34 pages in the document.
A single page containing everything down to at least the "Name of Participant" line in your screenshot would do.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #13  
Old 12-23-2018, 09:10 AM
bearcublandon bearcublandon is offline Windows 10 Office 2016
Novice
 
Join Date: Dec 2018
Posts: 24
bearcublandon is on a distinguished road
Default

Thank you Peter.

So the Range. Paragraph object referred to in the code refers to a line on the document (line the row object in Excel). Since the Employee name is on line 5 I'll modify the code from (1) to (5) per your suggestion.

How does the code know to create a new PDF for each employee (just curious).

I'll create a dummy couple of pages and send this to you later today or tomorrow. Thank you for your help on this, you're a life saver!

Michael
Reply With Quote
  #14  
Old 12-23-2018, 02:19 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,098
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 bearcublandon View Post
Thank you Peter.
Peter???
Quote:
Originally Posted by bearcublandon View Post
So the Range. Paragraph object referred to in the code refers to a line on the document (line the row object in Excel).
No, it actually refers to a paragraph. It's impossible to tell from your screenshot what paragraph # the desired content is in. For all I know at this stage, all the heading content is just one paragraph and the desired content is in paragraph 2.
Quote:
Originally Posted by bearcublandon View Post
How does the code know to create a new PDF for each employee (just curious).
It puts that part into the StrTxt variable, then uses that variable's content for the filename in the SaveAs code.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #15  
Old 12-23-2018, 02:47 PM
bearcublandon bearcublandon is offline Windows 10 Office 2016
Novice
 
Join Date: Dec 2018
Posts: 24
bearcublandon is on a distinguished road
Default

Sorry Paul, I meant to write Paul, not Peter.

The header is the first paragraph, their isn't a blank line until the 4th row. The second paragraph starts with Participant ...

I'll create a document for you following the format I recall. I'll also create a dummy second sheet so it will print more than one.

Thank you,

Michael
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert particular blocks of text within a word document to tables using macro benbob Word VBA 5 07-15-2018 03:20 AM
Mail Merge Macro ch1325 Word VBA 2 06-08-2015 06:18 AM
Mail Merge Macro spc94 Word VBA 2 06-04-2015 07:06 AM
Convert mail merge to PDF then email TeriJean Mail Merge 0 10-04-2011 03:52 PM
Word Doc Macro (mail Merge) ajolson1964 Word VBA 1 05-10-2011 10:15 PM


All times are GMT -7. The time now is 04:28 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