Looping a table as a Where clause and send emails.

[bamrak]

I seem to have gotten over my head, but this seems fairly
straightforward ..

I have a table of items assigned to a rep, and I have a rep list table
created from the original table ( to show only reps in the main
table).

I want to be able to send and email to each rep with only their
items.
I know I will have to have a loop statement that will take the reps
name and use that in a variable to be able to send it to each rep by
name. How do I sync that with the query?
I've got something like this:
Dim i As Integer
Dim strRep As String

For i = 1 To TblTempRep.Coun t
strRep = TblTempRep.Rep
DoCmd.SendObjec t acSendQuery, qryEmailRep, acFormatXLS, strRep
Next i

But I'm not sure if this is correct, or if this would be the proper
way.

I would think that using SQL would be easier than making a query and
passing the variable back to it through a function ( at least I think
that's how you would do it), but I don't have the knowledge to get the
SQL sent as a excel spreadsheet either.

I hope that makes sense, and thanks in advance for your time.

[Phil Stanton]

Have just done something similar to send membership renewal forms to Club
members. Very long winded, but I will give you some pointers.
Firstly. because the Invoice is quite complicated I can't send it as an RTF
file as the formatting would be lost. so I send it as a PDF file attachment.

You need the loop of your reps and I use an SQL for this
I use a PDF995 printer driver which is configured to save the file as a
fixed name e.g. RepRpt.PDF, so I delete RepRpt.PDF, I have a routine to
create the appropriate report for the Member (Rep) in question which I now
save as the new RepRpt.PDF. Then I have another routine using the Outlook
Application from the Microsoft Outlook object library to send the email and
attach the PDF file

Lots of coding. watch out for writer's cramp

Phil
<ba****@gmail.c omwrote in message
news:11******** **************@ 22g2000hsm.goog legroups.com...

>I seem to have gotten over my head, but this seems fairly
straightforward ..

I have a table of items assigned to a rep, and I have a rep list table
created from the original table ( to show only reps in the main
table).

I want to be able to send and email to each rep with only their
items.
I know I will have to have a loop statement that will take the reps
name and use that in a variable to be able to send it to each rep by
name. How do I sync that with the query?
I've got something like this:
Dim i As Integer
Dim strRep As String

For i = 1 To TblTempRep.Coun t
strRep = TblTempRep.Rep
DoCmd.SendObjec t acSendQuery, qryEmailRep, acFormatXLS, strRep
Next i

But I'm not sure if this is correct, or if this would be the proper
way.

I would think that using SQL would be easier than making a query and
passing the variable back to it through a function ( at least I think
that's how you would do it), but I don't have the knowledge to get the
SQL sent as a excel spreadsheet either.

I hope that makes sense, and thanks in advance for your time.