Mail Merge in Excel

Mail Merge in Word has some limitations.

Here's a way to perform a Mail Merge in Excel.

A. Setting up the data.

1. Make a separate sheet for your data table
2. If your data is in another application like Access, use Data/Get External data to query the columns and rows that you want.
If your data is already in Excel, if you have formatted your table properly (one table per sheet, headings in row 1 starting in column A, table is contiguous) then you can use the technique in step 2 as well.

B. Setting up the "form"

The objective is to MAP data from selected columns in your Source Data Table to the Form, using Named Ranges. If you do not know what a Named Range is, please refer to Excel HELP.

1. Format the page(s) the way that you want your form to look
2. Each Merge Cell should have no other data in it.
3. Each Merge Cell should be named with the same name as the column heading on the data sheet.
NOTE: if any data column heading contains a space, then substitute an UNDERSCORE character for each space. For instance, a data heading like First Name should have a corresponding Merge Field name of First_Name.

C. Running Mail Merge

1. Select the rows that you want from your data using the AutoFilter or Advanced Filter if you filter in place.
2. Run MergePrint from Tools/Macro/Macros or a button.

D. The code that runs the Mail Merge

There is a procedure (macro) called MergePrint and a function called RangeName. Paste both of these into a MODULE in the Visual BASIC Editor. If you wish, you insert a Button on your sheet Form to run MergePrint.
[color blue]

Code:

Function RangeName(sName As String) As String
    RangeName = Application.Substitute(sName, " ", "_")
End Function
Sub MergePrint()
'set up your merge form by naming the merge fields _
 with the same name as the data fields you are importing.
'if any data field contains spaces, then substitute an _
 UNDERSCORE character for each space in the name.
    Dim wsForm As Worksheet, wsData As Worksheet
    Dim sRngName As String, r As Long, c As Integer
    Set wsForm = Worksheets("My Form") 'change to your sheet name
    Set wsData = Worksheets("My Data") 'change to your sheet name
    With wsData.Cells(1, 1).CurrentRegion
        For r = 2 To .Rows.Count
            If Not wsData.Cells(r, 1).EntireRow.Hidden Then
                For c = 1 To .Columns.Count
                    sRngName = wsData.Cells(1, c).Value
                    Range(RangeName(sRngName)).Value = wsData.Cells(r, c)
                Next
                wsForm.PrintOut
            End If
        Next
    End With
End Sub

[/color]