Tech news 11: Facilitating… admin tasks
Filling in PDF forms and sending personalised emails
What for?
Maybe you want to pre-fill travel requests for all members of your group? Maybe you want to send a personalised email to all of your 200 co-authors, data providers, citizen science participants or parliament representatives? Maybe you want to send exam subjects to students but the data have to be different and randomly affected?
A case study
While our manuscript was under review at GEB we decided to offer co-authorship to data providers that had not been contacted before because their data were open access. Why should they not be proposed to be coauthors while we propose it to people whom we had to contact to get access to their data, right?
GEB then asked us to send the the new list of co-authors to all co-authors, original and new. They would have to physically sign it, digital signatures are not acceptable. And this is a Wiley process, not just GEB, nut fun Wiley!
So anyway, two tasks that would be painful to do by hand: 1) reporting name, email address and institution of all 121 co-authors in a table with only nine rows and also reporting the names of the 49 new authors in another table with only five rows ; and 2) sending personalised messages to all of them.
The data
Both processes could only be automated with good data and we had a table with separated first and family names, institutions and verified email addresses. It really helped that first names and family names were already in two columns and that multiple institutions were also in distinct columns.
PDF form filling in R
At least the Wiley form had fields and after looking into the packages I already had on hand: pdftools and qpdf, I turned to DuckDuckGo that pointed me in direction of the staplr package. The installation was not painless, mostly because staplr depends on java and maybe there was a conflict with pdftools but once set, staplr did everything!
It has basic pdf tools such as select_pages(), remove_pages(), rotate_pages() and combine_pdf() that we used to extract the pages with the tables before multiplying the first table 14 times and the second one 10 times, filling them and finally combining all pages together. For filling in the pdf, staplr works in three steps:
# First reading all fields in the document, there are 116 fields
> fields <- staplr::get_fields(input_filepath = "Authorship-change-form--1---5---2--1.pdf")
> fields[[50]] # this is Table 2, column `Email address`, row 6
$type
[1] "Text"
$name
[1] "Email addressRow6"
$value
[1] ""
# Then, filling in the fields in the value slot like this:
fields[[50]]$value <- "alban.sagouis@idiv.de"
# Easy! But looping over 5 columns, 121 authors, changing page every 9 authors took some more tweaking…
# And finally writing the fields back inside the PDF document!
staplr::set_fields(
input_filepath = "Authorship-change-form--1---5---2--1.pdf",
fields = fields,
output_filepath = "filled_authorship_form.pdf")Batch sending personalised emails
Now that we have the form ready, we can write to all co-authors, asking them to kindly print, sign, scan and send back the form. Our message would begin with “Dear Dr {Family name author}, thank you for contributing data from your data set entitled {Dataset title}, et caetera”. I quickly looked into how to do it in R and the prospect of setting up an email server seemed too complicated, especially when Microsoft Word and Outlook can do it.
Just write your message in a Word document, click on the Mailings tab, Start Mail Merge, choose Letters or Email messages and then click on Select Recipients and select the table where you stored the names, the titles, the personalised messages, random data for an exam, everything you need personalised and of course, the email addresses. Every time you reach a part of the email where you want personalised text, just Insert Merge Field and select the column from your table where to look for data. Once you’re done composing the email, you can Preview Results and go through all 121 recipients to check that the good name or title is sent to the good email address or check that special characters were properly reproduced.
It’s that simple! Finish & Merge, Word and Outlook are going to talk to each other and send each email independently.
Oh, you want to send an attachment too? Or even send a different attachment to each person? Well, Word and Outlook won’t give you the option 🤷️. We tried a first add-in for Word whose free plan did not fit our needs (max 50 recipients) and then found the Merge Tools add-in that allows batch sending emails 20 times for free, thanks Merge Tools.
Encoding
When reading data, Word preferred csv to xlsx but encoding handling was better from an Excel file… For one of the steps, Merge Tools seemed to mishandle UTF-8 encoding and I had to remove all special characters ie. “èéêëě” all became “e”. I used this R command:
stringi::stri_trans_general(
str = your_string_with_special_characters,
id = “Latin-ASCII")If you have tidyverse installed, you already have stringi.
Resources
The staplr package: https://cran.r-project.org/web/packages/staplr/
The microsoft Support page for Mail Merge: https://support.microsoft.com/en-us/office/use-mail-merge-to-send-bulk-email-messages-0f123521-20ce-4aa8-8b62-ac211dedefa4
A quick video to see it happening: https://www.youtube.com/watch?v=NikC2cJ_tHQ
The Merge Tools add-in that we ended up using: https://mergetoolsaddin.com/