seperate email for each record - same report

[alcifer]

Good day,
I have a pretty good idea how to do this but I am having some problems with the nitty gritty stuff.

I have a query that sorts out which records I want to email.
I have a report that is tied to the query.
As it stands now, the report shows all of the records based on this query. What I would like to do is send a seperate email for each record.
Don't have a lot of experience with reports and would need some assistance as to where to put the
code to piece this together.
I'm thinking something along the lines of
Do until rs.EOF
<<sendobject...>>
loop
but I'm not sure..
Many thanks in advance, Al

 

[MrRobin]

Have you considered using the query as a data source and using the email mail merge facilities in Word instead?

Might be a bit easier.

Let me know if you want more details.

Robin

 

[alcifer]

After a bit of research today I found some code that I will be able to modify for my purposes. I'm not familiar with word mail merge but it doesn't sound like a bad idea either.
I was able to find the gist of what I wanted and that is the ability to loop through a recordset and be able to do something with each record (send report via email)
Here is some code I found to loop through records:

Sub OpenRecordSetWithEditingExample()
Dim cnnLocal As New ADODB.Connection
Dim rstCurr As New ADODB.Recordset
Dim fldCurr As ADODB.Field

Set cnnLocal = CurrentProject.Connection
RstCurr.Open _
"Select * from tblMovieTitles where_
ReleaseDate = #02/01/01#", cnnLocal,_
AdOpenKeyset, adLockPessimitic
With rstCurr
Do Until .EOF
'-- Print each of the fields
For Each fldCurr in .Fields
Debug.Print fldCurr.Value
Next
'-- Updating the release date;
!Releasedate.Value = #2/1/2001#
Debug.Print
.MoveNext
Loop
End With
rstCurr.Close
End Sub