Expert: Scottgem - 6/30/2009

Question
I have 2 very simple tables - one listing multiple supplier numeric IDs and associated email address in 2 seperate fields, the other many rows of sales data, unique by supplier id and a product number (many suppliers, many products). I am trying to create code that will automatically create an email by supplier, with an attachment (.xls or .csv)of their specific data from the sales table. I cannot work through how to connect the email address in a recordset loop, to creating a specific qry for that supplier and attaching that result to the email - can you help ?

Answer
Have you looked at the SendObject method? That allows you to send the results of a query as an XLS file to an e-mail address.

If you want to send a filtered recordset within a loop, then you will have to use a Querydef to define the query with a filter based on the supplier. As to the e-mail address you simply populate that paramter with the e-mail field from the current record.

If just sending a single e-mail to a selected supplier, you use a stored query that filters for the supplier selected on a form. You set the criteria for the supplierID using the syntax:

=Forms!formname!controlname

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA