Hi All
I am using the following code to re-oder Peripherals devices.The problem I am having is that if a I have two devices on order for the one company it sends two e-mails to the company.Is there anything I can add to the code to only send one e-mail.
Dim db As Database
    Dim recReOrder As Recordset
    Dim recSupps As Recordset
    Dim objOutlook As New Outlook.Application
    Dim objMessage As MailItem
    Dim strSQL As String
    Dim strOrder As String
    Dim strItems As String


    Set db = CurrentDb()
    Set recSupps = db.OpenRecordset("qryReOrderSuppliers")


    While Not recSupps.EOF

        strSQL = "SELECT * FROM qryReOrder WHERE fkCompanyID = " & recSupps("CompanyID")
        Set recReOrder = db.OpenRecordset(strSQL)


        strItems = "Item" & vbTab & "Quantity"
        While Not recReOrder.EOF
            strItems = strItems & vbCrLf & recReOrder("Name") & _
                                  vbTab & recReOrder("ReOrderPoint")
            recReOrder.MoveNext
        Wend

       strOrder = "Hi " & recSupps("ContactName") & _
            vbCrLf & vbCrLf & _
            "Once again we are running short of the following items:" & _
            vbCrLf & vbCrLf & _
            strItems & _
            vbCrLf & vbCrLf & _
            "I'd be grateful if you could deliver these as soon as possible." & _
            vbCrLf & vbCrLf & _
            "Many Thanks" & _
            vbCrLf & vbCrLf & _
            "Brendan"

        If Not IsNull(recSupps("Email")) Then
            Set objMessage = objOutlook.CreateItem(olMailItem)
            With objMessage
                .To = recSupps("Email")
                .Subject = ""
                .Body = strOrder
                .Send

            End With

        End If





        recSupps.MoveNext
    Wend


    recSupps.Close
    Set recSupps = Nothing
    Set recReOrder = Nothing
    Set objOutlook = Nothing


End Sub
Thanks


Brendan Bartley

__________________
Brendan Bartley

Hi,

   I can't see the fields in your query, but if you have dupes, remove fields until you only get a single entry for each vendor with an outstanding order. Perhaps you can create a query with vendors with outstanding orders, then create a second query that just looks up the Vendor name in the first query, and set the Unique Values property to Yes. That will pull only one vendor regardless of the number of outstanding orders.

   Once you have the vendor names, then package your email.

HTH

mmcdonal

I have

Brendan Bartley

I have tried your suggestions and it does not work
Thanks

Brendan Bartley

I am working on the same issue this morning to send emails to users who have more than one issue, but package all issues in a single email to each user.

I am doing the following:

Create a view showing me the individual users with issues, but just their names, and unique values, so users with issues are in the view (query) once, no matter how many issues they have.

Then create a view showing me the users, and their issues.

I loop through the first view
   Then, using one name at a time, I loop through the second view
     Then taking each issue for this one user, I package it into the email
        Then I email this user
     End If
   End If
Loop

You have to nest them like this, and have your emails send from the center of the nested loops.

HTH

mmcdonal

Actually, I think you would do this:

Select Individual Company (rs1)
   Select All issues for Company (rs2)
     Capture those in a string variable like
       string = string & vbCrLf & rs2("Issue")
   rs2.MoveNext
   Loop
     If string <> "" Then
       Email function here
     End If
rs1.MoveNext
Loop



mmcdonal