How send email using first record in query
Posted on 2013-10-24
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
Set rs = Nothing