topic RE: Mailmerge using Word VBO in Product Forum

Mailmerge using Word VBO

JonathanPringle — Tue, 17 Jan 2023 13:27:00 GMT

Hi

I have installed the VBO containing the Mailmerge action.

However, when running it, multiple Word docs are created rather than all collection items being merged into one document.

What am I doing wrong?

------------------------------
Jonathan Pringle
RPA Developer
Agilisys
Europe/London
------------------------------

RE: Mailmerge using Word VBO

ewilson — Tue, 17 Jan 2023 14:45:00 GMT

Hi @Jonathan Pringle,

The Mail Merge action will take the input data Collection and create a new document for each row of the input data based on the template DOC provided. So, if I provide a data collection as input with two rows (see the screenshot), the VBO will create two new documents using the input template (one addressed to Eric and one addressed to Jerry).

Does that help?

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Mailmerge using Word VBO

JonathanPringle — Tue, 17 Jan 2023 14:55:00 GMT

I understand how it works now. The business want it to be like the Word mailmerge, where 3 rows of data in the collection would create 3 letters within one overall document.

------------------------------
Jonathan Pringle
RPA Developer
Agilisys
Europe/London
------------------------------

RE: Mailmerge using Word VBO

ewilson — Tue, 17 Jan 2023 15:13:00 GMT

@Jonathan Pringle,

Interesting. 🤔 Why would you want all of the custom letters in a single document? When the request was made for us to add this feature to the Word VBO it specifically asked for each letter to be saved as individual documents.

With that said, you could always get to the behavior you're asking for. You could either customize the Merge Mail Code stage to perform the action on a single destination document, or you could add a few additional actions to the process and cycle through the individual documents and merge them all into one final document (ex. Mail Merge -> Loop documents -> Open document X -> Select All -> Copy to Clipboard -> Paste from Clipboard into target document).

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Mailmerge using Word VBO

JonathanPringle — Tue, 17 Jan 2023 15:17:00 GMT

Thanks Eric. I dont get why they want the letters in one document either. I will look at the potential solutions, or I will write an object that drives the Word mail merge wizard.

------------------------------
Jonathan Pringle
RPA Developer
Agilisys
Europe/London
------------------------------

RE: Mailmerge using Word VBO

JonathanPringle — Tue, 17 Jan 2023 17:30:00 GMT

Hi Eric

On the existing Mail merge code, could you give me some pointers on what should be changed, so it can work on a single doc?

------------------------------
Jonathan Pringle
RPA Developer
Agilisys
Europe/London
------------------------------

RE: Mailmerge using Word VBO

ewilson — Tue, 17 Jan 2023 18:43:00 GMT

Hi @Jonathan Pringle,

I haven't investigated it, so I'm not 100% sure. What I can tell you is that the current Code stage creates a document instance, based on the template passed in, and then loops through the merge fields defined in the template (i.e. filed name includes MERGEFIELD) populating each one with the associated value from the data collection. Once it's covered all the fields, it saves the document to file giving it the name based on the specified merge field. It continues that loop for all the rows in the data collection.

My guess is that you'll want to have a look at the actual MailMerge object in Word's VBA object model. We didn't actually use that in the action we implemented, but my guess is that it will give you what you're looking for. However, the MailMerge object is based on OLEDB from the looks of it, so you may need to have your input data in a CSV or XLSX that you could then create a connection string for. Hopefully that makes sense?

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Mailmerge using Word VBO

JonathanPringle — Wed, 18 Jan 2023 11:00:00 GMT

Thanks Eric for your help. I'll look at my options and decide which one is the best solution.

------------------------------
Jonathan Pringle
RPA Developer
Agilisys
Europe/London
------------------------------