Find all records for a single customer and send them in e-mail

[ryaanmichael]

I have a table that contains customer records when they need an e-mail notification. The records only appear in the table when the customer needs a notification (this info is pulled from an HTML page). Sometimes there are multiple records for one customer because they need notification for multiple things. Right now I have a button that sends an email to the customer, but only for one record (its just a form that shows one record). What I want to do is compile all the records for a customer into one e-mail with the control of a button, rather than clicking through each record and sending individual e-mails. How can I do this?

[twinnyfo]

ryaanmichael,

First, how do you want to send the e-mails? I ask, because there are different ways to do this. If you want to send the data in the tables as an attached report, this will require different things. If you want to compile the body of an e-mail based on the multiple records, then this will require different coding. The principle will be the same though:

Build VBA code that builds a recordset based on the table. You may require two recordsets, once which is grouped by customer (so you only get the unique customer values) and one recordset with all the rest of the data associated with each customer.

Ther are multiple ways of doing this, either by filtering the data recordset by customer or by rebuilding a recordset each time a new customer record is reached.

Again, tell me your vision of what you want it to look like and we can move forward from there.....

[ryaanmichael]

Hi Twinnyfo,

My e-mail code send emails in Microsoft Outlook. The "send" button in my database pulls the data from a single record, puts it in the body of an outlook email, then "displays" it so I can send it out. It works great for one record, but I would love for the code to pull all records for a single user, and place all that data in the body of an e-mail.

Thanks for your help!

[twinnyfo]

Perfect! Start with the code that you currently have and nset it within some other code.

Step one: You will need to create a recordset of all the unique customers that require e-mails (i.e you may have 27 records, but only 15 unique customers). You can do this several ways: 1) cretae a query that generates that list, then use that to create your recordset, or 2) just use the same SQL statement for the query in 1) above and use that in your code. I sometimes find it easier to create the query and then generate the recordset on the query.

Next, your code will loop through the unique customers one at a time. Using that unique customer, create a second recordset that only pulls that customer's records from the table. Count how many records that customer has, because you may want to tell your customer, "Hey, we found 3 orders from you!"

Generate the body of the e-mail, starting with the main header or introduciton--informaiton you will tell every customer. Then, cycle through the records for that customer, adding necessary information along the way. Once you get through all their records, add the footer or closing information (your contact info, etc).

Then generate the e-mail, bring it up for you to review before you send, just like you do now.

Once you click send, this is where your code should loop back to the beginning, find the next customer and do it all over again.

Believe it or not, this is really not a very difficult set of code to do, but I will assist you with any snags you may come across along the way.

If you have troubles, please post code or attach a sample copy of your db. I'd be glad to take a look and trouble shoot!

Best of luck!

[ryaanmichael]

Hi Twinnyfo,

Thanks for your reponse. Please forgive my ignorance, I'm not too experienced in VBA. How can I get my code to loop through the unique records and find all the records for the unique person?

I made a query that pull the the unique customer name, but how do I do the rest? Here is a simplified version of my current code that only pulls one record:

Private Sub notifyCustomer_Click()
 
 
Dim strTo, strCC, strSubject, strBody As String
Dim objOutlook
Dim objEmail
Dim customerName, notificationItem, notificationDate
 
Set customerName = Me.customerName
Set noficationItem = Me.noficationItem 
Set notificationDate = Me.notificationDate
 
strTo = customerName
strCC = "Any other recipients"
strSubject = "Email Subjet"
strBody = customerName & notificationItem & notificationDate & " My email body"
 
 
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)
 
With objEmail
    .To = strTo
    .CC = strCC
    .Subject = strSubject
    .HTMLBody = strBody
    .Display
End With
 
 
Exit Sub
 
End Sub

[twinnyfo]

First, I would include a command button for NotifyAll. Here is some code that should get you pretty close:

Private Sub NotifyAll_Click()
On Error GoTo EH
    Dim dbCustomers As Database
    Dim rstCustomers As Recordset
    Dim dbCustomerInfo As Database
    Dim rstCustomerInfo As Recordset
    Dim intCustomerRecords As Integer
    Dim strSQL As String
    Dim strTo As String
    Dim strCC As String
    Dim strSubject As String
    Dim strBody As String
    Set dbCustomers = CurrentDb()
    strSQL = "SELECT * FROM tblCustomers GROUP BY CustomerName;"
    Set rstCustomers = dbCustomers.OpenRecordset(strSQL, dbOpenDynaset)
    If Not rstCustomers.EOF Then
        rstCustomers.MoveFirst
        Do While Not rstCustomers.EOF
            strTo = rstCustomerInfo("CustomerEMail")
            strCC = "Any other recipients"
            strSubject = "E-mail Subject Line"
            strBody = "Dear " & rstCustomers("CustomerName") & vbCrLf & vbCrLf
 
            strSQL = "SELECT * FROM tblCustomers WHERE CustomerName = '" & rstCustomers("CustomerName") & "';"
            Set rstCustomerInfo = dbCustomers.OpenRecordset(strSQL, dbOpenDynaset)
            If Not rstCustomerInfo.EOF Then
                rstCustomerInfo.MoveLast
                intCustomerRecords = rstCustomerInfo.RecordCount
                rstCustomerInfo.MoveFirst
            Else
                intCustomerRecords = 0
            End If
            strBody = strBody & "We found " & intCustomerRecords & _
                " records on file with us." & vbCrLf & vbCrLf
            Do While Not rstCustomerInfo.EOF
                strBody = strBody & rstCustomerInfo("NotificationItem") & _
                    rstCustomerInfo("Notification Date") & vbCrLf
            Loop
            rstCustomerInfo.Close
            strBody = strBody & "We appreciate your quick response to us, blah blah blah."
            'NOTE:  for this type of e-mail, I prefer to use the SendObject Method...
            DoCmd.SendObject acSendNoObject, , , strTo, strCC, , strSubject, strBody, True
        Loop
        dbCustomerInfo.Close
        rstCustomers.Close
        dbCustomers.Close
    End If
    Exit Sub
EH:
    MsgBox Err.Number & " " & Err.Description
    Exit Sub
Exit Sub

[ryaanmichael]

Hi Twinnyfo,

Thanks for your help again. I still can't get this to work. I updated the code your provided with the names of my querys/field names, but its still now working. I'm not sure I understand the strSQL line. My current query (qryUniqueCUSTOMER) provides the unique customer names already, should this SQL line be different? Here's what I got right now:

Private Sub NotifyAll_Click()
On Error GoTo EH
    Dim dbCUSTOMER As Database
    Dim rstCUSTOMER As Recordset
    Dim dbCUSTOMERInfo As Database
    Dim rstCUSTOMERInfo As Recordset
    Dim intCUSTOMERRecords As Integer
    Dim CUSTOMER
    Dim qryUniqueCUSTOMER
    Dim itemNumber
 
    Dim strSQL, strTo, strCC, strSubject, strBody As String
    Set itemNumber = Me.itemNumber
    Set dbCUSTOMER = CurrentDb()
    strSQL = "SELECT CUSTOMER FROM qryUniqueCUSTOMER GROUP BY CUSTOMER;"
 
    Set rstCUSTOMER = dbCUSTOMER.OpenRecordset(strSQL, dbOpenDynaset)
 
    If Not rstCUSTOMER.EOF Then
        rstCUSTOMER.MoveFirst
        Do While Not rstCUSTOMER.EOF
            strTo = rstCUSTOMERInfo("CustomerEMail")
            strCC = "CC Email addresses"
            strSubject = "Notification for " & itemNumber
            strBody = rstCUSTOMER("CUSTOMER") & vbCrLf & vbCrLf
 
            strSQL = "SELECT * FROM qryUniqueCUSTOMER WHERE CUSTOMER = '" & rstCUSTOMER("CUSTOMER") & "';"
            Set rstCUSTOMERInfo = dbCUSTOMER.OpenRecordset(strSQL, dbOpenDynaset)
            If Not rstCUSTOMERInfo.EOF Then
                rstCUSTOMERInfo.MoveLast
                intCUSTOMERRecords = rstCUSTOMERInfo.RecordCount
                rstCUSTOMERInfo.MoveFirst
            Else
                intCUSTOMERRecords = 0
            End If
            strBody = strBody & "We found " & intCUSTOMERRecords & _
                " records on file with us." & vbCrLf & vbCrLf
            Do While Not rstCUSTOMERInfo.EOF
                strBody = strBody & rstCUSTOMERInfo("NotificationItem") & _
                    rstCUSTOMERInfo("Notification Date") & vbCrLf
            Loop
            rstCUSTOMERInfo.Close
            strBody = strBody & "We appreciate your quick response to us, blah blah blah."
            'NOTE:  for this type of e-mail, I prefer to use the SendObject Method...
            DoCmd.SendObject acSendNoObject, , , strTo, strCC, , strSubject, strBody, True
        Loop
        dbCUSTOMERInfo.Close
        rstCUSTOMER.Close
        dbCUSTOMER.Close
    End If
    Exit Sub
EH:
    MsgBox Err.Number & " " & Err.Description
    Exit Sub
Exit Sub