Web Development

Ask something
Discussion Locked

Report Distribution

0 helpful
5 replies

by gsteve

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a report I have created in Access XP to show budgeted and actual revenue/expenses, broken down by VP, Administrator, and Department Head.

The report currently shows all departments, and I can use the sendobject macro to email (via MS Outlook/Exchange)this entire report in snapshot format to specified users.

What I would like to be able to do is email a report to Joe Schmoe, which only Joe Schmoe's departments on the report. I am guessing this will be done by making my source query a Parameter Query.

I have changed the query over to a parameter query, and I can run a macro using the SendObject function...but I have to type in each individual name.

Now I have to deal with the fact that we have over 100 VPs/Admins/Dept Heads, and typing each name every time I want to run the report just doesn't seem like a good use of time. I was hoping that I could use some type of distribution list with the Names and Exchange Aliases of each recipient?

Am I going the right direction here? Is anyone willing to help?

5 replies

by dryflies

create a table that contains the department names (that you would enter in the parameterized query) and the associated e-mail address.
Next creat a VBA function that iterates through that table inserting the department name into the query where the parameter is. for each iteration, save the query to a generic name, run the report off that query and then use the associated e-mail address to invoke the sendmail object.
This is obviously a high level description of the process but it gives you a good starting point.

by gsteve

>Next creat a VBA function that iterates through that table inserting the department name into the query where the parameter is.
...I know this is what I need to do, I just need to know how to do it! Thanks for the reply, though!

by dryflies

Lets try this one more time.
in the query that generates the data for your report, modify the query that the report uses so it reports only for joe schmo.
Get the SQL string for that query.
set up a table with the where clause string for each of your reports and the associated e-mail. for example "joe schmo", "jschmo@foo.com"
In VBA use ADO to iterate through your table.
let me know if you need help doing that. but you can look up ADO in access help and it will steer you in the right direction.
for each iteration, change the SQL and set the RecordSource property of the report to the sql string you build from this iteration. (See report object and RecordSource property in Access Help) Then, execute the report using the openReport comman
stDocName = "JUNIOR Rosters"
DoCmd.OpenReport stDocName, acPreview
or execute your macro that executes the sendObject method on the report.

This is all your gonna get for points.

by gsteve

Thanks for the help - I appreciate your patience! I will look this up, and post new questions as they come up (I'm pretty sure I will have more questions!!).

This discussion is closed to new replies.