Mass Email Tool Guide - Additional Information

This document provides supplemental information about the Mass Email Tool process, including working with formatting in the Mass Email Tool, adding the Create PDFs macro to existing letterhead files, and creating new Outlook profiles to send emails from other addresses.

This document takes a deeper dive into some of the more complex considerations of using the UW-Madison Mass Email Tool.

Working with formatting in the Mass Email Tool

Problem

Formatting can be one of the trickier parts of using the Mass Email Tool. Excel sometimes shows the values of cells in a different way than the "true" value of the cell.

As an example, the cells in the spreadsheet below are formatted as Currency and Date.

Screenshot of Excel spreadsheet with cells formatted as Currency and Date highlighted with red rectangle

However, if we change the format to General, you'll see the "true" values of the cells - a basic number and an Excel date serial number (number of days since 1/1/1900).

Screenshot of Excel spreadsheet with currency and date values in General format highlighted with red rectangle

These "true" values are what Microsoft Word sees when doing a mail merge. If you want to use the formatted values in your memos, you'll need to do a little Excel work.

Solution

To lock in formatting in Excel, you need to recreate the values as text. In effect, you are "staging" the data to appear exactly as you want it to be in the memo.

The easiest way to do this is with a simple Excel formula using the TEXT function, which tells Excel to force the value of a cell into a specific format.

To begin, select a blank column and change the format to General, which will allow you to enter formulas.

Screenshot of Excel spreadsheet with empty column and red rectangle highlighting General format

Next, enter the following formula into the first cell that you would like to hold a formatted value, adjusting your cell reference according to your spreadsheet.

=TEXT(K4, "$#,##0.00")

Screenshot of Excel spreadsheet with TEXT formula creating currency format

This formula tells Excel to take the value of cell K4 - the first cell on the example spreadsheet containing a value to be formatted - and then display in the format "$#,##0.00", meaning to add a dollar sign in front, separate thousands with a comma, and keep one digit for dollars and two digits for cents even if the value is 0. You can see the value displaying in the desired currency format in cell M4. The triangle in the top left corner indicates that the cell contains a number formatted as text, which is what we want.

Next, making sure the cell with the formula is selected, double-click or click and drag the fill handle (the square in the bottom right of the cell) to expand the formula to the rest of the rows.

Screenshot of Excel spreadsheet with fill handle in bottom right of cell highlighted with red circle

Screenshot of Excel spreadsheet with TEXT formula expanded to all rows to create currency format for all

Finally, make sure that all of the cells containing formulas are selected and copy (right-click and select Copy or use Ctrl+C / Cmd+C keyboard shortcut). Select the top cell of the original column ("Currency" in this example), right-click, and select the Paste Option "Values" (clipboard with "123").

Screenshot of Excel right-click menu with Paste Option "Values" highlighted with red rectangle

Screenshot of Excel spreadsheet with formula-created currency values pasted into column

The currency formatting is now part of the "true" value of the cell and will keep that formatting during your mail merge. You may delete the "helper column" (Column M in this example); it is no longer needed.

This method can be used to stage data in all sorts of different formats - dates, times, fractions, etc. Refer to Microsoft's TEXT function page for more details.

Additional Excel functions

There are many Excel functions that you can use to get your data how you want it. Refer to Microsoft’s Excel functions (by category) page for the full catalog of Excel functions. Also check out the Excel courses on LinkedIn Learning - free when you log in with your NetID@wisc.edu email address!

Here are some common functions to start.

Excel function starter kit

Function

Description

LEFT / MID / RIGHT functions

Get a specified number of characters from the left or right ends or from a specified starting point in the middle of a cell.

UPPER / LOWER / PROPER functions

Convert text to uppercase, lowercase, or proper case

TRIM function

Removes all spaces from text except for single spaces between words

TEXTJOIN function

Combines text from selected cells separated by a specified character or characters

IF function

Create different outcomes depending on if a specified condition is true or not.

XLOOKUP function

 

Searches for a value in a specified range and returns the value of a specified column when it finds a matching row.

 

Note: You may need to use the predecessor VLOOKUP function if you are using an older version of Excel.

