Merge to Template?

[Ivan Howard]

Hi All,

I am running a mail merge from an Excel based dataset. There are over 3000 documents (2 pages each) that are to be produced through the merge.

I would like to stop people from editing the documents before printing. I have the code to stop saving, selecting all, copying and cutting, etc, but it only works when in that code is in the particular document.

The code that I created uses the mail merge functionality and successfully creates a NEW separate file for each of the merged documents.

My QUESTION is... is there a way to merge the data into a 'template' which already contains the saving/copying/cutting code?

I don't know too much about Normal.dot - would this be part of the answer?

Thanks for your help.

[macropod]

This question has already been posted at:
http://www.mrexcel.com/forum/showthread.php?t=522902
where a number of solutions have been suggested.

Ivan: For cross-posting etiquette, please see: http://www.excelguru.ca/node/7

The solution I suggested in the other forum, which you have apparently rejected, is to ensure the merged output goes directly to the printer. Perhaps you could explain why that does not meet the objective which you've stated to be:

stop people from editing the documents before printing

[Ivan Howard]

Hi Paul,

Thanks for the post and for all the help.

Sincere apologies for cross-posting… here is the thing:

Before this morning, the only forum I belonged to was MrExcel. As MrExcel does not have a Word section, I initially posted my query in “The Lounge” section, after which I realised that it would better be placed in the “General Excel Discussion & Other Questions” section. I honestly did try to delete the initial post but could find a link to do so.

I was then kindly told by someone else to look at VBA Express as they have a Word section and probably have more Word gurus there. I then took their advice and posted my issue on VBA Express as I wasn’t sure how the people on VBA Express would have seen my post on MrExcel. Obviously some people belong to many forums so would have probably have seen my original post, so I do apologise.

I respect the rules and try to always abide by them… They are there for a reason, but I am sure you can understand how we got into this mess.
There are two reasons why I have also posted on VBA Express… the first being, I was told to and secondly, it was to see what thoughts other people have on the issue… with it being a Word forum and all.

Yes, there have been a few good suggestions on the MRExcel post, but the question that I have asked regarding a merge template has not been answered. All the answers have told me how I can stop certain things happening, and I am truly grateful for all the help. However, none of the (great) ideas given to me tell me if I can merge into a template that holds all these examples of code that people have told me about. Without the documents actually holding the code, I cannot force the output to go to the printer.

There will be over 3000 new files generated (to be distributed via email to over 120 people) by the merge I have… hence the reason for a template to be used, in which I can put your code. I suppose the question should have been about how to get code into 3000+ documents.

I really do appreciate every single answer and suggestion made by everyone on both forums I belong to and apologise for causing such an issue with my question.

[macropod]

Hi Ivan,

With the code I proposed (see below), which goes into your mailmerge main document, there is no need to add any additional code to the output documents. That's because there are none - the mailmerge output simply goes direct to the printer.

Of course, your users could disable the macro in the mailmerge main document, but the same is true of any macro you might insert into a mailmerge output document.

For the benefit of other users here, the code I proposed at MrExcel is:
Sub Mailmerge()
With ActiveDocument.Mailmerge
.Destination = wdSendToPrinter
.Execute
End With
End SubThe above code simply intercepts the 'Mailmerge' execute button and sends the output to the current printer. Using the above code, if your users were to make any changes, they'd have to change the mailmerge main document - and that would affect all the output.

[Ivan Howard]

Thanks very much Paul - much appreciated.

Creating 120+ datasets and then sending out the master mail merge document would not be possible due to the sheer volume and sensitivity of the data.

I have made a note of your code as I will definitely use it in the future.

I will link the MrExcel thread to this one.

Thanks again for all your help.

[macropod]

Hi Ivan,

Why would you need to create 120+ datasets? Assuming your operation has a common server, one dataset on that server should suffice. It's then just a matter of distributing the mailmerge main document.

In any event, I fail to see how this relates to the issue at hand - if your users are going to be running the mailmerge, they'll need access to the data sets whichever way you do it.

The only reasonably secure alternative is to merge the data to pdf files in a central location, then distribute the pdfs to the users for printing.

[Ivan Howard]

Hi Paul,

Thanks for trying to help :beerchug: .

Unfortunately the only server that I work on is secured.

