Emailing part recordsets to email addresses in one of the fields (1 Viewer)

[prmitchell]

Hello
I have a table - tblBarSessions - with similar data to the following which is just an example

Subject	ShiftDate	StartTime	eA
Shed Lunch I: Bill	1/03/2023​	11:00 AM​	bgates@iinet.com.au
Bowls: Bill	8/03/2023​	3:45 PM​	bgates@iinet.com.au
Shed Lunch: Satya	1/03/2023​	11:00 AM​	snadella@hotmail.com
Bowls: Satya	15/03/2023​	3:45 PM​	snadella@hotmail.com
Bowls: Satya	22/03/2023​	3:45 PM​	snadella@hotmail.com

I know how to program a loop to process the recordset
and I know how to use the DoCmd.SendObject to send all of the records in a recordset to an email address

but when reaching record three in the sample above - given it is a different email address from the previous - how do I exactly at that point send an email to the first email address with just the first two (in this case) records
and then to keep proceeding until again a different email address is encountered

advice / pseudocode / code / links - any or all will be most appreciated

Thanks in anticipation

 

[June7]

You mean you send all the records of a table/query/form/report? If you want to send a recordset that means VBA code to loop records and concatenate to send as part of email body.

If you want to send an attachment, loop a recordset of unique email addresses to open a filtered report and send that.

 

[Gasman]

I would get a distinct set of email addresses to control the loop.
Then a query to get all records for that email address.
Then your sendobject.

I have done it in the past with recordsets and storing the previous email address, and updating when needed, and sending just the required data for an email address, but that was all done via Outlook automation, not SendObject.

 

[MarkK]

but when reaching record three in the sample above - given it is a different email address from the previous - how do I exactly at that point send an email to the first email address with just the first two (in this case) records
and then to keep proceeding until again a different email address is encountered

Write a loop. Save the first email address. In each iteration of the loop after that, check if the saved address has changed. If so, send the email with the accumulated data, and start over.

 

[Pat Hartman]

Create a querydef that selects all the data you need to send out.
Create a second querydef that selects only the email from the first query and group by it so you end up with one row per email address.

Use the second query in a DAO loop. As you read each record from the email query, save the email address in a hidden text field on the form.

Create a third querydef that selects the first querydef but has a WHERE that refers to the hidden text field:

Select firstquery.*
From firstquery WHERE firstquery.email = Forms!yourform!yourhiddenemailfield

Then send that third query to the email located in the hidden text field on the form.