E-mail Selective Report

[adams.bria]

So I have done things like this, but never this exact scenario and cannot figure out how to design it.

I have a report that is displaying a department, and a list of students who meet X criteria in that department. What I want to do is this:

E-mail the report to the department where students are meeting X criteria. But not the entire report, just the list of students that actually belong to that department.

Right now I have my query built to select the data I need based on the criteria, and I have the report based on the query. But for some reason I cannot wrap my head around where to go from here. Any help?

Just an FYI here is what my selection query looks like

SELECT DISTINCT Current_Work_Study_Earn.Balance, Stu_Info.Stu_Last, Stu_Info.Stu_First, Stu_Info.Stu_Middle, Stu_Term.Aid_Year, Stu_Assn.Reassign, Dept.Dept_Desc, Dept.Sub_Dept, Stu_Assn.Stuassn_ID, Contacts.Cont_First, Contacts.Cont_Last, Contacts.Cont_Email, Stu_Info.Camp_ID, Stu_Info.Hr_ID, Stu_Info.Stu_Email
FROM (((((Stu_Term INNER JOIN (Current_Work_Study_Earn INNER JOIN Stu_Info ON Current_Work_Study_Earn.[Person ID] = Stu_Info.Hr_ID) ON Stu_Term.Empl_ID = Stu_Info.Empl_ID) INNER JOIN Stu_Assn ON Stu_Term.Stuterm_ID = Stu_Assn.Stuterm_ID) INNER JOIN Local_Vars ON Stu_Term.Aid_Year = Local_Vars.Aid_Year) INNER JOIN Dept_Cont ON (Stu_Assn.DC_ID = Dept_Cont.DC_ID) AND (Local_Vars.Aid_Year = Dept_Cont.Aid_Year)) INNER JOIN Dept ON Dept_Cont.Dept_ID = Dept.Dept_ID) INNER JOIN Contacts ON Dept_Cont.Cont_ID = Contacts.Cont_ID
WHERE (((Current_Work_Study_Earn.Balance)<500) AND ((Stu_Term.Aid_Year)=[Local_Vars].[Aid_Year]) AND ((Stu_Assn.Reassign)=False));

 

[vbaInet]

I thought I replied to this but obviously I didn't

So what you need to do is simply use the DoCmd.SendObject to send the report:

docmd.SendObject acSendReport, "Report Name", acformatpdf, "me@me.com", , , "Students that meet X Criteria", "Hello there"

But you also need to filter it based on the Department and Criteria right? Have a look at this thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=206372&highlight=outputto

Post #10: replace the DoCmd.OutputTo code line with the DoCmd.SendObject statement above.

 

[adams.bria]

Thanks. I think I was having one of those days that for some reason, NOTHING makes sense. Here is my final working code, in case someone runs across this:

Private Sub Earnings_Warning_Click()

On Error GoTo Err_WarningEmail

strempwarnemail = "badams2@uwlax.edu"

Dim vbRst As New ADODB.Recordset

vbRst.Open "qryEarning_Warning_Email", CurrentProject.Connection, adOpenDynamic

Do While Not vbRst.EOF

    strempwarnemail = vbRst.Fields(0).Value
    
   ' MsgBox "step 1"
      
     DoCmd.OpenReport "rptEarning_Warning", acViewPreview, , , acWindowNormal
     
     Reports![rptEarning_Warning].FilterOn = True
     
     'changed this one
     Reports![rptEarning_Warning].Filter = "cont_id=" & vbRst!Cont_ID
      
        'MsgBox "step 2"
      
    
    FilterOn = True
    
 '   MsgBox "step 3"
    
    DoCmd.SendObject acSendReport, "rptEarning_Warning", , vbRst.Fields(0).Value, , , "Federal Work Study Earning Warning", "Our records indicate that the students on the attached list are nearing their total Federal Work Study allocation for the academic year. If the student goes over their Federal Work Study allocation, and continues to work, the HR System will then start to pay out of student help. Please contact HR with questions relating to paying funds out of student help instead of Federal Work Study."
    
   ' MsgBox "step 4"
    
    vbRst.MoveNext
    
    FilterOn = False
    
    Loop
    
    DoCmd.Close acReport, "rptearning_warning"
         
Exit_cmdWarningemail:
    MsgBox "Completed Successfully!"
    Exit Sub

exit_error:
MsgBox "Failed to complete"
    Exit Sub

Err_WarningEmail:
    MsgBox Err.Description
    Resume exit_error

End Sub

 

[vbaInet]

Good job adams.bria!