Hi James,
Maybe something like this? Loop through values in a table (recordset) and
assign them to a string for dropping into the To argument of the SendObject
action.
Sub SendGroupEmail()
'If using Acc 97 change recordsets and
'connection to DAO equivalents.
On Error GoTo Err_SendGroupEmail
Const CancelSend = 2501
Dim cnYourConnection As ADODB.Connection
Dim rsYourRecordset As ADODB.Recordset
Dim strSQL As String
Dim intCtr As Integer
Dim i As Integer
Dim strTo As String
strSQL = "SELECT EmailAddress FROM tblEmailAddresses WHERE EmailAddress IS
NOT NULL"
Set cnYourConnection = CurrentProject.Connection
Set rsYourRecordset = New ADODB.Recordset
With rsYourRecordset
.Open strSQL, cnYourConnection, adOpenKeyset, adLockOptimistic
If Not .EOF Then
'Uusually movelast first to force
'accessing of all records but
'with only a small number we
'shouldn't need to
.MoveFirst
intCtr = .RecordCount
'Loop thru records and assign values to a string.
'Add a semi colon to separate the address until
'the last address is processed
For i = 0 To intCtr - 1
strTo = strTo & rsYourRecordset.Fields(0) & IIf(i <=
(intCtr - 2), ";", "")
.MoveNext
Next i
End If
End With
'Close what you opened
rsYourRecordset.Close
Set rsYourRecordset = Nothing
Set cnYourConnection = Nothing
'Send your email - Change/add optional arguments to suit
'The format argument is left out here so the user will be
'asked to nominate it when the email is generated. Add it in
'if you want a fixed format.
DoCmd.SendObject acSendTable, "tblEmailAddresses", , Trim(strTo), , , _
"YourSubject", "YourMessage", True
Exit_SendGroupEmail:
Exit Sub
Err_SendGroupEmail:
If CancelSend Then
Resume Next
Else
MsgBox Err.Number & ": " & Err.Description
End If
Resume Exit_SendGroupEmail
End Sub
HTH
Jamie
