Need report coding clarification please

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The following is a re-post of my question. I received and answer that only
allowed a single report to be sent. I want to send mulitple reports via a
single switchboard button. Is this possible?

I have DB where I want to e-mail mulitple reports (about 7) to a single
distribution list. I want to do this via a button on a Switchboard - I click
button and e-mail pops up with reports attached. I can do
this with a single report via a command button on a form, but how do I get
multiple reports to do this from a Switchboard button? I'm thinking that I
need to
create VBA code (I would need serious help here) and attach it to the
Switchboard button (Can this be done?).

Your assistance is invaluable. Thanks in advance.
 
Here's what I do to send reports via email:

1. Create a temporary file (I use scripting, but others might disagree),
save the file name in the variable 'fn'.

2. Open the report in Preview mode:
DoCmd.OpenReport "My Report", acViewPreview

3. Save the report as a snapshot to the temporary file:
DoCmd.OutputTo acOutputReport, "My Report", acFormatSNP, fn, False

4. Close the report:
DoCmd.Close acReport, "Active Contracts Report"

5. Repeat steps 1-3 for each report I want to send.

6. Create an instance of Outlook:
Dim Outlook_Object As Object
Set Outlook_Object = CreateObject("Outlook.Application")

7. Create an Outlook mail message:
Dim Outlook_Message As Object
Set Outlook_Message = Outlook_Object.CreateItem(olMailItem)

8. Set the message recipient list:
Dim Message_Recipient As Object
Set Message_Recipient = Outlook_Message.Recipients.Add("recipient")
Message_Recipient.Type = 1

9. Attach each report snapshot file:
Dim Attachments as Object
Set Attachments = Outlook_Message.Attachments
For each report snapshot file:
Attachments.Add (fn)

10. Display the mail message, allowing me to make adjustments and then send
the message manually:
Outlook_Message.Display (True)

11. Be sure to clean up the objects I created:
Set Attachments = Nothing
Set Message_Recipient = Nothing
Set Outlook_Message = Nothing
Set Outlook_Object = Nothing

12. Delete the temporary report files

As far as attaching the above VBA code to a switchboard button, I can't help
you there, as I haven't worked with switchboards before. Maybe someone else
can address that.

HTH,

Carl Rapson
 
Back
Top