Inserting multipage PDFs via MailMerge?

Topic

New Reply

Hello, I am a new member and this is my first post here.

I’m currently assembling submittal sheets for a drawing package. This involves writing up a cover sheet, converting it to pdf, then attaching the manufacturers specsheets (also in PDF) to it, and repeat ad nauseum for about 200 devices.

I am already using a mailmerge to do up the cover sheets. Instead of names and addresses, we’d use manufacturer name, device, name, manufacturer website, etc and then export the mailmerged results as PDF. But, i’m still tasked with having to open up the cover sheet pdfs and manually insert the manufacturers specsheets in with the cover sheets.

I ran across this old thread: http://windowssecrets.com/forums/showthread//146926-Inserting-an-image-via-Mail-Merge?highlight=insert+pictures

I would like to be able to do almost exactly this. But, with mutli-page pdfs, rather than images. Is this possible with word and excel? Would you recommend a different program to do this?

Thanks in advance!

Reply | Quote

Replies

Word has no facility for importing multi-page PDFs or other objects, whether by mailmerge or otherwise; the most you can do is to insert a single page (which will only ever be the first page of a PDF) or insert it as a clickable icon (which isn’t much use for printed material).

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Darn. Thanks for the reply anyway. At least I know I can stop chasing that particular dead end.

If I was to somehow convert a multipage pdf to an image (long vertical length), would word be smart enough to chop it across multiple pages? or would the inserted image simply be squished into one page?

Reply | Quote

No, an inserted object cannot span page boundaries; whatever’s too large to fit at 100% is liable to be scaled down till it does. Consequently, you’d have to break the object up into one-page blocks and insert each one individually. IIRC, inserted PDFs often print poorly too.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

You have choices to solve this problem.

For instance, you could automate Acrobat (if you had it) using VBA. You could use that macro to save the Word coversheet as a PDF and then add the specsheet PDF behind that PDF.

An alternative is to export PDF pages as images (one page = one image) so those images can then be imported into Word as one-page graphics (using the mail-merge functionality).

Do you own Adobe Acrobat? Do you know how to write VBA macros?

Reply | Quote

you could automate Acrobat (if you had it) using VBA.
…
Do you own Adobe Acrobat? Do you know how to write VBA macros?

Be aware that this requires Adobe Acrobat Pro, not just the Reader. The good news is that Adobe has made version 8 of Adobe Acrobat Pro available as a free download.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

You have choices to solve this problem.

For instance, you could automate Acrobat (if you had it) using VBA. You could use that macro to save the Word coversheet as a PDF and then add the specsheet PDF behind that PDF.

An alternative is to export PDF pages as images (one page = one image) so those images can then be imported into Word as one-page graphics (using the mail-merge functionality).

Do you own Adobe Acrobat? Do you know how to write VBA macros?

What we are doing at this moment is using the mailmerge in Word to create the coversheets only. The merged coversheets are then exported as one .pdf. Then in another program we insert the manufacturer pdf specsheets with their matching cover sheet, and save it again. It is then appended to the final drawing package (which is again… in PDF) and submitted to the contractor digitally.

I haven’t coded in VBA at all. To a small degree, I can follow how a program is developed and what it is doing. My experience in coding is with Basic for microcontrollers (mechanical background). A lot of the commands are the same, but instead of pins and voltages, it’s files and objects (I think).

I’ve thought of exporting our current specsheets as images. I’ve had success in getting the tutorial to work from the other thread (inserting images into letter via mail merge). However since our submittal sheets vary in size (1 sheet to as many as 11) I’ve noticed that if an image is blank, it will insert pages with a small error message on them. File not specified or something to that effect. Which could make more work again.

Be aware that this requires Adobe Acrobat Pro, not just the Reader. The good news is that Adobe has made version 8 of Adobe Acrobat Pro available as a free download.

I’ll look into the free download for sure! Thank you.

EDIT: Added note for pdf to images and mailmerge

Reply | Quote

Since your specsheets are PDF and the final output is PDF then I would forget about including the specsheets in the Word document. It is easier to stitch the files together after you turn the Word file into PDF.

The full version of Acrobat (including Acrobat 8) allows you to either merge PDFs and doesn’t require automation to do this quickly.

Reply | Quote

Andrew, that is more or less what we’re doing now. But, we’re doing so by hand.

