Paul.
Once again thanks for all your help. Here is the final code:
Code:
'Build the address string
Dim dbCurrent As DAO.Database
Dim rs As DAO.Recordset
Dim intCounter As Integer
Dim strAddresses As String
Dim blnEMailSent As Boolean
Dim strSQL As String
Set dbCurrent = CurrentDb()
'Define SQL this is always the same as created above
'tbl_Bulk_EMail from user defined parameters e.g. All Members, New Members etc.
strSQL = "SELECT DISTINCT EMail FROM tbl_Bulk_EMail"
'Open a recordset to obtain recipients
Set rs = dbCurrent.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then
'Get total record count
rs.MoveLast
rs.MoveFirst
intRecordCount = rs.RecordCount
'Loop through the record set sending an e-mail every 10 addresses
Do While Not rs.EOF
intCounter = intCounter + 1
strAddresses = strAddresses & rs!EMail & ";"
If intCounter = 10 Then
strAddresses = Left(strAddresses, Len(strAddresses) - 1)
If SendMail(strAddresses, strDefaultAddress, strSubject, strMessage, strAttachmentName, strSignature, intImportance) Then
intCounter = 0
strAddresses = ""
blnEMailSent = True
Else
Err.Raise -101
End If
End If
rs.MoveNext
Loop
End If
'Send any remaining email
If Len(strAddresses) > 0 Then
strAddresses = Left(strAddresses, Len(strAddresses) - 1)
If SendMail(strAddresses, strDefaultAddress, strSubject, strMessage, strAttachmentName, strSignature, intImportance) Then
strAddresses = ""
blnEMailSent = True
Else
Err.Raise -101
End If
End If
I will mark the thread as solved.