Creating a temp mailing list

[bbrazeau]

I am working on a DB for my church. It has a main form with member details pulled from other tables (ie. Gender,State,Affiliated church, etc) It has many members and I would like to provide a way for the pastor to dynamically select a group of members and run a mail merge on it to produce form letters to those selected. What is the best way to accomplish this? Could someone give an example?

[June7]

For mail merge or Access report, can use parameterized query.

Options:

1. a yes/no field in table, report is filtered by the setting of this field, use a form to present the list of members for selection, code could set all records to 'no' when form opens (or at the end of process when form closes)

2. filter report by attributes (gender, state, affiliation, etc)

3. combination of the above

A parameterized query can be the basis for report RecordSource or code can construct filter string and pass to the report with WHERE CONDITION:

DoCmd.OpenReport "report name", , , strFilter

[DepricatedZero]

Make sure you put an associated email address in the user table, and then you can call the list of email addresses for each user from a query and concatenate them into a single string, then just put the string in the BCC line.

Here are some sample functions I use in my code that work similarly

For these, I first populate a ListBox with a Query of email addresses.

Private Function FetchCustomers(sourceBox As ListBox) As String
    If sourceBox.ListCount > 0 Then
        Dim emails As String
        Dim i As Integer
        For i = 0 To sourceBox.ListCount
            emails = emails & "; " & sourceBox.ItemData(i)
        Next
        
        FetchCustomers = emails
    End If
End Function

This returns a String which contains all the entries in the listbox, delimited by a semicolon (

With that string you could create an outlook item and open it or send it to those emails like so

Dim oApplication As Outlook.Application
Dim oItem As Outlook.MailItem
Dim oAccount As Outlook.Accounts
Dim signature As String

Private Sub SendMail()
   Set oApplication = New Outlook.Application
   Set oItem = oApplication.CreateItemFromTemplate("String Path to an HTML Template for your Email")
   oItem.BCC = FetchCustomers(lstCustomers)
   oItem.Send
End Sub

Or you could use oItem.Display to have it pop the email open in outlook so you can validate it before sending.