Maintenance reminders using email (complex question)

[avarusbrightfyre]

I have a database that tracks maintenance history as well as suggested repairs for items stored in the database. I already have an email merge set up to invoice customers for their regularly scheduled payment based on a query. What I need now is a way to have the database sift through the records, select records that have suggested repairs attached to them and send one email to each client with a tabular list in the body of the email displaying the suggested repair table entries pertaining to that client.

The configuration for all this is a main form with just the customer information attached to it, then a subform that records the maintenance history for that customer and a second subform that records "suggested repairs", all connected through the "ID" field on each form. I'm looking for a command button that will send these email reminders in one click, if possible.



Is there a way to set up a mass email this way? Having to sit down and write one email for each client listing his "suggested repairs" would be very time consuming. So far I've only seen merging fields for one record on from one table. I imagine this would require drawing information from several tables, and I've never seen a way to do an auto-generated list within a merge. Is any of this feasible?

[kennejd]

You would set up a sub routine to loop through each customer and then pull the applicable information for the email. Similar to what you do for your invoice email I should think. Multiple tables is no problem because you could combine tables in a query...or open multiple recordsets in your code.