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:

  1. 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)
  2. Subject – <Key> <Site Name> <Parent Agency> (can be anything desired)
  3. 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.

Use CTRL-C (Copy) to copy the data

On the Home Tab go to Find & Select>Replace

Paste the file path in the Find what: field

Leave the Replace with box empty and click Replace All.

You are left with just the file names.

Check that your subj and attachment names are the same and belong to the email address under eaddress!

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

Turn on your Developer Toolbar by going to File>Options>Customize Ribbon and checking the Developer box on the right hand list. (only have to do this once)

Go to the Developer toolbar (now found next to View)

Click Macros

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 1blank for the next batch. (another hard coded piece to this tool)

Sue Darby
Business Consultant & Technical Writer at Alaska Office Specialists
Sue Darby is a wizard with Office Suites… pick a Windows, Internet or Linux based variation and she can make Word, Excel, PowerPoint, Access, Publisher, Project, SharePoint and their counterparts do whatever is needed from basics to macros and things they weren’t originally programmed to do (mass e-mail merge PDF files through Outlook). She writes technical instructions from “This is the mouse” level to anything new that she’s done only once or twice. She can target instructions to the level of the audience as well for presentations. She also installs, sets up, maintains and secures efficient WordPress websites. She is a mother, published author, honors graduate and amateur photographer.

Sue is an experienced talent on the labor market. She is an explorer, ready for adventures abroad. She is an achiever, who is willing to work hard to reach goals. She is a team player, getting along well with people around her. Creativity is one of her strengths and she’s often up to try something new. She is a great asset to a team!

She is always willing to learn something new every day. Continuous personal development and adaptability are in high demand in a fast-changing work environment, thus sharing her learning motivation is important to her. She is determined to have a positive impact – not only on the working environment but on the whole of society.

Bookmark the permalink.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.