Work Automation – Bulk letter generator and print out

The creating of bulk documents and printing them out is probably something a few of us do, whether it be part of your 9-5 or even as a form of outreach for your own project/business. As part of this project we will be creating a Python script that will make use of an Excel spreadsheet that will contain a list of recipients i.e. their names and addresses. The script will fetch this data and merge it in to a word document before printing it.

Before we start ensure that you have a list of recipients saved to an xlsx file, a word document with the relevant merge fields added and your default printer set appropriately. You will not be able to define the printer with this script and it will revert to the one that is set to your default.

we start by opening the Excel spreadsheet via Openpyxl and define the sheet name.

wb = openpyxl.load_workbook('C:/Desktop/Spreadsheet.xlsx')
sheet = wb["Sheet"]

With the sheet now open we will iterate through all entries and merge them to the word document and save a copy of each file. The individual files in this case will be named after the recipients name.

for i in range(2,sheet.max_row+1):
        MergeDoc = "C:/Desktop/Letter Template.docx"
        document = MailMerge(MergeDoc)
        document.merge(
        Name = str(sheet.cell(row = i, column = 1).value),
        Address = str(sheet.cell(row = i, column = 2).value))
        document.write("C:/Desktop/"+str(sheet.cell(row = i, column = 1).value)+" Letter.docx")

Now that we have all the files saved its time to send them off to the printer, in my experience to avoid my machine locking up, I like to introduce a small delay between each print – Feel free to remove this if an inconvenience.

os.startfile(("C:/Desktop/"+str(sheet.cell(row = i, column = 1).value)+" Letter.docx"),"print")
        time.sleep(3)

Full source code for the project can be found below.

from docx import Document
from mailmerge import MailMerge
from datetime import date
from openpyxl import Workbook
from win32com import client
import openpyxl
import os
import time

wb = openpyxl.load_workbook('C:/Desktop/Client List.xlsx')
sheet = wb["Sheet"]
for i in range(2,sheet.max_row+1):
        MergeDoc = "C:/Desktop/Letter Template.docx"
        document = MailMerge(MergeDoc)
        document.merge(
        Name = str(sheet.cell(row = i, column = 1).value),
        Address = str(sheet.cell(row = i, column = 2).value))
        document.write("C:/Desktop/"+str(sheet.cell(row = i, column = 1).value)+" Letter.docx")
        os.startfile(("C:/Desktop/"+str(sheet.cell(row = i, column = 1).value)+" Letter.docx"),"print")
        time.sleep(3)

Leave a Reply