Stuck on Email from MS Access

[carahorse]

Hi, using Ms acess 2007

this code is working fine buuutttt....

This one worked, but sent everyones records
DoCmd.SendObject _
acSendReport, _
"Contact Report", _
acFormatHTML, _
"realridergmail.com", _
, _
, _
"Reservations", _
"Reservations", _
False
End Sub



I need it to send just the records belonging to the customer, they shouldnt be able to see anyone elses...been looking around online I am stuck on this

I set up a test database to learn how to do it,

sites.google.com/ site/adopt12horses/ accesshelp screen shots located on this page



I am so excited, how to send the dates that belong only to each customer? Once I get this help I will be off and running! =)

PS this is for a non profit for kids and horses so your help will be doing great things!
**emails changed to protect privacy

 

[JHB]

How does your report looks like?
You have to set the report up, (use a query), so it only shows records belonging to the customer.

 

[carahorse]

I have two posts to go to post links

 

[carahorse]

it wont let me post any pics

 

[carahorse]

https://sites.google.com/site/adopt12horses/accesshelp

 

[carahorse]

There now I have a link to post pics of my sample tables and query...how to email just each person their dates?

 

[JHB]

You need a loop to pick up each "Contact ID" from the "Contact Query".

1. Take a copy of the query "Contact Query", call the copy "Contact QueryEmail".
2. Open the report "Contact Report" and change the record source to "Contact QueryEmail" and save it.
3. Take a copy of the below code, and set it in where you have the line DoCmd.SendObject acSendReport, "Contact Report", acFormatHTML, "realridergmail.com", , , "Reservations", "Reservations", False in you code now.

  Dim dbs As Database, rst As Recordset
  Dim QDF As DAO.QueryDef, SQLString As String

  Set dbs = CurrentDb
  
  Set rst = dbs.OpenRecordset("Contact Query")
  Set QDF = dbs.QueryDefs("Contact QueryEmail")
  SQLString = dbs.QueryDefs("Contact Query").SQL
  If Not rst.EOF Then
    Do
      QDF.SQL = Replace(SQLString, ";", "") & " WHERE [Contact ID]=" & rst![Contact ID]
      DoCmd.SendObject acSendReport, "Contact Report", acFormatHTML, "realridergmail.com", , , "Reservations", "Reservations", False
      rst.MoveNext
    Loop Until rst.EOF
  End If