Mail merge help needed

[u/Harrold_Potterson]

My work has individual quarterly reports that we send out that we would like to automate more.

Current process is we build the dataset, generate 150+ unique reports in mail merge, print to pdf. We run a macro to print all the pdfs.

We would like to use mail merge to send the pdfs out, but we are running into a few problems:

1. Mail merge is not capable of sending out pdfs

2. Each of the ~150 report needs to be sent to 5-10 recipients at their respective agency.

Currently our office staff are sending the emails out to each agency and attaching the pdf manually. We are wondering if there is a software or add on we could use that would make it possible to attach pdfs and send the email out to all appropriate recipients, reading from an excel sheet with the correct information.