Who Gets Letters?

In addition to letting users select a form letter, the BulkMail dialog box lets them select the letter's recipients by clicking one of four option buttons: All, Last Names, Companies, or States.

When users select Last Name, Companies, or States, they can further target the mailing by clicking particular names, companies, or particular states. For example, if users select the States option, they can then select California, Texas, and New York from the multiselect list box.

Here's how this works: The Bulk Mail Database worksheet contains the contact data in a range named Database. ("Database" is a Microsoft Excel reserved name that you must use to manipulate data as a database through code.) When users select an option such as Companies or States, the ListDistinctEntries subroutine uses the AdvancedFilter method (shown below) to query the database and select the data specified.

objDatabase.Range("Database").Columns(iField).AdvancedFilter _
    Action:=xlFilterCopy, CopyToRange:=.Cells(2, 10), Unique:=True

The variable objDatabase in this code represents the Database range on the Bulk Mail Database worksheet: iField is the number of the column that users select (1 is Last Name, 3 is Company, and 6 is State), and strField is the column heading (Last Name, Company, or State). The Unique argument is set to True to return only nonduplicate entries in the selected column. Also, setting the Action argument to xlFilterCopy copies the entries to the location specified by the CopyToRange argument.

The ListDistinctEntries routine then fills the multiselect list box in the BulkMail dialog box with the entries selected. From here, users can further target recipients. The code for ListDistinctEntries follows.

Sub ListDistinctEntries(iField As Integer)
    On Error GoTo ListError

    Dim objCriteriaRange As Object

'Run an Advanced Filter to get a list of the distinct entries
'in the column specified by the argument iField.
    With objDatabase
        strField = .Cells(1, iField).Value
        .Range("Database").Columns(iField).AdvancedFilter _
          Action:=xlFilterCopy, CopyToRange:=.Cells(2, 10), _
          Unique:=True
        Set objCriteriaRange = .Cells(2, 10).CurrentRegion
    End With
    
'Trim off the column heading and sort the list.
    With objCriteriaRange
        .Rows(1).Delete
        .Sort Key1:=.Cells(1, 1)
    End With
    
'Fill the criteria list box with the extracted data.
    Dim i As Integer
    objCriteriaList.RemoveAllItems
    For i = 1 To objCriteriaRange.Rows.Count
        objCriteriaList.AddItem objCriteriaRange.Rows(i).Value
    Next i
    
'Clear the extracted data range.
    objCriteriaRange.ClearContents
    Set objCriteriaRange = Nothing
    Exit Sub
    
ListError:
    If Err <> 0 Then
        GlobalErrorMsg "ListDistinctEntries", Err
        Exit Sub
    End If
End Sub