Code To Send Each Customer An Email With All Of Their Orders (1 Viewer)

[alexl-123]

Hi,

I am trying to make a VBA Solution that will at the click of a button send an email out to all of my customers with updates on there orders
I have got a code to send an email to each record however this means that if a customer has 40 orders they will receive 40 emails, where as I want to send them just one with all of the orders within it so my plan is to search through the records and find records with the same email and put these into a tempory table which I can use as the record set for my mailing sub however the current code I have seams to not function correctly or just crash my computer can anyone help?
please see code below

Option Compare Database
Private Sub Command2_Click()
Dim dbS As DAO.Database
Dim rst, drst As DAO.Recordset
Dim tblName, aVar As Variant
Dim sqlStr, Email, Order_ID, Name, Vart As String
Dim i, a As Integer
Set dbS = CurrentDb()
Set rst = dbS.OpenRecordset("Test")
Set drst = dbS.OpenRecordset("Test")

With rst
Do Until rst.EOF

Email = .Fields("Email_Address")
Name = .Fields("empName")
'loop through each record to find matches to first email
        With drst
        i = 0

        Do Until drst.EOF

         i = i + 1
         drst.MoveFirst
         If .Fields("Email_Address") = Email Then
         Vart = Vart & I
         Vart = .Fields("Order_ID")
         End If
         drst.MoveNext

        Loop
        End With

'open temp table and add record
dbS.OpenRecordset (Temp)

    .Append
    !Name = .Fields("Name")
    !Email_Address = .Fields("Email_Address")
    'loop through Order ids and add to new record
     a = 0
         Do Until Vart = ""
         a = a + 1
         Vart = Vart & a
         Order_ID = Order_ID & a
        !Order_ID = Vart
        Loop
    .Update
    .Close
Loop

End With
End Sub

 

[Cronk]

What have you debugging have you done? Have you stepped through the code using F8 to see which line is cause the problem?

