By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,671 Members | 1,278 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,671 IT Pros & Developers. It's quick & easy.

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

P: 24
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?
Aug 7 '12 #1
Share this Question
Share on Google+
6 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,131
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.....
Aug 7 '12 #2

P: 24
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!
Aug 7 '12 #3

twinnyfo
Expert Mod 2.5K+
P: 3,131
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!
Aug 7 '12 #4

P: 24
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub notifyCustomer_Click()
  2.  
  3.  
  4. Dim strTo, strCC, strSubject, strBody As String
  5. Dim objOutlook
  6. Dim objEmail
  7. Dim customerName, notificationItem, notificationDate
  8.  
  9. Set customerName = Me.customerName
  10. Set noficationItem = Me.noficationItem 
  11. Set notificationDate = Me.notificationDate
  12.  
  13. strTo = customerName
  14. strCC = "Any other recipients"
  15. strSubject = "Email Subjet"
  16. strBody = customerName & notificationItem & notificationDate & " My email body"
  17.  
  18.  
  19. Set objOutlook = CreateObject("Outlook.application")
  20. Set objEmail = objOutlook.CreateItem(olMailItem)
  21.  
  22. With objEmail
  23.     .To = strTo
  24.     .CC = strCC
  25.     .Subject = strSubject
  26.     .HTMLBody = strBody
  27.     .Display
  28. End With
  29.  
  30.  
  31. Exit Sub
  32.  
  33. End Sub
Aug 7 '12 #5

twinnyfo
Expert Mod 2.5K+
P: 3,131
First, I would include a command button for NotifyAll. Here is some code that should get you pretty close:

Expand|Select|Wrap|Line Numbers
  1. Private Sub NotifyAll_Click()
  2. On Error GoTo EH
  3.     Dim dbCustomers As Database
  4.     Dim rstCustomers As Recordset
  5.     Dim dbCustomerInfo As Database
  6.     Dim rstCustomerInfo As Recordset
  7.     Dim intCustomerRecords As Integer
  8.     Dim strSQL As String
  9.     Dim strTo As String
  10.     Dim strCC As String
  11.     Dim strSubject As String
  12.     Dim strBody As String
  13.     Set dbCustomers = CurrentDb()
  14.     strSQL = "SELECT * FROM tblCustomers GROUP BY CustomerName;"
  15.     Set rstCustomers = dbCustomers.OpenRecordset(strSQL, dbOpenDynaset)
  16.     If Not rstCustomers.EOF Then
  17.         rstCustomers.MoveFirst
  18.         Do While Not rstCustomers.EOF
  19.             strTo = rstCustomerInfo("CustomerEMail")
  20.             strCC = "Any other recipients"
  21.             strSubject = "E-mail Subject Line"
  22.             strBody = "Dear " & rstCustomers("CustomerName") & vbCrLf & vbCrLf
  23.  
  24.             strSQL = "SELECT * FROM tblCustomers WHERE CustomerName = '" & rstCustomers("CustomerName") & "';"
  25.             Set rstCustomerInfo = dbCustomers.OpenRecordset(strSQL, dbOpenDynaset)
  26.             If Not rstCustomerInfo.EOF Then
  27.                 rstCustomerInfo.MoveLast
  28.                 intCustomerRecords = rstCustomerInfo.RecordCount
  29.                 rstCustomerInfo.MoveFirst
  30.             Else
  31.                 intCustomerRecords = 0
  32.             End If
  33.             strBody = strBody & "We found " & intCustomerRecords & _
  34.                 " records on file with us." & vbCrLf & vbCrLf
  35.             Do While Not rstCustomerInfo.EOF
  36.                 strBody = strBody & rstCustomerInfo("NotificationItem") & _
  37.                     rstCustomerInfo("Notification Date") & vbCrLf
  38.             Loop
  39.             rstCustomerInfo.Close
  40.             strBody = strBody & "We appreciate your quick response to us, blah blah blah."
  41.             'NOTE:  for this type of e-mail, I prefer to use the SendObject Method...
  42.             DoCmd.SendObject acSendNoObject, , , strTo, strCC, , strSubject, strBody, True
  43.         Loop
  44.         dbCustomerInfo.Close
  45.         rstCustomers.Close
  46.         dbCustomers.Close
  47.     End If
  48.     Exit Sub
  49. EH:
  50.     MsgBox Err.Number & " " & Err.Description
  51.     Exit Sub
  52. Exit Sub
