Participant

Question

can you mail merge data into a fillable pdf from excel?

22 replies
108468 views
Translate

How can i pre-fill a fillable pdf form from data in an excel spreadsheet and then email each individual their pre-filled form for editing, like you would mail merge in office

This topic is closed to new replies. Start a new post to keep the conversation going.

M

Maciej+S.

Participant

Disclosure: I'm the dev of JustFill mentioned below.

There are two different jobs in your question and I don't know a single tool that does both well.

The filling part (one pdf per excel row) is the easy half nowadays. Mine does it in the browser: upload the form, import the sheet, map columns to fields, preview a row, export the whole thing as a zip. Works with real fillable forms and also with flat ones where it detects the fields on the page: https://justfill.app/solutions/fill-pdf-from-excel - a small batch is free so you can test your exact form first.

The emailing part we deliberately don't do. What works instead: name each generated file after a spreadsheet column (recipient name or email), then let Word/Outlook handle sending - the free Merge Tools add-in by Doug Robbins does mail merge with individual attachments, which stock mail merge can't.

About your "for editing" requirement - careful there. A lot of batch tools flatten the output so the recipient can't type into it anymore, and whether fields survive depends on the tool and on whether the source was a real fillable form. Whatever you pick, run row 1 through it, email the file to yourself and check you can still edit it. If editability after delivery is a hard requirement, AutoMailMerge has a documented keep-form-fields option.

Translate

D

David_Englert5419

Participant

You might want to have a look at Bulk PDF.

https://www.youtube.com/shorts/MOqpvFvOi1k

They also offer a free demo where you can test your use case before you streamline your PDF processing.

Translate

sloonman

Participant

I know this is old but I have been working on this issue for a client recently. I have created a mailmerge application to take a input.txt file (tab delimited excel file) and a form (fillable pdf file) and output to folder. My version saves the files name as the value in a field named BoxF (for a W3 annual report for a client - client name) . This can be modified as needed . I have uploaded the Excel File (input.xlsx) , Form File (form.pdf), Python Script below: Once you have your Python Environment set this should give you the ground work for creating your own mailmerge python scripts.

import pandas as pd
import os
from PyPDF2 import PdfFileReader, PdfFileWriter
import tkinter as tk
from tkinter import filedialog

root = tk.Tk()
root.withdraw()

# Open a file dialog box to select the tab-delimited text file
text_file_path = filedialog.askopenfilename(title = "Select the tab-delimited text file", filetypes = (("Text files", "*.txt"), ("all files", "*.*")))

# Open a file dialog box to select the fillable PDF form
pdf_file_path = filedialog.askopenfilename(title = "Select the fillable PDF form", filetypes = (("PDF files", "*.pdf"), ("all files", "*.*")))

# Open a folder dialog box to select the output folder
output_folder = filedialog.askdirectory(title = "Select the output folder")

# Read in the tab-delimited text file
data = pd.read_csv(text_file_path, delimiter="\t")

# Open the fillable PDF form
pdf_template = PdfFileReader(open(pdf_file_path, "rb"))
fields = pdf_template.getFields().keys()

# Loop through each record in the data
for index, row in data.iterrows():
# Create a new PDF form for each record
pdf_output = PdfFileWriter()
pdf_output.addPage(pdf_template.getPage(0))

# Loop through each field in the record
for field, value in row.items():
# Set the value of the corresponding field in the PDF form
if field in fields:
pdf_output.updatePageFormFieldValues(pdf_output.getPage(0), {field: value})
if "BoxF" in fields:
file_name = pdf_template.getFormTextFields().get("BoxF", "output_{}.pdf".format(index))
else:
file_name = "output_{}.pdf".format(index)
# Save the output as a new PDF file
with open(os.path.join(output_folder, "{}.pdf".format(file_name)), "wb") as f:
pdf_output.write(f)

Translate

Community Expert

You might not have noticed, but this is an Acrobat forum, not a Python forum.

** Note to viewers, the script presented in the previous post has literally nothing to do with Acrobat.

Translate

Thom Parker - Software Developer at PDFScriptingUse the Acrobat JavaScript Reference early and often

T

TysonMA

Participant

This is an old question but I just find out a open source tool that do the job very well (and do it offline):

https://github.com/plainlab/plainmerge

Translate

Thom Parker

Community Expert

This tool is cheap ($28), but it's not free > https://plainlab.gumroad.com/l/plainmerge

That link you gave is to the source code.

Translate

Thom Parker - Software Developer at PDFScriptingUse the Acrobat JavaScript Reference early and often

H

Harlan22835694u59r

Participant

hi I like plainmerge, can it merge in a QR code or an image of a QR code?

Translate

try67

Community Expert

Just a word of warning for anyone using third-party online PDF mergers: Make sure you don't share any sensitive or private information, as you never know who is behind such services and what is done with the data you upload to them!

Translate

S

SBP04

Participant

There is a free tool to do mail mergers like this at pdfzero.com

Translate

K

kim chiv87414909

Participant

AWESOME!! Thank you so much! literraly created 50 PDF's that my client was manually creating in less than 5 mins! I appreciate you sharing this.

Translate

K

kimg91411714

Participant

I know this is three years old, but I came to this thread to figure it out a couple days ago. Ended up figuring it out by myself. If this is something you do regularly, I suggest investing in a tool that will do it for you, especially if there are multiple text field entries like email, phone, first name, last name, etc...

1. Create a Microsoft Excel spread sheet for just one of thise text field categories. For example: an excel spread sheet dedicated to emails.

2. Label each column in the top row email#1, email#2, email#3 and so on until you're done. This means that if you have 100+ emails, you would be naming columns all the way up to email#100.

*This is why I stress that this process is really involved and tedious and I suggest getting a plug-in (several listed below) if you have a long list or have to create these often.

3. Now you put in the emails. Each email gets its own column. Doing it this way gives me the most success.

*When I place every email in one column labeled "emails," this resulted in the data not importing properly

4. Convert the .xlsx file to a tab delimited text (.txt) file. Also be sure to save a .xlsx version.

5. Create your PDF with your fillable text field boxes. Make sure the formatting is even and aligned.

6. Label each email text field box according to the column name. The first Email text field box will be named email#1, the second will be named email#2 and so on.

7. Go to the "Prepare Form" tool. Look for a little tool icon next to the word More. Click the drop-down menu.

8. Import your .txt data by clicking "options" and selecting Text Files. Import

9. Start again with the next text field (names, phone numbers, addresses, etc...)

*I cannot stress how long this process will take. There are tools and resources in this thread, including the link to a Q&A thread that also details how to do a process similar to this.

Best,

Translate

james.goodman

Participating Frequently

I know the post is like 2 years old, however I found a nice tool which get's the job done quite well: www.pdfmailmerger.com

if someone has the same problem...

Translate

P

Peaceful_skill5DE5

Participant

Thanks for updating, came in handy this afternoon!

Translate

alexandraFR

Participant

If you are using Adobe Acrobat and Windows operating system, you can purchase the AutoMailMerge plugin available from EverMap (http://evermap.com/AutoMailMerge.asp), which is specifically designed to do this.

Translate

Thom Parker

Community Expert

There are articles on this topic and scripts here:

https://www.pdfscripting.com/public/ExcelAndAcrobat.cfm

Translate

Thom Parker - Software Developer at PDFScriptingUse the Acrobat JavaScript Reference early and often

Show more replies

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.