Need report coding clarification please

[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.

 

[Carl Rapson]

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