Mass Email Tool Guide - Additional Information

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
• Adding Create PDFs macro to existing letterhead files
• Sending emails from shared accounts

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.

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).

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.

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")

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.

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").

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.

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.

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.

Copy and paste the following code into the Module window.

Click to copy code

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

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.

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

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.

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.

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

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

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.

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.

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.

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.

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

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

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

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.

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.

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.