Filter/loop/email

[tweetyksc]

I'm trying to find a way to get this done, I've looked everywhere, tried everything, and am getting nowhere fast.

I have to generate a report separately for each separate "department". Query is complete, report is set up, and it's easy enough from a form where I can choose the department from a list box or type in....

What I'm trying to do now is automate it to run automatically with a table that has two fields-- "Dept" and "email" so that:
1. the report runs for the department
2. email is sent in snapshot form to the email address.
3. On to the next dept/email, etc.

-I know how to program SendObject to send a report automatically, if I hard code the report name and email address. It's the getting it to run for each department separately (pass the dept name) and sending to each email address I'm having trouble with.
i.e. do I filter the query or the report? I don't think you can filter a report programmatically...
-I've done loops before but not in a situation like this, i.e. I've never used a table to supply information to a query...

 

[dgm]

Try something like this,

Public Sub sndEmail()
On Error GoTo errHandler
    Dim strQry As String
    Dim rst As DAO.Recordset
    Dim dbs As Database
    
    DoCmd.Echo False
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM Employees")
    
    If Not rst.EOF And rst.BOF Then
        rst.MoveFirst
        Do Until rst.EOF
            strQry = "SELECT * FROM Employees WHERE (((Employees.empNo)= " & rst!empNo & "))"
            DoCmd.OpenReport "report1", acViewDesign
            Reports!report1.RecordSource = strQry
            DoCmd.Close acReport, "Report1", acSaveYes
            DoCmd.SendObject acSendReport, "report1", acFormatSNP, "username@yourcompany.com", , , "Test", "Report", False
            rst.MoveNext
        Loop
        DoCmd.Close acReport, "Report1", acSaveNo
    End If
exitHandler:
    DoCmd.Echo True
    Exit Sub
errHandler:
    MsgBox Err.Description
    Resume exitHandler
End Sub

I've never used recordsets before, but I think this is right. Let me know if you think otherwise.

There could be a better way to do this as well,
DoCmd.OpenReport "report1", acViewDesign
Reports!report1.RecordSource = strQry
DoCmd.Close acReport, "Report1", acSaveYes
But it works.

Dave

 

[tweetyksc]

Almost there?

I feel like I'm almost there but there's something eluding me.
I'm not sure where the query goes and the table that holds the information to pass to the query goes. I wish there were somewhere to get an overview on this.

OBJECTS:
Query: qryGCodeInvEMAIL (field "GroupCode" is the param field)
Table: tblDeptEmail (2 fields: "GroupCode" and "Email")
Report: GCodeInvEMAIL (based on the query)

I'm assuming that the RECORDSET should be the table, and not the query....

Here's what I have. NOTHING happens when I click to run
(at least the errors stopped!!!)
=================================
Private Sub cmdGCodeInvMAIL_Click()
On Error GoTo errHandler

Dim strQry As String
Dim rst As DAO.Recordset
Dim dbs As Database

Dim strGcode As String
Dim strEmail As String

DoCmd.Echo False

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblDeptEmail")

If Not rst.EOF And rst.BOF Then
rst.MoveFirst
Do Until rst.EOF
strGcode = rst!GroupCode
strEmail = rst!email
strQry = "SELECT * FROM qryGCodeInvEMAIL WHERE (((qryGCodeInvEMAIL.GroupCode)= strGcode)))"
DoCmd.SendObject acSendReport, "GCodeInvEMAIL", "SnapShot Format(*.snp)", strEmail, _
, , "Group Code Inventory Report", _
"Attached is the current Group Code Inventory Report", False
rst.MoveNext
Loop
End If
exitHandler:
DoCmd.Echo True
Exit Sub
errHandler:
MsgBox Err.Description
Resume exitHandler
End Sub
============================

 

[dgm]

You need to open the report and change the recordsource,

DoCmd.OpenReport "GCodeInvEMAIL", acViewDesign
Reports!GCodeInvEMAIL.RecordSource = strQry
DoCmd.Close acReport, "GCodeInvEMAIL", acSaveYes

after you set strQry and before your send the report.
and change this:

strQry = "SELECT * FROM qryGCodeInvEMAIL WHERE (((qryGCodeInvEMAIL.GroupCode)= strGcode)))"

to this:

strQry = "Select * FROM qryGCodeInvEMAIL WHERE (((qryGCodeInvEMAIL.Groupcode) = " & strGCode & ")))"

Then it should work.

Dave