Sharing my Power Automate template: Mail Merge into One Document with Conditional

Alex Wong

- Description: Get list of gifts and based on various condition, some of them will get populated into word document (mail merge), the result will be ONE document file (use-case: send to mail house for physical mailing)

- Required connections:

• Blackbaud Raisers Edge NXT Constituents
• Blackbaud Raisers Edge NXT Gifts
• SharePoint
• Microsoft Teams
• Word Online (Business)

- Suggested skill level required to use this template: Intermediate

- Configuration details:

• Setup a Word Document template on SharePoint that has Repeating Control. (this template is not for teaching how to do word document template with repeating control, please use google).

- Category: Acknowledgement, Automation, Notification

- Instructions:

• Download the .zip package and import into Power Automate

WordMailMergeIntoOneDocument_20240224051815.zip

• After import, update the List Gift action (currently target a specific constituent system id) to list of gifts you want to test
• Optionally update the condition on which gift to include (currently is only conditioning on gift amount > 0)
• Update Populate Word Template action pointing to the word template created that has the repeat control
• Update Create Merge Document to save the word document to sharepoint location of your choice with file name updated.
• Update (or delete action) Send Message of Full Process Fail action to send chat bot message to yourselves (Recipient). This is error handling that will prevent flow erroring out without proper handling.

Alex Wong

Actually forgot to add a condition into the flow.

It is possible that there are “no gift” to do mail merge on, so here's a new zip file which checks for the length of the array of gift to receipt being greater than 0, if yes, then populate and create document.

WordMailMergeIntoOneDocument_20240224054306.zip

Carol Grant

@Alex Wong
Thank you for posting this, I saw your comment in the Power Automate Group for me also. Great timing! I did get the Repeating letters to work, now just playing with the page breaks.

-Carol

Alex Wong

@Carol Grant
in the repeat control, i find it easiest to add a page break right before end of repeat control to do this. However, this also means there will be a extra last page that's useless that needs to be dealt with manaully. If you find other ways around it, please post.

Austen Brown

@Alex Wong - The easiest way to get around the final blank page is to add a bunch of extra returns below the body of the letter within the repeating control.

Alternatively, according to ChatGPT you could use a VBA Macro to remove the final blank page after the merge, but that seems needlessly complicated.

Alex Wong

@Austen Brown
lol i was just reading an article yesterday about ChatGPT is only 52% right when it comes to anything programming related.

You can you run a VBA Macro to remove the last page, but if you have the time to open the word doc to run a macro, you can delete the last page (delete the last “page break”) manually in the same amount of time. Yes, like you said, needlessly complicated.

As for your comment about “add a bunch of extra returns below the body of the letter within the repeating control” I'm not sure what you mean

Austen Brown

@Alex Wong - Can you see the image in the my previous post? I included a screenshot of what I was referencing. Essentially you can get around the blank page issue by extending the repeat control to encompass entire page. I've attached a sample doc.

As for ChatGPT not surprised it's right only half the time. When I use it for coding assistance, I have google open trying to confirm the solution it gave me actually exists…

Sample Thank You Template_Repeating Control.docx

Alex Wong

@Austen Brown
I think I understand what you mean, by manually adding enough “newline paragraph" so that the “full page” is taken up and not use any page break.

This works for “static" length of the page. If the page can be varying length (i.e. 1 line per gift in a donor statement situation, 1 line per “split” of gift info, etc), then this won't work.

Mark Kosier

@Alex Wong
Hello Alex, thank you for sharing this work. I wanted to ask how are you finding the connector for Blackbaud Raisers Edge NXT Gifts. I am able to find the other Raisers Edge connectors I just connect find the Blackbaud Raisers Edge NXT Gifts connector.

Alex Wong

@Mark Kosier
I always search for “blackbaud gift” and it will come up