Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How send email using first record in query

Posted on 2013-10-24
1
Medium Priority
?
374 Views
Last Modified: 2013-10-30
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!"

        Else
            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.Edit
            rs![EmailReminderSent] = True
            rs![EmailSentDate] = Date
            rs.Update
        End If

        rs.MoveNext
    Wend
    rs.Close
    Set rs = Nothing

End Sub
0
Comment
Question by:SteveL13
1 Comment
 
LVL 31

Accepted Solution

by:
Helen Feddema earned 2000 total points
ID: 39598256
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
         msg.Display
         'msg.Send
      End If
      rst.MoveNext
   Loop
   
ErrorHandlerExit:
   rst.Close
   Set rst = Nothing
   Set appOutlook = Nothing
   Exit Sub

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

End Sub

Open in new window

0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question