Email many reports with wherecondition 2

[dkmansion]

Hi all,

I need to simplify an email process, here's the details.
A. Access2003 to develop for Access2000 and 97 distribution
B. Application is a deficiency tracker for over 172 different clinics for a LARGE L.A. area hospital.
C. Each clinic would receive a report for their unit only!, and we will send it through email automation (no touch)(Outlook).
D. Might only send one at a time but could need to loop through clinics

I have current working code where:
1. we select from a listbox passing a select case in the VBA to choose the right report etc.
2. send the report as an rtf document
3. pass stored recipients from the clinics table
4. fill in all related subject, and email.body info based on the clinic data and the report we are sending.

This works assuming I manage 172+ 'identical' reports and any additional reports for later implemented clinics. This I don't want to do.​

What suggestion could be made to...
1. Use one report and pass the clinicID# to the report from the Reports form when choosing the clinic, while only creating ONE access report. &
2. Saving that report without any additional user intervention {most likely opening the report in hiddenmode and saving > closing and then going into the email code and attaching there...}(to eliminate possible errors).

I don't see docmd.openreport doing it right off the bat. And docmd.sendobject acreport does not allow for passing the wherecondition, neither does the docmd.outputto code.

What combination or the sendobj, openreport, and or outputto would you follow? thanks

Donald M

 

[Remou]

Hi
Are you using Outlook? If so, it might be useful to look at this thread705-1026158 regarding sending an attachment. You could then output your report files to disk.

 

[dkmansion]

I have working code to attach to email already, thanks. My need for input regards the suggested sequence for exporting with a where condition of clinicid = stringfromformfield in a "no touch" automation, outside of pressing a command button.

Donald M

 

[Remou]

Hi
How about:
Code for form dlgReport:

Private Sub Form_Open(Cancel As Integer)
   Me!txtClinicID = OpenArgs
End Sub

Code in a module:

DoCmd.OpenForm "dlgReport", , , , , acHidden, "1"
DoCmd.OutputTo acOutputReport, "Report1", acFormatRTF, "C:\Report1" _
   & Forms!dlgReport!txtClinicID & ".RTF", Yes
DoCmd.Close acForm, "dlgReport"

Code for a Report:

Private Sub Report_Open(Cancel As Integer)
  Me.Filter = "ClinicID=" & Forms!dlgReport!txtClinicID
  Me.FilterOn = True
End Sub

 

[dkmansion]

Thanks Remou,

You must be telepathic 'cause I wrote an almost identical set of code, (with a bunch of variables and select case statements) to handle the reports and filters for said Clinics.

I have it down to 2 reports per export type (since excel and rtf documents usually require different formatting) for a total of six reports to manage for 170+ clinics.

Here's a star anyway..

Donald M