To batch format decision letters in Word, you will need to create a VBA macro. A macro, in this context, is a simple automation tool that allows Word to apply consistent formatting to your documents automatically.

Below are step-by-step instructions for creating the macro, along with sample scripts to use. You can customize these scripts to match your specific formatting requirements. Once your macro is created and saved, you can use it whenever needed to quickly format letters in the future.

Customization options

Your macro can be customized in three primary areas using the scripts below. Each area allows you to control specific formatting elements to meet your document standards. All scripts chosen, would be added into the same macro.

Appending images

You can configure the macro to automatically insert images in designated locations within the document, such as:

• Adding a logo or graphic to the header

• Inserting an image into the signature section

Modifying fonts

The macro can standardize text formatting through adjusting:

• Font names (e.g., selecting a specific typeface)

• Font sizes

• Font colors

Adjusting Page Properties

You can also control overall page layout settings, including Margins.

Creating a query

To create a query that exports decisions to Word:

1. Go to Queries / Reports.

2. Select New Query.

3. Configure the following settings:

   • Type: Configurable Joins

   • Category: Records

   • Base: Application, or appropriate base depending on the rows needing returned

4. Select Save.

5. Configure the query with filters and exports.

6. Select Run Query.

7. Set Output to Decision Letter Export to Word.

8. Select Export. The query is downloaded to your machine.

Open the file in Word.

Setting up the Macro

With the downloaded file opened in Word, press Alt+F11 to access the Visual Basic Editor where the macro will be created.

1. Click ThisDocument. If no previous macro has been configured, a blank pop-up will appear.

2. Enter the following script:

   Sub cleanLetters()
        Selection.WholeStory
   
   End Sub

All other scripts will be added after the Selection.WholeStory and before End Sub.

Any script chosen from below would be added within this one overarching script. You do not need to make a separate macro per script.

Appending images to a header

To add an image to a header:

1. Add the following code after Selection.WholeStory and before End Sub in the popup.

   'Append image to header
   ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
    With Dialogs(wdDialogInsertPicture)
         .Name = "http://i.imgur.com/hHCIojS.png" 'This is the URL for the header image
         .Execute
    End With

2. Change the .Name = “URL” link to match the image you want added to the letters.

Appending an image to a signature

To append a signature image to a signature line:

1. Add the following code after Selection.WholeStory and before End Sub:

   'Append signature image to signature
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
         .Text = "The Slate University Admissions Team" ' This is the name we're looking for, and it shouldn't exist in the rest of the letter (e.g., in the body)
         Do While .Execute
              With Dialogs(wdDialogInsertPicture)
                   .Name = "http://i.imgur.com/x7YxZjv.png" 'This is the URL for the signature
                   .Execute
              End With
         Loop
         .Text = "admissions@slate.edu" ' Because we replaced the name with the signature, we need to find the unique text that immediately proceeded the name
         .Replacement.Text = "Alexander Hamilton^padmissions@slate.edu" ' In this case, we're replacing the email address with "Alexander Hamilton" and then adding the email address to the next line
         .Forward = True
         .Wrap = wdFindContinue
         .MatchCase = True
    End With
    Selection.Find.Execute Replace:=wdReplaceAll

2. Update all URLs, email addresses, and names to match your insitution.

Changing font name, font size, or font color

To change font name, font size, or font color, add the following code after Selection.WholeStory and before End Sub in the popup:

'Modify font & font size

Selection.Font.Name = "Times New Roman"

Selection.Font.Size = 12

Selection.Font.Color = wdColorAutomatic

Script for Changing Page Properties

To change page properties (such as margins), add the following code after Selection.WholeStory and before End Sub:

'Modify margins by inches

With ActiveDocument.PageSetup

    .TopMargin = InchesToPoints(2)

    .BottomMargin = InchesToPoints(0.75)

    .LeftMargin = InchesToPoints(0.5)

    .RightMargin = InchesToPoints(0.5)

    .HeaderDistance = InchesToPoints(1) 'Distance between the header and top of the page

    .FooterDistance = InchesToPoints(1) 'Distance between the footer and bottom of the page

    .PageWidth = InchesToPoints(8.5) 'Paper width

    .PageHeight = InchesToPoints(11) 'Paper height

End With

Example of macro with multiple scripts

After adding all of the above to your macro, it should look like this:

Saving the macro

When you’ve added all the scripts you want and adjusted variables to match your institution, you can save the macro and apply it to the exported decision letters:

1. Select File → Export File.

2. In Enter a name.

3. Click Save. Leave the file type as Class Files (*.cls).

   

4. Right-click Normal and select Import File.

5. Select the newly created macro file and click Open.

Applying the macro to the decision letters

1. Return to the downloaded letter file in Word.

2. Click the down arrow in the top left-hand corner.

3. Select More Commands.

4. Change the Choose commands from setting to Macros.

5. Select your macro from the first box on the left and click Add. You should see the macro appear in the box on the right.

6. Select the macro in the right side box and click Modify.

7. Choose any icon and click Ok.

8. The icon for your macro should appear on the top toolbar of the Word document towards the left.

9. To apply the macro on the Word file, click on the icon.

   

💡 Tip

A test letter (Test Letter.docx) and a macro file (Test Macro.bas) are attached for your experimentation and use:

• Test_Macro.bas

• Test_Letter.docx

For Testing with these files:

1. Enable Macros in Word

2. Import the Test_Macro.bas file into Microsoft Visual Basic by opening the program (see above)

   1. Click File on Word then Import

   2. Choose the test_macro.bas file to import

3. The imported macro may appear under the Module or another folder on the left-hand navigation menu. Once you have the file, you can edit/update it and follow the above instructions for testing it on the letter.