IS functions

Check if a cell has a specific value or state

AND / OR functions

Checks if all or any of a set of specified conditions are met

ROUND function

Rounds a number to a specified number of digits

DAYS function

Calculates the number of days between two dates

TODAY function

Returns the current date

Adding Create PDFs macro to existing letterhead files

Although we strongly encourage you to use the Mass Email Memo Template to create your PDF memos, we recognize some users might worry about recreating an existing letterhead file and would prefer to add the Create PDFs macro into an existing letterhead file. This section will walk you through that process.

To begin, navigate to the Developer tab in the Microsoft Word ribbon. If you don't see the Developer tab in the ribbon, you might need to show the Developer tab.

Select the Visual Basic option.

Screenshot of Developer tab in Word with Visual Basic option highlighted with red rectangle

This will open the Microsoft Visual Basic for Applications program. In the Project window towards the top left, right-click your document (UW_ltr_4c in this example), open the Insert menu, then select the Module option.

Screenshot of Microsoft Visual Basic for Applications program showing menu path to insert module

Copy and paste the following code into the Module window.

Sub CreatePDFs()

    Dim response As String
    Dim strFolder As String
    Dim strName As String
    Dim mainDoc As Document
    Dim i As Long
    Dim sendCount As Long
        sendCount = 0
    
    'Confirm macro start
    response = MsgBox("Depending on the number of documents being created, this process might take a while. You will get a confirmation when the process finishes." _
    & Chr(10) & Chr(10) & "Click OK to begin or click Cancel to end.", vbOKCancel + vbInformation, "UW-Madison Mass Email Tool")

    If response = vbCancel Then

        'Display cancellation message and end macro if user clicks Cancel button
        MsgBox "The process has been cancelled. No documents were created.", vbOKOnly + vbInformation, "UW-Madison Mass Email Tool"
        Exit Sub
    
    ElseIf response = vbOK Then
        
        'Hide document window
        Application.ActiveWindow.Visible = False

        Set mainDoc = ActiveDocument

        With mainDoc
    
            For i = 1 To .MailMerge.DataSource.RecordCount
    
                With .MailMerge
                .Destination = wdSendToNewDocument
                .SuppressBlankLines = True
        
                    With .DataSource
                    .FirstRecord = i
                    .LastRecord = i
                    .ActiveRecord = i
                
                        'Skip mail merge row if Empl ID without leading/trailing spaces is blank
                        If Trim(.DataFields("First_Name")) = "" Then Exit For
                        strName = .DataFields("File_Name")
                        strFolder = .DataFields("File_Path")
                
                    End With
        
                .Execute Pause:=False
        
                End With
    
                strName = Trim(strName)
                strFolder = Trim(strFolder)
                
                'Save each mail merge document using folder and filename from linked Excel sheet
                With ActiveDocument
                    .SaveAs FileName:=strFolder & Application.PathSeparator & strName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
                    sendCount = sendCount + 1
                    ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
                End With
            
            Next i
    
        End With
        
        'Show mail merge document
        Application.ActiveWindow.Visible = True

        'Display success message
        MsgBox "You have created " & sendCount & " documents saved in " & strFolder & "." & Chr(10) & Chr(10) & _
        "You may now close Word and open the Mass Email Tool in Excel for next steps.", vbOKOnly + vbInformation, "UW-Madison Mass Email Tool"
        Exit Sub

    Else
        
        'Display cancellation message and end macro if user clicks anything other than OK or Cancel buttons
        MsgBox "The process has been cancelled. No documents were created.", vbOKOnly + vbInformation, "UW-Madison Mass Email Tool"
        Exit Sub
    
    End If

End Sub
Screenshot of Create PDFs macro code in module
Click to enlarge image

Continue to set up your mail merge as normal, making sure to connect your mail merge document to the Mail Merge (read-only) sheet of your associate Mass Email Tool.

Screenshot of mail merge data source with Mail Merge (read-only) sheet selected

When you are ready to create your PDFs, return to the Developer tab in the Word ribbon and select Macros.

Screenshot of Developer tab in Word with Macros option highlighted with red rectangle