What I need to do is this: I will run the merge and generate the 3000+ documents. They will then go through a checking exercise and then will be sent out to 'controllers'. They will then distribute them to the 120+ people.

PDF was my first choice, but no matter what I do in both Acrobat Writer 9 or in Word, I keep on getting a '5121' error when running the Word VBA that prints the documents via the PDF driver. I even tried running them all to one pdf file and then batch processing the letters into separate files. That didn't work (PDF batch process cant split the document), hence the idea to just use Word but protect each document. The problem with that is that with a Word document people can use Save As or copy and paste and they can then replicate the document and change it.

I have decided to use 3000+ protected word documents with strict instruction that they cannot be changed. Not ideal, but dont really have a choice... unless we can get around the '5121' error? Any ideas?

[macropod]

Hi Ivan,

The run-time error '5121: Word cannot open this document' typically results from:
1. a file extension not being specified when using the FileName argument, such as if you have two files of the same name but different extensions and you tell Word to open the file;
2. the file you're trying to create already exists (eg you're trying to save to a PDF file with the same name as your Word document - again probably without specifying the extension); or
3. a DLL file is missing, or not registered on your computer.

[Ivan Howard]

Thanks very much Paul.

The initial code that I created looped through the merge records and for each record, merged the current record to a PDF file via the print function. Through a series of variables, I created a new filename using merge fields from the record to save it as individual records. I have also attached ".pdf" at the end, so I dont think the output files would be considered to have the same name.

However, do you think that there is a conflict around the actual master merge document itself?

Adobe Acrobat 9 Pro works fine for all other tasks, so I am confident that it is installed correctly.

I have changed the code to just protect and merge to a Word file, so will re-create the 'merge to pdf' code and see if I can get it working.

Thanks again.

[Ivan Howard]

Hi Paul,

I have recreated the 'merge to pdf' code and it looks like this:


With ActiveDocument.MailMerge
.Destination = wdSendToPrinter
.SuppressBlankLines = True
.DataSource.FirstRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
.DataSource.LastRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
.Execute Pause:=False
End With


When stepping through the code, I found that when it gets to the 'Execute' command, that is when it asks the user for a filename. In my previous code, I had tried to save the document after the pdf had been created... so I was actually trying to save the word document - I think anyway.

I have looked in the Object Browser to see what other file/save commands I can use within the "With ActiveDocument.MailMerge" statement, but can't find anything. It probably isnt possible, but I am looking for something along the lines of:


With ActiveDocument.MailMerge
.Destination = wdSendToPrinter
.SuppressBlankLines = True
.DataSource.FirstRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
.DataSource.LastRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
.SaveAs = "Test Document.pdf"
.Execute Pause:=False
End With


Any ideas?

[macropod]

Hi Ivan,

As Word's error message should have alerted you, your 'Destination = wdSendToPrinter' and 'Save As' approach is all wrong. You're telling Word to send the file to a printer and to save it without creating an output document. You really should have addressed those issues instead of assuming you'd have to generate Word documents instead of PDFs.

Assuming you're using Word 2007 or later, you could use:

.Destination = wdSendToNewDocument
...
.SaveAs Filename:="Test Document", Fileformat:=wdFormatPDF
If you're using Word 2003 & earlier, you can either use Acrobat's merge to PDF facility, or you can use Acrobat's PDF 'printer driver' to generate the PDFs.

[Ivan Howard]

Hi Paul,

Yes Word did alert me and I did know that it wouldn't work... I use the 'Compile' checker every time I make a change. The reason why I did it like that is so that you would understand what I am trying to do.

I only changed my mind to produce Word documents because I cannot merge each document to pdf successfully.

I am using 2003 and I have tested the built in merge facility, and I have also tested the print drivers. Both options will only print all 3000+ documents - over 6000 pages into ONE pdf document... As I mentioned before, I need 3000+ separate pdf documents.

In the interest of not wasting anymore of your time, I am going to go with protected word documents only.

Sincerely, thanks very much for taking the time to help me out. I do appreciate it.

[macropod]

Hi Ivan,

You might want to check out the following link, which shows how to go about generating individual letters from a mailmerge, including PDFs using Adobe Acrobat:
http://www.gmayor.com/individual_merge_letters.htm

[Ivan Howard]

Awesome - thanks very much Paul.
:beerchug: