Mass Email Tool Guide - Using the Tool - 1. Setting Up the Mass Email Tool (Excel)
This document explains the process of using the Mass Email Tool in Excel to set up the contents and settings for a mail merge in Microsoft Word and emails sent through Microsoft Outlook.
You'll use the UW-Madison Mass Email Tool file to set up the contents and settings to be used for the future mail merge and emails. This document will walk you through each step of this file.
First, open the UW-Madison Mass Email Tool file (Microsoft Excel Macro-Enabled Worksheet) from the folder where you extracted it in the previous step.
Important!
You must click the Enable Editing and Enable Content buttons if they're displayed in the top banner after opening the Mass Email Tool. The tool will not work if you do not enable provide this permission!
If you see a banner that says "SECURITY RISK Microsoft has blocked macros from running because the source of this file is untrusted":
- Follow the instructions from Microsoft to unblock the file.
- Select the Enable Content button as described above.
Overview
The Mass Email Tool consists of several sheets that you will use for different parts of the process. Click the sheet name to jump to the sheet's section on this page.
- Tool Info and Process - this sheet contains a summary and basic instructions for using the tool.
- Enter Data - this sheet is where you will enter the individual data that will be used to customize memos and emails.
- Email Input - this sheet contains the content of the emails that the tool will send.
- Set File Naming - this sheet contains the file names and locations for the customized memos, plus any additional files you want to attach to all emails.
- Mail Merge (read-only) - this sheet is populated by the macro in Step 4 and contains the final version of all the information that will be used to create memos and send emails.
- Help - this sheet launches the Mass Email Tool Guide in your default Internet browser in case you get stuck. There is no content within the sheet itself.
Tool Info and Process
Click to enlarge image
The file will open to the Tool Info and Process sheet, which provides an overview of the tool and basic steps. Note the buttons in Steps 4 and 9 - you will single-click those buttons at the appropriate times to run macros.
Enter Data
Click to enlarge image
The Enter Data sheet is the space for you to organize the data that will be used in the memos and emails you create with the tool. Think of each row as one memo and email. You will determine the exact data used for the memos and emails in other steps.
The tool will prevent you from deleting any of the default information to protect the formulas and macros that make the tool work, but you may add additional data fields starting with Column K and hide any of the default columns that you will not use.
The Enter Data sheet includes several default fields for basic information likely to be used in most projects:
- To_Email - email address(es) to be used in the To field for each row's email. Separate multiple email addresses with a semicolon as you would in Outlook - example: HRIS@ohr.wisc.edu; hr_communities_of_practice@ohr.wisc.edu
- CC_Email - email address(es) to be used in the Cc field of the email for each row's email. Separate multiple email addresses with a semicolon as you would in Outlook - example: HRIS@ohr.wisc.edu; hr_communities_of_practice@ohr.wisc.edu
- BCC_Email - email address(es) to be used in the Bcc field of the email for each row's email. Separate multiple email addresses with a semicolon as you would in Outlook - example: HRIS@ohr.wisc.edu; hr_communities_of_practice@ohr.wisc.edu
- Reply_To_Email_1 - email address to direct replies to instead of the sender address. For example, if you will send the emails from your individual email address but you would like replies to be directed to a shared account, enter the shared account address in this field and if the recipient clicks Reply then Outlook will automatically direct the reply to the shared account address. Only enter one email address in this field.
- Reply_To_Email_2 - second email address that will be added for replies in addition to Reply_To_Email_1. Only enter one email address in this field.
- First_Name - the first name of the recipient of the memo/email.
- Last_Name - the last name of the recipient of the memo/email.
- Empl_ID - the Employee ID # of the recipient of the memo/email.
- Empl_Rcd - the Employee Record # of the recipient of the memo/email. If one person will receive multiple memos/emails for different jobs, the Empl_Rcd can be used to ensure unique file names for the memos.
- UDDS - the UDDS (Unit > Division > Department > Subdepartment) code or Department ID of the recipient of the memo/email.
Note: Any formatting you apply to cells in Enter Data (currency, date, etc.) will not transfer into the memos due to how Microsoft Word Mail Merge reads data from Excel spreadsheets. Refer to the Mail Merge (read-only) sheet after running the Generate Mail Merge macro to see how your data will show up in the mail merge. If you need to change anything, make your updates in the Enter Data sheet and then run the Generate Mail Merge macro again. Refer to the Mass Email Tool Guide - Additional Information document for more information about formatting.
Email Input
Click to enlarge image
The Email Input sheet allows you to draft the language for the email that the tool will send. You specify the Subject, Greeting, Name option, body paragraphs, Salutation, Signature, and Footer. You'll see a preview of your email in the Email Preview cell to ensure it looks correct. The Email Body HTML Format cell shows the body of the email with HTML formatting, which is how it will be sent to Outlook to actually send the email.
Email limitations
- At this time the only customizable part of the email message is the name in the greeting.
- It is not currently possible to add formatting or hyperlinks to the emails. However, if you enter an email address or URL, most modern email clients will automatically hyperlink them on the recipient's end. We recognize this does not align with accessibility best practices and we will continue to look into ways to make the emails more dynamic.
Set File Naming
Click to enlarge image
The Set File Naming sheet is where you will specify the settings for the PDF memos that the tool will create.
Begin by clicking the Browse... button in the PDF Save Folder section to select the folder where you want your PDF memo files to be saved.
If there are other existing files that you would like to attach to every email, you may select up to two additional files in the Additional Attachments section.
Next you will choose the elements that will be used to create the file names for your PDF memo files. For each element, indicate which data point you would like to use. This data will pull from the Enter Data sheet that you filled out previously. Note that the tool automatically adds a space between each element. The File Name Preview shows you an example of what your file names will look like using the first row from the Enter Data sheet. The File Path Preview shows an example of the full file path for your PDF memo files, meaning your selected PDF Save Folder and the file name as you've constructed it.
Important!
You may not use any of the below characters within the file name. These reserved characters have special meanings within computer operating systems, so the operating system does not allow them in file names.
< > : " / \ | ? *
If you use one of these characters in your file name, the tool will show an error in the File Name Preview to prompt you to change your settings.
Mail Merge (read-only)
The Mail Merge (read-only) sheet is populated by the Generate Mail Merge macro in Step 4. This sheet contains the final form of all the information that will be used to create memos and emails later. Since the information is generated in specific ways to work with future processes, this sheet cannot be changed manually. If you need to make any changes to your data, update the Enter Data sheet and then run the Generate Mail Merge macro again; the macro fully deletes the Mail Merge sheet and then populates it from scratch each time it is run.