email filter on report

[jasn_78]

hey what i am trying to do is i have a report called rptTILLTOTALS that contains sales information for various stores. (Grouped on STORE)

What i am trying to do is to email this report to the relevant people for that store. So i want user1 who is in store1 to get only there results and so on.

I have figured out how to email the same report to people but im not sure how to go about doing my final aim.

any ideas would be great.

Jason

 

[pbaldy]

Here's a start:

http://granite.ab.ca/access/email/reporttomultiplerecipients.htm

 

[jasn_78]

paul thanks

i am trying the following and just wondering how i would get the strCriteria to work on emailing the report, it works fine if i run using the report open but i really need to email it. any suggestions?

Dim rst As DAO.Recordset
Dim strEmailAddresses As String
Dim stDocName As String
Dim i As Integer
Dim strCriteria As String

Set rst = CurrentDb.OpenRecordset("tblMAILINGLIST")

Do Until rst.EOF

   strEmailAddresses = strEmailAddresses & rst("EMAIL") & ";"
   strCriteria = "[STORE]='" & rst("STORE") & "'"
   DoCmd.SendObject acSendReport, "rptTILLTOTALS", acFormatPDF, strEmailAddresses, _
                    , , "TILL TOTALS", "CUSTOM REPORTS BY TFRS", False
   rst.MoveNext

Loop

 

[pbaldy]

Well, as noted in the link, SendObject can't accept a criteria. Generally you have to have the filtering done in the report. I have seen people open the report with the criteria, then use SendObject, then close the report. Apparently if it's already open it will send with the filter applied. You can give that a try.

 

[jasn_78]

paul that worked brilianlty by opening the report with the criteria then emailing it

thanks mate.

 

[jasn_78]

sorry paul one problem with my current code i am getting multiple emails to each email address the number of emails relates to the record number in the table so in a 4 record table first record gets 4 emails 2nd record gets 3 etc.

any ideas?

 

[jasn_78]

paul never mind figured it out silly add on the strEMAILADDRESS

thanks again mate

 

[pbaldy]

No problem; glad you found a solution.