M4 (Mass Mail Merge Macro)
(PDF or other file attachments)
This mass mail merge tool was developed for the Remediation process for Compliance. A few rules for use:
- It will ONLY (currently) work from a user’s desktop and it has to have the code modified to include said user’s id
- The macro code is commented to include what to leave alone and what can be modified or has to be modified.
- These instructions were originally written for the Remediation process but can be used for any send or BCC address and any user.
- The PDF files must be in ONE folder, may be copies if they are in the project folder on the user’s desktop.
Go into the folder named Remediations for E-Mail Merge (or designated project folder) on your desktop. Find the M4 Mail Merge <username>.xlsm file. This file is hard coded to look in this folder for the files you are about to merge and CANNOT be moved (until this whole process is done) as it will break it.
Each team member must have a custom M4 Mass Mail Merge Macro workbook. These are specific to each team member and are hard coded in the macro for each person. The macro must specifically be a folder labeled Remediation Notice Mail Merge (can be named something else but must be changed in the macro to reflect the folder name and is case sensitive) on your desktop.
For each file you will need the following 3 pieces of information:
- Eaddress – the email address of the Program Administrator (or other addressee such as a provider’s main email, only one email address per line is allowed, if more than one needs to be used duplicate the line with the secondary email address)
- Subject – <Key> <Site Name> <Parent Agency> (can be anything desired)
- Attachment – the file name <key>_Remediation (will come from the list of files)
To easily fill in the file path for a long list of files to be sent (Attachment Field):
Go to Remediations for E-Mail Merge (or your project’s designated folder) on your desktop where you will find a long list of files you want to send.
Select all the files using CTRL-A (Select All) then hold Shift and Right click. The pop up menu will have an option to Copy as Path
Go into the M4 workbook and Paste (CTRL-V) the results under attachment.
You only need the file name for this column because the file path is hard coded into the macro, so to quickly eliminate the unnecessary data you will highlight the file path up until the file name.
On the Home Tab go to Find & Select>Replace
Leave the Replace with box empty and click Replace All.
The body of the email and signature are hard coded and will show up automatically as will a BCC of each message. It will send the messages as if it was sending from the Compliance box as well.
Running the Macro
The Macro dialog will pop up, click Run
The process will run automatically with an OK button for each letter going out (no I have no idea how to fix this at this time)
Once the batch is sent move the files to an appropriate folder for that service i.e. ISW, Adult Day etc.
MOVE the list to a “Service m-d-y” named sheet and leave Sheet 1 blank for the next batch. (another hard coded piece to this tool)