I’d really like to find a way to automate this in order to avoid stitching together by hand. A very small project with maybe 10-20 devices isn’t too bad. But, on some projects that involve over 200 different devices, stitching that many coversheets and submittals together by hand is time consuming and tedious (as well as creates opportunities for dumb errors)!

Is there a similar tool to mail merge in acrobat that would get me closer to what I need?

Reply | Quote

So are you joining the PDFs using this kind of functionality?
http://www.adobe.com/content/dam/Adobe/en/products/acrobat/pdfs/adobe-acrobat-xi-merge-pdf-files-tutorial-ue.pdf

Whilst you can automate this, the trick will be naming each of the files to allow the macro to work out where each file sits in the order. If you have to customise this file order every time you are doing the task then you might as well just use acrobat’s merge functionality anyway.

Perhaps an easier way is to keep all the PDFs separate and include a mailmerge-supplied hyperlink in each Coversheet which opens the relevant product sheet. The user will still navigate the resulting file(s) logically and your preparation time is minimised.

Reply | Quote

The method you have linked to is, more or less, how i am doing assembling the files now. I open the coversheets (that are in one continuous pdf file) in acrobat then drag and drop the spec sheets into place. The finished pdf assembly is saved and dropped into the drawing package pdf.

The spec sheets are saved locally on our network so we don’t have to go surfing manufacturers websites every time we need them. Because we save them locally and do not move the folders or files around, I had myself myself convinced that there is a way to get these Word or something to do the insertions automatically by using an excel sheet as a type of database. Mail merge was looking promising since it was able to pull up images that were linked, but not anything else.

Does the order change? yes. But a lot of jobs use the same devices. specialized devices aren’t really a big deal either. we’ll typically just add the device and spec sheet as we go.
The cover sheets change for each job also since we display the title of the project on each sheet.

I don’t think the hyperlinks would work out since they wouldn’t be useful to the field staff. We don’t usually print the drawings out for ourselves here in the office, but the contractors sometimes do for the technicians.

Sorry if it sounds like something new is popping each time I reply. I appreciate the amount of effort you’ve given already

Reply | Quote

I think the time-consuming part of this is your dragging pdfs into a specific location in the single document coversheet file. I think it would be faster to export the Coversheet file as separate PDFs and then assemble the complete set in your preferred order where every second file is a coversheet.

You can create a script to merge PDFs in a defined order but we would need to know a lot of information such as what versions of Word and Acrobat you are using and where the information on file order would be coming from and how the merge files are structured etc. Because there are so many variables in getting this to work through a coding solution, it is a less preferred option than the more direct route.

Any solution involving PDFs inserted as graphics into Word is going to be dodgy because the resulting output is unlikely to be word searchable and likely to suffer either image quality and/or file size issues.

Perhaps you could ask the same question on the PDF forums to see if someone has already created a script to merge files in a specific order based on a text input file.

Reply | Quote

Have you looked at IrfanView. It can merge PDFs and in command line mode can use file lists. I use V4.36 and if you look at the Help FAQ & Command Line Options I think you could easily work a solution in conjunction with your Excel speadsheets to create the file lists. You may have to include Ghostscript.

Reply | Quote

Have you looked at IrfanView. It can merge PDFs and in command line mode can use file lists. I use V4.36 and if you look at the Help FAQ & Command Line Options I think you could easily work a solution in conjunction with your Excel speadsheets to create the file lists. You may have to include Ghostscript.

I’m checking out irfanview right now. program seems a bit alien to me but i’ll see what I can coax out of it. Thanks for the suggestion. I have no idea what Ghostscript is :s

IMHO If you are using Word’s .pdf maker you are on a hiding to nothing as MS Word really does not create efficient .pdf’s (it produces really huge files).

You really need to use Adobe Acrobat Pro – or similar such as Foxit or PDF Converter Pro (as suggested elsewhere in this thread) then you can add, subtract, combine, insert links and manipulate .pdf’s in multiple ways with small file sizes.