In the Macros window that displays, make sure that the CreatePDFs macro is selected and then select the Run button. The macro will prompt you through the rest of the process.

Screenshot of Macros window with CreatePDFs macro selected

Sending emails from shared accounts

The Mass Email Tool will send emails from your default email address in Outlook. In most cases, that will be your individual address. You may want to send from a different email address, like a shared service account.

In order to do this, you'll need to set up a new Outlook profile and sign directly in to the account from which you want to send.

There are a couple of steps involved in this process.

Confirm service account password

To begin, make sure you know the password to the service account. If you do not know the password for the service account and you are an administrator for the account, refer to DoIT's Office 365 - Reset Service Account Password document to set a new password.

Create new Outlook profile

Now that you know the password for the service account, you can set up the new Outlook profile. For this example, we'll be signing in to the HRIS@ohr.wisc.edu service account.

In Outlook, select the File tab, then click the Account Settings button and select the Manage Profiles option.

Screenshot of Outlook File menu with Manage Profiles option highlighted with red rectangle

In the Mail Setup - Outlook window that opens, select the Show Profiles... button.

Screenshot of Manage Profiles window with Show Profiles option highlighted in red rectangle

In the Mail window that displays, select the Add... button.

Screenshot of Outlook Mail profiles window with Add button highlighted in red rectangle

Choose a name for the profile you will be setting up. You may name the profile whatever makes sense to you. We'll enter "HRIS" since we're setting up a profile for the HRIS account.

Screenshot of New Profile window

In the Add Account window that displays, fill in the information in the Email Account section. Outlook may autofill your name and email address with your current individual account - just change the email address and the rest of the fields should become editable. Select the Next button.

Screenshot of Outlook Add Account window with OHR HRIS information filled in

Outlook will attempt to automatically sign in to the account. If a Microsoft Sign in window displays, confirm that the email address shown is the account you are trying to add, then select the Next button.

Screenshot of Microsoft Sign in window with HRIS@ohr.wisc.edu account displayed

You will likely be shown a UW-Madison Login window asking for NetID login. The NetID for a service account follows the format name_domain, where name is the part of the email address before the @ and domain is the part of the email address between the @ and "wisc.edu" (if it exists). For example, the NetID for the HRIS@ohr.wisc.edu account is HRIS_ohr. Use the password that you confirmed in the previous section. Select the Log In button.

Screenshot of NetID login window with NetID hris_ohr entered with password

Outlook should display a success message. Select the Finish button to finish setup.

Screenshot of Outlook Add Account success message

You should now see your new profile in Outlook profiles list you saw earlier. Select the OK button to close this window.

Screenshot of Outlook profiles list with new HRIS profile added

To switch to your new profile, open the Account Settings menu again and select the Change Profile option.

Screenshot of Outlook Account Settings menu with Change Profile option highlighted with red rectangle

Outlook will display a pop-up message and then close. Re-open Outlook. Outlook will show a prompt to choose which profile you would like to open. Select the new profile you created. Tip: if you want to get this prompt every time you open Outlook to ensure you always select the appropriate profile, select the Options button, then select the option for "Prompt for a profile to be used". Select the OK button to open Outlook.

Screenshot of Outlook Choose Profile window with new HRIS profile selected

Screenshot of Outlook Choose Profile window with option selected to prompt for profile every time

It may take longer than usual to open the new profile for the first time. Once Outlook opens, you should see the folders for your service account. It may take some time for Outlook to load all of your messages and items.

Screenshot of HRIS@ohr.wisc.edu folders in Outlook

To change back to your normal profile after sending your emails, go to Account Settings > Change Profile as you did previously or just close and re-open Outlook if you selected the option to select a profile every time. You can also go back to Account Settings > Manage Profiles > Show Profiles to change your settings about asking for a profile every time or selecting a default profile.




Keywords:macro, excel, formatting, mail merge, word, letterhead, pdfs, outlook, profiles, evergreen   Doc ID:122569
Owner:Colin S.Group:HR Communities of Practice
Created:2022-11-18 13:56 CDTUpdated:2022-12-22 14:51 CDT
Sites:HR Communities of Practice
Feedback:  0   0