I didn't look at your code in detail. It's too hard without indentation and you not including the CODE tags (the # button) at the top of the message window.

 

[alexl-123]

Hi, the Debugging I was doing was running the code and seeing what errors it returned then amending appropriately, however I have now got to this stage and Access just crashes when I try to run it. I have tried the F8 key you suggested and it doesn't appear to do anything?

sorry about not using the code tags I am new to the forum and wasn't sure how to do this please see above I have amended my original post to use code tags and have the indentation

thanks in advance for your help

 

[alexl-123]

Hi, I already have a sub which sends the emails for me the issue is that if 1 customer has 40 orders and I updated them all to dispatched then It would generate 40 emails to that one customer where as I want to send one with all of their orders

I have a table (test)
It's structure is as below
empName
Email_Address
Order_ID


I want to loop through and take all the records with the same email and put them in a temporary table with all the Order_IDs that they have eg


Customer1 ,"customer1's email", order 123 ,order 473


Then when I run my emailing sub it will send each customer all of their requests in one email rather than an email per order

 

[spikepl]

my plan is to search through the records and find records with the same email and put these into a tempory

Not a good plan.

Make a query that gives you your orders ordered by email or customer id. Maka a loop where you save the email/customerID for each record. For each record, check if current email/customerID is the same as the one saved for the previous record. If yes, then add orders to the mail (to the same recipient). If not then send the already built mail and start building a new one.

 

[alexl-123]

Hi,

why is this not a good idea and how would I make a loop to do as you suggested?

Thanks

 

[Cronk]

I would use two loops. The first based on looping through each customer which has at least one order. Generate the email in this loop. Then have an inner loop based on a recordset of all relevant orders for the customer. The inner loop adds the order attachments.

 

[alexl-123]

okay how would this work, what is wrong with my initial code?

 

[Cronk]

Where do you open the recordset with the orders for the customer?

Going back to my first post in this thread, debugging, put breakpoints in your code by pressing F9, run the code, it will stop on the breakpoint line. Then step through execution by pressing F8 a line at a time, or F5 to continue running. Examine the value of variables in the immediate window. Google to get more detail.

 

[alexl-123]

just to provide you with some more information to help you all help me imagine this scenario, a customer orders 40 items that creates 40 records with 40 email addresses all the same when I use my update query to mark these all as dispatched another query runs and collects the jobs and then sends an email for each record, that's what I currently have.

I want to evolve this so each customer gets 1 email with all their orders in as apposed to 40 emails one for each

 

[alexl-123]

simply I want

Current data

Name- Steve-steve-paul-steve-susan-paul
email- stevesmail-stevesmail-paulsmail-stevesmail-susansmail-paulsmail
order- 1-2-3-4-5-6

Desired Data
name - steve-paul-susan
emails- stevesmail-paulsmail-susansmail
order1- 1-3-4
order2- 2-5-
order3- 3- -

 

[alexl-123]

Where do you open the recordset with the orders for the customer?

Going back to my first post in this thread, debugging, put breakpoints in your code by pressing F9, run the code, it will stop on the breakpoint line. Then step through execution by pressing F8 a line at a time, or F5 to continue running. Examine the value of variables in the immediate window. Google to get more detail.

Hi,
I only have one table which has the structure

name
email
Order_ID


with regards to the debugging I have changed the code

Option Compare Database
 Private Sub Command2_Click()
Dim dbS As DAO.Database
Dim rst, drst, nrst As DAO.Recordset
Dim tblName As Variant
Dim sqlStr, Email, Order_ID, Namea, Vart, avar As String
Dim i, a As Integer
 Set dbS = CurrentDb()
Set rst = dbS.OpenRecordset("Test_OLD")
Set drst = dbS.OpenRecordset("Test_OLD")
Set nrst = dbS.OpenRecordset("Temp")
 
With rst
Do Until rst.EOF
 
    Email = .Fields("Email_Address")
    Namea = .Fields("empName")
    'loop through each record to find matches to first email
    With drst
    i = 0
    
    drst.MoveFirst
    Do Until drst.EOF
    
     
        i = i + 1
        If .Fields("Email_Address") = Email Then
        avar = "avar" & i
        avar = .Fields("Order_ID")
        End If
        drst.MoveNext
        
    Loop
    End With
    
    'open temp table and add record
    
    With nrst
    
     .AddNew
     !empName = Namea
     !Email_Address = Email
     'loop through Order ids and add to new record
        a = 0
     Do Until Vart = ""
        a = a + 1
        Order_ID = "Order_ID" & a
        !Order_ID = avar
     Loop
     .Update
      End With
     rst.MoveNext
     
Loop
    
End With
 End Sub

now I basically get a copy of the originating table but with no Order IDs

 

[Cronk]

You have made this query harder on everyone trying to help by not telling us this detail from the start. Most would assume a normalised database with a table of customers and a related table of orders.

There is still some missing information. Where do you record the fact that you have acknowledged previous orders?

 

[alexl-123]

Hi Apologies about this,
previous orders are not added into this query as when the query is run it assigned a batch id to all the records then when that batch is dispatched the query criteria to send emails is based upon them being in that batch

 

[Cronk]

Please provide your table structure.

 

[alexl-123]

Hi My Table Structure is as follows

1 table
Name Field
email Field
Order ID field
product field
status field
postcode field
house no field
batch Id Field

hope this helps

 

[Cronk]

Getting information out of you is as easy as water from a stone. You could have included field types. I will assume all fields are text except the ID fields. assumed to be numeric.

First of all "Name" is not a good field name; it's a reserved word in Access. Let's call it Customer.

Create a query of all Customers and email with Open orders

select Customer, Email from tblYourTable where Status='Open' group by Customer, Email;

Open a recordset based on this and loop through it.

Within the loop, open another recordset of orders for the current customer with all open orders for that customer

select OrderID, Customer,Email, Product from tblYourTable Where Status ='Open' And Email = '" & rst!Email & "'"

This could have been post #2 in this thread.