This was an incredibly complex project I was asked to help with initially and it became my “baby” after a while.
The whole purpose of the project was to track the results of on site reviews for Medicaid compliance. The project had completed a Survey Monkey survey and was struggling to compile and output letters after additional on site reviews had been completed. I took the survey results and the additional questions that the team needed and turned it into a multi sheet Excel, one for each site (1200 or so site based providers and equal that in Spreadsheets).
The first sheet was an import of the PDF from the survey so it was clear which site was being reviewed. The second sheet contained a macro driven questionnaire for the Compliance Team to use for reviews. The third sheet compiled the data entered into the second sheet and combined it with provider name, number and address information and was used for the first round of mail outs via email as well as merging the data to a form based PDF. The last sheet compiled additional compliance data as a way to determine what additional letters were needed.
The second piece of this project was a series of 8 letters, the initial letter telling them the results of their visit and an additional 6 for various levels of compliance plus one certifying the acceptance of the changes made during the process. These were all written in Word which was then used to do mail merged letters one at a time. From there the letter was converted to PDF and fill in form fields were added.
Adobe DC and Outlook do not do mail merges well and so a secondary Excel spreadsheet system was developed to assist with creating the emails and attaching the PDFs to emails. This system used a contact name, email, file name for the PDF and a static programmed instructional message for the body of the email.
Given that I worked on the functionality of the whole system solo and had no budget, I did however have tight timelines as the site reviews were in full swing when I was asked to tackle the project. I am forever grateful to my office mate who tested several times a day for me and offered invaluable feedback on the needed functionality.
The first run of the full project was tested in house and the Provider Certification & Compliance team was gracious enough to put up with and even make up data to help test the system for me and allow me to work out bugs. The first live test was 300 merges and mailings and out of that only 8 bounced all of which were because of a provider changing their email contact and not notifying the State of the change.
This process was used to generate another 3-5 rounds of emails in order to get the 1200 or so providers all in compliance with the regulations.
In addition to designing and developing the process from end to end, I also built a procedure manual for it and provided the team with training. A tool to track incoming responses was also built and generated reports for management.
All the macros were written in .Net. Only the actual mail merge portion is shown below. The Compliance Tool is on another page.