[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Mail merge to print - individual PDFs

Posted on 2006-06-11
8
Medium Priority
?
770 Views
Last Modified: 2012-05-05
Hi,

I have a mail merge to Word which when run will produce x number of 5 page reports.

How can I batch print the reports into individual pdfs with files names based on the value of one of the merge fields.

I imagine I could use a VBA macro to do this. Any ideas?

Thanks
0
Comment
Question by:oranco
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 16880466
I am unclear about how one prints to a PDF, but I think that the simplest way would be to do the mailmerge to a new document and to run a macro that splits out each report and saves or prints it. The splitting bit is straightforward, but the naming part would depend on being able to locate the name on the result document. Hopefully this would always be something like, say,  the third word in the second paragraph.

Which version of Word are you using?
0
 
LVL 1

Author Comment

by:oranco
ID: 16880491
hi,

we are using Word 2003.

The printing to PDF would be done using a batch command in Adobe Acrobat Distiller but if we are able to break a long document down into individual files, we should be ok.

How can we do it - bear in mind that i am not sure at this point if we have 4, 5 or 6 pages so it should be clear in the VBA where this needs to be set. The naming of the document would come from one of the merge fields.

Thanks
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 16880782
Sticking with Plan A, the code would look like this.

Sub SplitMMResultDoc()
    Dim Doc1 As Document
    Dim Doc2 As Document
    Dim strFileName As String
    Dim rng As Range
    Dim sec As Section
     
    Set Doc1 = ActiveDocument
    For Each sec In Doc1.Sections
        Set rng = sec.Range
        rng.MoveEnd wdCharacter, -1 ' omit the seciotn break
        rng.Copy
        strFileName = sec.Range.Paragraphs(2).Range.Words(3)
        Set Doc2 = Documents.Add
        Doc2.Range.Paste
        Doc2.SaveAs ("C:\MyFolder\" & strFileName & ".doc")
        Doc2.Close False
    Next sec
     
End Sub


If we can't be sure of the position of text from the merge field that provides the name, then Plan B will be to run the merge itself programatically.
0
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
LVL 1

Author Comment

by:oranco
ID: 16880837
thx... so i have create a doc with 8 pages. Each page contains the following text:

'Hello , I'm a test recording

Name is <name>'

When i run the macro tho i get an error 'the requested member of the collection does not exist' referring to:

strFileName = sec.Range.Paragraphs(2).Range.Words(3)

where am i going wrong?
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 16881428
Sorry about the delay. I had to go out.
I can see an error, but not what I would expect.
It looks as if you have a third word in the second paragraph, do it's a bit puzzling.  
This code splits the line up, to help isolate the problem.

Sub SplitMMResultDoc()
    Dim Doc1 As Document
    Dim Doc2 As Document
    Dim strFileName As String
    Dim rng As Range
    Dim sec As Section
    Dim para as Paragraph
    Dim wrd as range
    Set Doc1 = ActiveDocument
    For Each sec In Doc1.Sections
        Set rng = sec.Range
        rng.MoveEnd wdCharacter, -1 ' omit the seciotn break
        rng.Copy
        Set Para = sec.Range.Paragraphs(2)
        Set wrd = para.Words(3)
        strFileName = wrd.text
        Set Doc2 = Documents.Add
        Doc2.Range.Paste
        Doc2.SaveAs ("C:\MyFolder\" & strFileName & ".doc")
        Doc2.Close False
    Next sec
     
End Sub
0
 
LVL 1

Author Comment

by:oranco
ID: 16883348
Morning,

So now when I run it I get a 'compile error: method or data member not found' - and it gets stuck on Set wrd = para.Words(3)

0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 2000 total points
ID: 16883517
Sorry, I got a bit pushed for time yesterday, so I failed to test the code.

I had missed out the .Range method to get the Words collection.

Sub SplitMMResultDoc()
    Dim Doc1 As Document
    Dim Doc2 As Document
    Dim strFileName As String
    Dim rng As Range
    Dim sec As Section
    Dim para As Paragraph
    Dim wrd As Range
    Set Doc1 = ActiveDocument
    For Each sec In Doc1.Sections
        Set rng = sec.Range
        rng.MoveEnd wdCharacter, -1 ' omit the seciotn break
        rng.Copy
        Set para = sec.Range.Paragraphs(2)
        Set wrd = para.Range.Words(3)
        strFileName = wrd.Text
        Set Doc2 = Documents.Add
        Doc2.Range.Paste
        Doc2.SaveAs ("C:\MyFolder\" & strFileName & ".doc")
        Doc2.Close False
    Next sec
     
End Sub
0
 
LVL 1

Author Comment

by:oranco
ID: 16883895
thanks. that's great.

we got it working and added another couple of lines to get it to print to pdf.

brilliant.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is written from a 'VBA for MS Word' perspective, but I am sure it applies to most other MS Office components where VBA is used.  One thing that really bugs me is slow code, ESPECIALLY when it's mine!  In programming there are so many ways to…
This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question