Sending selective bulk emails based on domain

[avayaman]

I have a database for a club. We use email forwards to prtoect real addresses and they all end in the same thing, or one of 2, actually. We have Australian members. North American members end in "natcoa.com", Australian in "intcoa.com". I want to be able to send bulk e-mails to either one group or the other, or both. Right now the following routine will paste all the e-mails into Outlook. (natcoa@natcoa.com is the default that copies back to me). Can anyone tell me how to make this more selective? I need to detect if a mail address int he same field ends in intcoa.com or natcoa.com. I do not mind using 3 seperate VB buttons to reference the sub. (one for natcoa, one for intcoa & one for both) The one choice you see in this routine already, is simply to select real emails that are in a different field or the forwards. I'm not concerned about the real emails.



Private Sub BulkMail_Click()
On Error GoTo Err_BulkMail_Click


Dim varEmail
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

If MsgBox("Do you want to use NATCOA E-Mails (Select 'No' to use real E-Mails)? ", vbYesNo, "EMAIL FORMAT") = vbYes Then


rs.MoveFirst

Do While Not rs.EOF()
If rs!ForwardEmail & "" <> "" Then
varEmail = varEmail & ";" & rs!.ForwardEmail


End If
rs.MoveNext

Loop

varEmail = Mid(varEmail, 2)
DoCmd.SendObject acSendNoObject, , , "natcoa@natcoa.com", , varEmail, "Group E-mail to all Natcoa/Intcoa Members"

Else

rs.MoveFirst
Do While Not rs.EOF()
If rs!RealEmail & "" <> "" Then
varEmail = varEmail & ";" & rs!RealEmail
End If
rs.MoveNext
Loop

varEmail = Mid(varEmail, 2)
DoCmd.SendObject acSendNoObject, , , "natcoa@natcoa.com", , varEmail, "Group E-mail to all Natcoa Members"
End If
Exit_BulkMail_Click:
Exit Sub

Err_BulkMail_Click:
If Err.Number <> 2501 Then
MsgBox Err.Description
End If
Resume Exit_BulkMail_Click
End Sub

Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com

 

[Eupher]

Hi, Paul,

Well, the method that presents itself to me is to create an option group control on your form with 3 choices: natcoa, intcoa, both. Then use that as the criteria to query your database and open a recordset of the desired records. Then, as you are doing now, simply loop through the recordset to build your address list.

Let me know if you need help building the SQL for the queries. You could use saved queries or just use SQL strings in your code. Saved queries are optimized for the Jet engine and are supposed to be faster. But I often just use SQL strings; either will work.

HTH,

Ken S.