How can I send different Access reports to Lotus Notes mails

[jb500010]

Hi,
I am pretty new in Access VBA programming and have many problems with that.
I have database with two linked tables 'Emails', & 'Details' linked by EmployeeID field. I have also report RptDetails.
What I want to do is to send by Lotus notes email to each employee from 'Emails' table report filtered for only that one EmployeeID. I have code showing how I can attach a file but I do not have any idea how I can attach report with filter.
Any ideas?
Jacek

 

[Remou]

This may help.

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strReport

Set db = CurrentDb
Set rs = db.OpenRecordset("Select * From Members")

strReport = "rptMembers"
DoCmd.OpenReport strReport, acViewDesign

Do While Not rs.EOF
    'Set a filter to the ID of the recordset
    'Note that this ID (Code) is text, for a 
    'numeric ID, leave out the quotes (Chr(34))

    Reports(strReport).Filter = "[Code]=" & Chr(34) & rs!Code & Chr(34)
    Reports(strReport).FilterOn = True

    'Save
    DoCmd.Save acReport, strReport

    'Sent to default email programme
    DoCmd.SendObject acSendReport, strReport, acFormatRTF, "a@b.c"
    rs.MoveNext
Loop

DoCmd.Close acReport, strReport

 

[JerryKlmns]

Open a recordset (rst) with EmployeeID included, loop, and on each step open the report in hidden WindowMode, using the WhereCondition on that EmployeeID,

DoCmd.OpenReport "YourReportName", acViewPreview, "EmployeeID=" & rst.Fields("EmployeeID"), acHidden

save it as snapshot

.OutputTo acOutputReport,,acFormatSNP, "c:\blah.snp"

and attach that snapshot file.

 

[JerryKlmns]

Looks like I was taking my time for that answer to post.

 

[jb500010]

Thanks to both of you.
It works
Now the next step.
I would like to put to the same code the next condition/filter to limit the results to certain date.
I have field [Date] on my report which is in the same table as other information, and I have calendar on my form from which I want to run the code. So I would like to open the form, choose date in that calendar and press command to run the code.
But how can I add one more filter to my report?

Jacek

 

[Remou]

Just bulid the Where statement as you would build any Where statement, that is, using And and Or where necessary.