For some years I have used Powershell as often as possible. Especially in the beginning of this journey it meant that it took more time to accomplish the task with PS than it would have taken manually. One task at the time it has been building my confidence. I’m not a developer by any means nor a script hero. But I have achieved the level where PS is faster and more robust way of working quite often. I’m trying to keep this attitude being eager to learn and not limit it to PS only. In context to Power Automate, it means that I’m trying to see challenges and possibilities to learn whatever I do. Hopefully this one will also turn out to be surprisingly effective tool at some point.
This time the challenge is: “Find an automated way of delivering personalized PDF documents to recipients as encrypted email send from a shared mailbox”. I was able to find multiple tricky points to overcome while building this up.
My selection for tools is:
- Microsoft Word + Excel = MailMerge to create PDF content
- Adobe Acrobat DC = Split content created with MailMerge to personal PDF files
- Power Automate = Create email drafts and send those as separate task
Step 1: Creating personalized documents, MailMerge (Microsoft: Use mail merge for bulk email, letters, labels, and envelopes)
Using MailMerge is pretty straight forward. Keeping in mind that we would like to deliver each produced document to a specific person, we need a way to match a document with the recipient email address. I didn’t find a way to produce separate PDF files using only features available from Word. Adobe provides “Adobe PDFMaker” plugin to Word that allows saving each document produced by MailMerge as separate PDF file. Unfortunately, there is very limited options on naming the produced PDF files. I didn’t find a way to identify which file belongs to which recipient.
Step 2: Producing separate PDF file for each recipient, Acrobat DC (Adobe: How to split a PDF file)
Not being able to produce separate PDF files from Word lead me splitting those files using Acrobat DC. So from Word MailMerge I first printed out the whole result as one PDF. Then in Acrobat DC, using split tools, it was easy to produce files with names like “file#-filname.pdf”.
Step 3: Creating email drafts, Power Automate (Microsoft: Power Automate, Use the Outlook mail REST API)
Power Automate has built-in actions like “Send an email from a shared mailbox (V2)”. I wanted to create email draft and send it as encrypted. I didn’t find readily available action to achieve these, so I decided to use GraphAPI calls instead. My flow starts contains following steps: Listing PDF files (pattern: \d+-.*.pdf$) in OneDrive 100 at the time, finding the Excel with input data and the first table of it, finding the correct email folder for drafts (I named the folder MailMerge) and then actually creating the drafts in a apply to each loop (that is inside until loop).
The issues I had to overcome:
- “Find files in folder” action returns a maximum of 100 files. This requires handling the rest somehow. Therefore, I have the until loop to fetch more files until all of them have been processed. It also seems that this action actually returns 100 items and then applies the search (pattern in my case), so do not expect to have even that 100 hits in one reply every time.
- “List rows present in a table” seems to return only 256 items by default. Remember to activate pagination through settings if you want more.
- Looping through files referenced by “Find files in folder” requires expression to access a particular file. For my case is used “items(‘Apply_to_each:_Create_email_drafts’)?[‘Id’]”
- For finding recipients email address from the table, I need the information of the row number. I’m splitting the “file#” part from the file being currently processed using expression “sub(int(split(items(‘Apply_to_each:_Create_email_drafts’)[‘name’], ‘-‘)[0]), 1)”. So, I will take the “name” property, split it with delimiter “-” and select the first part from the first object. Since file numbers start from 1, but the table numbering starts from 0, I will also have to subtract one from the result.
With that one GraphAPI call I can set the target to a specified folder within a shared mailbox, specify the recipient email address from the input Excel, set custom email header (that can be used to trigger a mail flow rule to set the encryption) and attach a file to the message.
I have then another Power Automate flow that finds messages in that folder inside that shared mailbox and sends those messages using GraphAPI. This is implemented as a separate flow to allow a human to preview the emails before actually sending those out.
I did test the process and the flow creating the drafts takes something like 10-15 minutes to create 300 drafts. This is with concurrency set to 1. The performance is dependent on multiple factors, one being the size of the attachments (which were small in my tests). With the default settings the until loop will run a maximum of 60 times. So depending on how your filter matches with the files, this should scale up to a couple of thousands of attachments/emails. That should be enough for this kind of a flow, I think. Actually, it seems that also Adobe has quite capable APIs available, so it might be possible to actually merge and split the documents within that first flow…
I was happy to play around with this and happy that I was able to run my test successfully eventually. You’re questions or comments are more than welcome, as always!
Just because I/1 can 