You might consider creating each ‘customised’ .pdf and then storing each of them in the cloud (http://www.Pogoplug.com gives 5Gb of free secure storage) and then simply providing the correct customised link within each Word document you send out to your end user!

I’m not concerned about filesize at all. Also, it was already mentioned that hyperlinking is not an option. Field techs can’t open hyperlinks on a paper drawing when they’re in the middle of installing an air handling unit.

Reply | Quote

You might be onto a winner there greytech. I am a long-time fan of irfanview (and the squashed cat icon :rolleyes:) but hadn’t thought to suggest it for the task.

I’ve never used it to merge PDFs but if it can do it then that would be worth investigating.

Reply | Quote

IMHO If you are using Word’s .pdf maker you are on a hiding to nothing as MS Word really does not create efficient .pdf’s (it produces really huge files).

You really need to use Adobe Acrobat Pro – or similar such as Foxit or PDF Converter Pro (as suggested elsewhere in this thread) then you can add, subtract, combine, insert links and manipulate .pdf’s in multiple ways with small file sizes.

You might consider creating each ‘customised’ .pdf and then storing each of them in the cloud (http://www.Pogoplug.com gives 5Gb of free secure storage) and then simply providing the correct customised link within each Word document you send out to your end user!

Reply | Quote

CutePDF Pro does a good job of reducing those huge .PDF’s exported by MS Word.

Reply | Quote

If I understand this correctly, you currently use mail merge to create a set of cover sheets, then use “another program” to combine that cover sheet with the manufacturer’s specifications and the drawings into a single PDF file. Presumably you do the combining manually now and the program cannot be automated (or scripted, or batched).

If it were me I would keep the same process but automate it. You can use command line tools like PDF Tools or pdftk (http://www.pdflabs.com/tools/pdftk-the-pdf-toolkit/) to merge PDFs from a batch file. Practice ‘by hand’ till you understand the syntax. pdftk –help gives lots of information. Example:
[INDENT]PDFTK cover1.pdf, specs1.pdf drawing1.pdf output combined1.pdf
[/INDENT]If your mailmerge database is in Excel now (you mentioned Excel but I wasn’t sure whether you were using it at present), simply add another two columns, for the specsheet and drawing filenames. Then on a second sheet, build up the commands: put pdftk in the first column and replicate down, and output in the fifth column. Use Excel links to grab the filenames from your ‘database’ sheet. If necessary, do a bit of combining, for example to add a full path in front of the filenames so they go to the right folder.
Finally, export the second sheet as a text file. Point to File > Save As…. and use ‘Formatted text (space delimited) (*.prn).
Rename the .prn file to .bat and execute it.

You could automate the renaming step with another batch file containing
[INDENT] rename *.prn *.bat[/INDENT]

You could also automate the production of sheet 2 using VBA if you feel up to it

If you are not using Excel as the database you may have to adapt this method a bit to output the batch file automatically.

Reply | Quote

If I understand this correctly, you currently use mail merge to create a set of cover sheets, then use “another program” to combine that cover sheet with the manufacturer’s specifications and the drawings into a single PDF file. Presumably you do the combining manually now and the program cannot be automated (or scripted, or batched).

If it were me I would keep the same process but automate it. You can use command line tools like PDF Tools or pdftk (http://www.pdflabs.com/tools/pdftk-the-pdf-toolkit/) to merge PDFs from a batch file. Practice ‘by hand’ till you understand the syntax. pdftk –help gives lots of information. Example:
[INDENT]PDFTK cover1.pdf, specs1.pdf drawing1.pdf output combined1.pdf
[/INDENT]If your mailmerge database is in Excel now (you mentioned Excel but I wasn’t sure whether you were using it at present), simply add another two columns, for the specsheet and drawing filenames. Then on a second sheet, build up the commands: put pdftk in the first column and replicate down, and output in the fifth column. Use Excel links to grab the filenames from your ‘database’ sheet. If necessary, do a bit of combining, for example to add a full path in front of the filenames so they go to the right folder.
Finally, export the second sheet as a text file. Point to File > Save As…. and use ‘Formatted text (space delimited) (*.prn).
Rename the .prn file to .bat and execute it.

You could automate the renaming step with another batch file containing
[INDENT] rename *.prn *.bat[/INDENT]

You could also automate the production of sheet 2 using VBA if you feel up to it

If you are not using Excel as the database you may have to adapt this method a bit to output the batch file automatically.

Thanks for the reply! I’m looking into these two programs and they look promising already.

The only tedious part is assembling cover sheets and spec sheets. All I do with those afterwards is attach it as an appendix to the rest of the drawings afterwards. Automation for that isn’t necessary.

With the method you are describing, it sounds like i’llhave to save each cover sheet separately and give it a sequential file name (cover1.pdf, cover2.pdf etc). The current spec sheet names are all over the place.

Your steps are pretty clear so i’ll give it a shot when I get some time. I’ll report back with what I get and ask any questions if something strange crops up.

Cheers!

Reply | Quote