At the end of every month a report will be going to each manager detailing their departments orders for that month. I have a summary report that lists all departments on one report but I need to make about 20 different reports (1 for each department) I did it the long and tedious way by creating 20 different queries using each department# as criteria, then created 20 reports based on those queries.
My next step was to create a macro using 20 SendObject actions to email each manager their report.
Here's the problem: if a report has no data, of course I don't want it sent out. The way I have it set now, is that if the report has no data, the macro is set to cancel.
I'm sure there has to be an easier way to do this. I just can't figure it out.
Originally posted by bertthefreak
This won't help you at the minute but this is sometyhin that I am experimenting with also. If i manage to do it I'll let you know.
I didn't try it myself, but I was just thinking ...
Suppose you have a form on which a button is placed. Clicking this button will start generating reports and send them
Suppose you a table containing all the managers names and their email-addresses.
To generate a report; the query is always the same except the department#.
Public Sub myButton_Click()
Public sub GenerateReport ()
Dim strSQL as String
Dim rsManagers as New ADODB.RecordSet
Dim departmentID as Byte
strSQL = 'SELECT allManagers FROM yourTable'
rsManagers.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
Do While Not rsManagers.EOF
departmentID = rsManagers.Fields(???)
strSQL = 'SELECT whateverYouNeed FROM yourTable WHERE department# = departmentID'
myReport.RecordSource = strSQL
DoCmd.OpenReport myReport.Name, acViewPreview
Call SendReport (myReport, rsManager.Fields(0))
DoCmd.Close acReport, myReport.Name, acSaveNo
Public Sub SendReport (myReport, recipient)
Write a procedure to send the report to the recipient
The syntax may not be correct or complete, but it might give you an idea.
I hope this is of any help.