I am trying to create a command button that will email 1 or 2 dozen people at a time. I have the code for the email more or less figured out, its just the recordset i'm having trouble with. I'm not real knowledgable on the recordset stuff, and I ALWAYS have tons of errors thrown at me when I try to do it. Even when using exsisting code that I found, like in this case.
I have Access 2007.
Here is my code so far:
Code:
Private Sub cmdBulkEmail_Click()
Dim cn As DAO.Connection
Dim rs As DAO.Recordset
Dim strEmails As String
Set cn = CurrentProject.Connection
Set rs = New DAO.Recordset
With rs
Do While Not .EOF
If [pu BulkEmail] = -1 Then
strEmails = strEmails & .Fields("Email") & "; "
strClientNames = strClientNames & .Fields("Merchant") & " | "
[pu BulkEmail] = 0
End If
.MoveNext
Loop
.Close
End With
strEmails = Left(strEmails, Len(strEmails) - 2)
strClientNames = Left(strClientNames, Len(strClientNames) - 3)
MsgBox (strEmails)
MsgBox (strClientNames)
This command button is on the form footer of a continuous form. I have checkboxes on the details that can be checked. This button should then loop through each record, skipping records that do not have the check box checked, and it should gather the email addresses and names for later use.
I keep getting errors like "invalid use of the word new" for the Set rs = New DAO.Recordset line. and set cn = CurrentProject.Connection errors, etc.
Any idea how I can fix this to work? Im ready to pull my hair out!