How send email using first record in query

I have this code in the onload event of a form.  When the form is loaded an email is automatically send to recipients that come from a query.  But now I only want ONE email to be send per unique InvoiceN.  How does this code have to be changed to do this?

Private Sub Form_Load()
   Dim strSQL As String
   Dim rs As DAO.Recordset
   Dim strEmail As String, strSubject As String, Customer As String, InvoiceN As String, strToAddress As String, ToEmail As String

   strSubject = "The afore mentioned customer's contract is up for renewal. Please arrange to send the invoice."
   Set rs = CurrentDb.QueryDefs("qryFindRecordsForAutoEmail").OpenRecordset

   While Not rs.EOF

        If IsNull(rs!ccEmpEmailAddresses) Or IsNull(rs!EmailAddress) Then
            MsgBox "Customer " & rs!Customer & ", Record ID " & rs!ID & ", is missing either the Email Address or the c/c Email Address!"

            Customer = rs!Customer
            InvoiceN = rs!InvoiceN
            strEmail = rs!ccEmpEmailAddresses
            ToEmail = rs!EmailAddress
            strToAddress = rs!ccEmpEmailAddresses
            DoCmd.SendObject acSendNoObject, , , ToEmail, strToAddress, , strSubject, "Customer: " & [Customer] & " - Invoice Number: " & [InvoiceN], False
            rs![EmailReminderSent] = True
            rs![EmailSentDate] = Date
        End If

    Set rs = Nothing

End Sub
Who is Participating?
Helen FeddemaConnect With a Mentor Commented:
You need to iterate through the recordset, creating a separate email for each member.  Here is some code that does this, using the more reliable Automation method:

Public Sub EMailAllContacts()
'Created by Helen Feddema 31-Oct-2009
'Last modified by Helen Feddema 31-Oct-2009

On Error GoTo ErrorHandler

   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim appOutlook As Outlook.Application
   Dim msg As Outlook.MailItem
   Dim strEmail As String
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset("qryContacts")
   Set appOutlook = GetObject(, "Outlook.Application")
   Do While Not rst.EOF
      strEmail = Nz(rst![EmailName])
      If strEmail <> "" Then
         'Create email
         Set msg = appOutlook.CreateItem(olMailItem)
         msg.To = strEmail
         msg.Subject = "Subject"
         msg.Body = "Message"
         'Comment out next line and uncomment Send line
         'to send automatically
      End If
   Set rst = Nothing
   Set appOutlook = Nothing
   Exit Sub

   'Outlook is not running; open Outlook with CreateObject
   If Err.Number = 429 Then
      Set appOutlook = CreateObject("Outlook.Application")
      Resume Next
      MsgBox "Error No: " & Err.Number _
         & " in EMailAllContacts procedure" _
         & "; Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Sub

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.