Mail Merge and Create individual Files

[Iamrickdeans]

:banghead:I thank you all in advance for any help you can provide me, I am looking to perform a mail merge from access to Publisher. I have linked everything together and got everything looking the way that I want it to look however...
What I want to do is when I perform the mail merge I would like publisher to save a copy of each individual record from the database as a specific file based on one field of the mail merge.
I will try to clarify...
One of the mail merge fields is ClientAccountNumber I would like the merge to save a copy of the publisher file into c:\mailmerge as a PDF including the year 2014.
So this file(s)for example would look like
C:\mailmerge\123456 - 2014.pdf C:\mailmerge\123457 - 2014.pdf etc...
Does anyone know if this is possible and if so how I would go about it?
Regards
Richard

Sub MailMerge() 
 '
 ' Macro1 Macro

Dim TempDocCreate 
Dim FileNameTemp As MailMergeDataField 


Set FileNameTemp = Application.ActiveDocument.MailMerge.DataSource.DataFields.Item("Box 22 Rcp Acct No") 


With ActiveDocument.MailMerge.DataSource 
    Application.ActiveDocument.ExportAsFixedFormat pbFixedFormatTypePDF, Filename:= _ 
    "L:\Operations Database\Projects\1042\PublisherPDF\2011 Merge\" & "FileNameTemp" & " - 2011" & ".pdf" 


End With

I need help with solving this absolutely baffled, confounded and almost delusional!!!!


Please please help I will provide as much detail as any needs if you can help me with this and will clarify any point or information required. I am at the limit of my knowledge with this one.

 

[Tiger955]

Hi Richard!

Sorry, I did not understand what you will do.
I will try to understand.

ClientAccountNumber is a field in an access table?
There are some files produced in Publisher as PDF and you want to merge those PDF's (more than one per ClientAccountNumber) to only one per ClientAccountNumber ?

As your question can be found in some other forums as well and no one seems to understand exactly what the workflow from application to application is, pls try to describe the workflow more detailed.


Michael

 

[Iamrickdeans]

Hi Michael

Thanks for looking at my post! I am still trying to work it out and just cannot get it!

I have an access database located in L:\MailMerge\MailMerge.accdb
Within that are Three tables;
2011, 2012, 2013

each of these tables are identical in setup and each contains a field called ClientAccountNumber if it helps it is a 'text' field.

Each client account number is unique.


In publisher I have a mail merge set up which at this moment in time is pointing at the '2011' table.

My aim is to have the 'publisher' mail merge start with the first record or first client account number in the '2011' table and then export the merged data to a PDF document.

The PDF document has to be named with the relevant data from the 'ClientAccountNumber' field.

I hope this helps? If you need any more clarification please let me know!
I won't put any more code up as I have wrapped myself in knots with this and cannot for the life of me work it out.

If you or anyone else out there can work out how this is done I would be infinitely grateful!

Regards

Richard

 

[Tiger955]

Well, first I doubt, that your data model is optimal, as for me ONE table with 'ClientAccountNumber' and a field Salesyear (dont use the field name 'year' only) seems the better solution.

But then, whatfor you use a mailmerge in publisher?
What kind of data are in the publisher files which are not in the access table?

Do you want to merge some publisher files together to ONE PDF?? and name this merged PDF for example '12345767-2011.pdf'?
Is this your target?

If so, I would defintively take another approach.
I still do not know HOW publisher is involved in your problem.
Michael

 

[Iamrickdeans]

Hi Again

To be honest I do not know if publisher is the right tool for the job I will try to illustrate further.

I am doing a tax return for the IRS the file is a 1042s the file is a PDF of which only three parts of this file are required to be split out and then sent to our customers.

I have split out the three parts of the PDF and used publisher to ensure that their legibility and alignment is perfectly correlated with the electronic files submitted.

From there as the document was as I required it to look I simply created the mail merge directly within publisher as it seemed at the time to make little sense to move it into another programme.


What I need to do is perform a mail merge that simply takes creates individual PDF documents named on the Customer Account Number so that I can then script it to upload to each individual clients accounts.

VBA, and the MS - Office files are not what I am experienced with so I have no idea what the best way to perform a mail merge to approximately 10K individual PDF's are.

I really, really need to find a solution though.

Thoughts?

 

[Tiger955]

Well, as I stil do not know, what exactly ist going on, I try to sum up what I understood.

You have allready three single PDF out of one file, lets say called, PDF1.pdf, PDF2.pdf, PDF3.pdf.

AFter checking something you want to combine all three PDF again and name it with the 'ClientAccountNumber', which is stored in an access table and then "link" the one PDF to the 'ClientAccountNumber'.

Is this what you want to do?

 

[Iamrickdeans]

Thanks for your help I have managed to solve the problem but I am not near the source code now to post my coded solution.

My process became,
Take the original PDF
Decompile into TIFF format so each page was an individual document
Compile only the required images into a new singular TIFF file.
Embed the TIFF file into a word document.
Bind approximately 300 text boxes to the document over the TIFF image to contain each of the salient merge fields with transparent border and backgrounds.
Place each of the merge fields into the required now hidden text boxes.

Wrote a word macro based on the data source to preview each merge document and export all data as a new PDF document using the client reference as the document title.

Further loop to kill the temporary word document that was create

Loop again through all records in range

Sat back and waited....


Painful in extremis however the exercise has been accepted and successful.

Scripted server side and uploaded to my clients...

Thank you for your interest and reply!

Kind regards

Richard