"Can you send certificates of attendance by e-mail this year? We have printed them and sent them by regular mail."
"I can try."
Thus began my "journey" through many computer programs. My attempts to use Microsoft Power Automate failed due to factors beyond my control. With a lot of Google and consulting with colleagues in marketing or IT, we came up with the result.
Steps that can help you if you ever send a mass e-mail where each recipient gets their personalized attachment:
Step #1 - Sample Document
A colleague created a sample certificate in A4 format in Adobe Illustrator. He saved the image as a .pdf, which I converted to .docx.
Step #2 - Individualization of the Document
In Microsoft Word, I created a Text Box on the blank line for the individualization of the certificate. Send > Start Merging Documents > Select Recipients > Use Existing List ... Using the above function, I extracted one column from Microsoft Excel that I needed on the certificate.
Step #3 - Save all of the Documents
After checking all the pages, to avoid any errors, I saved the certificates in one PDF document: Send > Finish > Finish and Merge > Print Documents ... > All > Microsoft Print to PDF.
Step #4 - Seperate Documents
On the ILovePDF website, I separated the documents so that I got each certificate in its file.
Step #5 - Don't forget GDPR
Another colleague uploaded documents to our internal website and generated random links so that, by guessing, recipients would not be able to download other documents. For example, if the links ended with certificate-1, certificate-2 ... this would be inappropriate under the GDPR.
Step #6 - Collect the data
I collected the following data in Microsoft Excel: first name, last name, e-mail, institution, and link to the certificate.
I separated the first and last names into columns using Data > Data tools > text to columns.
I changed each column of first and last names with the =PROPER function so that the data starts with a capital letter. Sometimes there were extra spaces after the text, so I used =TRIM to delete them.
Before finishing, I checked that whichever person was not repeating themselves with the Data > Data Tools - Remove Duplicates.
Lastly, I made one column that I needed for the certificate: name, last name, and institution with function =CONCATENATE. I used it in step 2.
Step #7 - Mailchimp
Personalized e-mail can be sent from Outlook if the data is in Microsoft Excel, but there is no traceability of the opened messages. I already use Mailchimp to send e-mails to our clients, thus I used the same tool. I imported the data there using Merge Tags.
Step #8 - Check the Result
Unfortunately, the Preview in Mailchimp did not tell me if the task was successful, so I created a separate tag, called it Test, and added only my email. I sent myself an email and started jumping with joy.
Mission accomplished!
Perseverance and teamwork have led to progress in a process that has been the same for many years. I am proud of our success, and I know there are many more to come. How could there not be, as we have such a good team of colleagues? 😉
Comments