Aug 7 '12 #6

P: 24
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub NotifyAll_Click()
  2. On Error GoTo EH
  3.     Dim dbCUSTOMER As Database
  4.     Dim rstCUSTOMER As Recordset
  5.     Dim dbCUSTOMERInfo As Database
  6.     Dim rstCUSTOMERInfo As Recordset
  7.     Dim intCUSTOMERRecords As Integer
  8.     Dim CUSTOMER
  9.     Dim qryUniqueCUSTOMER
  10.     Dim itemNumber
  11.  
  12.     Dim strSQL, strTo, strCC, strSubject, strBody As String
  13.     Set itemNumber = Me.itemNumber
  14.     Set dbCUSTOMER = CurrentDb()
  15.     strSQL = "SELECT CUSTOMER FROM qryUniqueCUSTOMER GROUP BY CUSTOMER;"
  16.  
  17.     Set rstCUSTOMER = dbCUSTOMER.OpenRecordset(strSQL, dbOpenDynaset)
  18.  
  19.     If Not rstCUSTOMER.EOF Then
  20.         rstCUSTOMER.MoveFirst
  21.         Do While Not rstCUSTOMER.EOF
  22.             strTo = rstCUSTOMERInfo("CustomerEMail")
  23.             strCC = "CC Email addresses"
  24.             strSubject = "Notification for " & itemNumber
  25.             strBody = rstCUSTOMER("CUSTOMER") & vbCrLf & vbCrLf
  26.  
  27.             strSQL = "SELECT * FROM qryUniqueCUSTOMER WHERE CUSTOMER = '" & rstCUSTOMER("CUSTOMER") & "';"
  28.             Set rstCUSTOMERInfo = dbCUSTOMER.OpenRecordset(strSQL, dbOpenDynaset)
  29.             If Not rstCUSTOMERInfo.EOF Then
  30.                 rstCUSTOMERInfo.MoveLast
  31.                 intCUSTOMERRecords = rstCUSTOMERInfo.RecordCount
  32.                 rstCUSTOMERInfo.MoveFirst
  33.             Else
  34.                 intCUSTOMERRecords = 0
  35.             End If
  36.             strBody = strBody & "We found " & intCUSTOMERRecords & _
  37.                 " records on file with us." & vbCrLf & vbCrLf
  38.             Do While Not rstCUSTOMERInfo.EOF
  39.                 strBody = strBody & rstCUSTOMERInfo("NotificationItem") & _
  40.                     rstCUSTOMERInfo("Notification Date") & vbCrLf
  41.             Loop
  42.             rstCUSTOMERInfo.Close
  43.             strBody = strBody & "We appreciate your quick response to us, blah blah blah."
  44.             'NOTE:  for this type of e-mail, I prefer to use the SendObject Method...
  45.             DoCmd.SendObject acSendNoObject, , , strTo, strCC, , strSubject, strBody, True
  46.         Loop
  47.         dbCUSTOMERInfo.Close
  48.         rstCUSTOMER.Close
  49.         dbCUSTOMER.Close
  50.     End If
  51.     Exit Sub
  52. EH:
  53.     MsgBox Err.Number & " " & Err.Description
  54.     Exit Sub
  55. Exit Sub
  56.  
Aug 8 '12 #7

Post your reply

Sign in to post your reply or Sign up for a free account.