Automate The Conversion of Word Mail Merge Doc into PDF Fillable Form

[robk32632290]

I am new to Adobe Pro.

Every day, I create a unique Word Mail Merge document specific to a dataset for that date. The final document has 2 - 3 records on each page and is printed out to provide a form for staff to complete and have the customer sign.

We are trying to go paperless, and would like to convert these mail merge documents into interactive forms on a tablet. Although new to this application, I think I'll be able to create such manually.

Is it possible to automate the conversion of the mail merge Word document into the PDF fillable form. To do this manually each day for up to 200 records would not be practical.

If its possible, perhaps someone is able to suggest some online resources to consult, or some appropriate search terms.

[try67]

Will the fields always be located at the exact same location on the page? If so, then yes, it can be automated in Acrobat Pro.

However, there's another option. You can create a single editable PDF file and then do the Mail Merge directly on it, using the data from an Excel file, for example. This can be achieved using some versions of the Adobe PDFMaker plugin for Office, or by using a script in Acrobat (like this paid-for one I've developed: http://try67.blogspot.com/2011/09/acrobat-mail-merge-and-email.html)

[robk32632290]

Thank you Try for the encouraging reply.

[quote]Will the fields always be located at the exact same location on the page?[/quote]

Hard to answer this. The mail merge is set up as a directory. Each record occupies a standard template where all fields would be in the same spot in the template, but there may be 2-3 records on a page. So relative to the merge template yes, but page, no. Does that make sense?

I will have to Google Adobe PDF Maker Plugin and tutorials on mail merging to Adobe. I suspect that since my final merged document consists of more than one record normally, it may not be as straight forward.

I will be back if I encounter confusion.

[try67]

Yes, that makes it extra difficult, and possibly impossible to do with the built-in plugin, but a script can be created to do it.

If you're interested in hiring someone to develop it for you feel free to contact me privately via [try6767 at gmail.com].

[robk32632290]

I am at a point now where I want to dig a little bit deeper into this process. My employer just threw Adobe Pro onto my lap with instructions to take a current wasteful paper process into a paperless process with an iPad. Of course, the onus is on me to self educate myself to make it happen.

Here is our current process ...

• Each day we compile a database of "tasks". 
• This database provides the data for a Word mail merge. The merge document is basically a table designed to be a form. The form provides static information (from the database) as well as blank fields for manual input (from staff users incl. a customer signature)
• The mail merge is set up in a manner, to reduce paper, have more than one record represented on a page. So, if there a 9 records processed, there will be 3 pages (3 records per page), rather than 9 pages (of one record per page)
• The the new document (after merging) is printed, distributed to staff for reference, information recorded by staff in the form as required, and customer signature gathered
• All these pages (sometimes upwards of 100+) are returned, reviewed, and recorded staff data compiled into another central database. This is a tediuous manual process that becomes complicated by inconsistent or missing staff documentation on the forms.
• There are certain circumstances in which specific information collected has to extracted and forwarded to another department. This is currently done during the review process and requires that data to be compiled into an email and sent off.
• The paper documents are retained (usually up to half a dozen bankers boxes worth each year) for the established record retention period.

Now, let me express apologies in advance if my expectations / needs are pie in the sky. I know very little of Adobe and I'm sure what I need comes from education and or years of experience. I am not asking anyone to do any of this for me, but really only to comment on the process and whether or not the process can be adapted. I am willing and prepared to focus on self education, but my success will come largely for the input of experienced users to direct me on the concepts I should be reading up on in order to accomplish this. 

Here is what I hope to accomplish

• Continue to compile and prepare data through Excel into a database of static data. Right now, we have a VBA based Excel application that we use to automate the process of preparing this database and launching the different mail merge reports. We will need to continue to use this application to create the database, but need to investigate the best approach to using that database in a merged document for distribution.
• Eliminating the creation of paper mail merged documents, in favour of fillable forms accessible on an iPad. These fillable forms will provide the interface for staff to record information and allow the customer to sign with the iPad pen. Since we are aiming towards paperless, the need to have more than one record on a page is not necessary. Each record can have it's own page, within one document (merged document).
• These mail merge documents (multi page) will continue to be uniquly prepared based on recipient criteria. We currently have 8 different reports used to compile a maximum of 40 different mail merged documents (each report for each of 5 different crews). These new documents would either be emailed to staff or be accessible by staff from the cloud (OneDrive)
• Provide error checking of staff entered information, and the flagging of missing information before submission
• If a certain condition exists based on the input of data by staff, additional information is sought whether through an "unlocked" portion of the existing form, or a sub form.
•  Compilation of data extracted from the fillable forms into a database (Excel; Google Sheets). Ideally it would be great if that resulting database could be stored in the cloud (OneDrive). This would eliminate the human element of compiled information into the final database. It would be awesome if new data coule be appended to the master database.
• If certain conditions exist for a particular record, information is gathered specific to that one record and emailed in a standard email to the department.

So, yes. Perhaps pie in the sky. But, I don't want to use my inexperience to kybosh the possibility. You may also be inthe opinion, with a chuckle, that although doable ... not by this amateur lol.

Anyway, whatever support/critism can be shared will be appreciated support/critism.

[Thom Parker]

If there is some regularity to the template fields, then they can be detected by a script. Try67 mentioned location, but if there are other clues, such as keywords and approximate locations, then it may be possible to write an automation script.

And there are other possibilities. For example, performing the complete mail merge in Acrobat. Since you have a potential dynamic component that will shift the page geometry, then different parts of the document could be built separately, and then combined.

You will of course need a developer for this.