MS Access - Email Multiple Reports

Best approach is to use the Outlook Object to automate your process and to have more control on attachments and to Edit your e-mail.

See below thread:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27037237.html?cid=748#a35771953

HTH,
Daniel

It looks like these examples all require the attachments to be precreated and saved somewhere on the computer.  I don't currently have it programmed this way.  The two attachements are sort of real time reports within access.  Is there an easy way to do this?

See this example for exporting the reports

http://www.blueclaw-db.com/mail_rtf_report_access.htm

http://en.allexperts.com/q/Using-MS-Access-1440/2009/1/Access-2003-Distributing-Reports.htm

http://support.microsoft.com/kb/307184

available formats
http://office.microsoft.com/en-us/access-help/distribute-a-report-electronically-HA010206320.aspx

The easy way is have the attachments to be precreated and saved somewhere on the computer.  If you want real-time reports you would have to use SQL Server Reporting Services tied directly to the SQL Server whereby a link is sent via email and they click on it and get data real-time.  

With Access all you can do is print out the reports as the data is in that point in time and then send the report - which in essence all a report is....because people can change the data after the report is run - doesn't mean its going to refresh.

I usually export mine to a pdf to a c:\local temp location and then do as the above Outlook VBA coding that the experts have shown...

To export as PDF with Access 2007 (be sure you have all your Service Packs for Office 2007 SP2)-

https://www.experts-exchange.com/questions/23597321/Create-pdf-using-DoCmd-OutputTo-acOutputReport-acFormatPDF-but-with-ability-to-pass-filter-to-report.html

Experts:
Sorry, I was not very clear with my last post.  I have not yet played with saving reports to the computer.  I just need to make sure that there are not issues with overwriting these temporary attachement files when the code is run a second time.  Let me play around with this information and get back.

I am using a couple suggestions above (primarily boag2000's suggestion) and have it working except for a couple items.  I have a string of 2-3 emails that is in the TO field and 2 more in the CC field of the email.  These are all address and not true "recipients".  I am separating all the emails with a semi-colon.  When the program (outlook) resolves the addresses (or when the send button is clicked), it returns the "Check Names" dialog box for only the TO field.  It reads the string of emails as a single email address.  If I click in the TO field and back out of it, Outlook separates all the address correctly.  Any ideas what is wrong?

CodeCruiser,

To is not a collection; rather, it is a delimited string.

Recipients, OTOH, is a collection.

:)

Patrick

< I have a string of 2-3 emails that is in the TO field and 2 more in the CC field of the email.>

These are new requirements, not mentioned in your original post.


If the Recipients and the CC will always be the same people, I would create a distribution list for the Recipients and another for the CCs.

The you could just reference them with syntax something like so:
Set objOutlookRecip = .Recipients.Add("RecipientsDistributionListName")

You would do the same for the CCs as well...

JeffCoachman

The addresses are project specific.  I can not use a distribution name for this.  I have it configured as a string.

I've played around with it and it seam like only the CC field is resolving correctly.  The TO string is fine, because if I change the code to insert the TO string into the CC field the CC field resolves fine, and if I put the CC string into the TO field it will not resolve the TO field.  It looks like it is an outlook problem.

I think I have to add each email address seperatly as CodeCruiser mentioned.

shrimpfork,

If you are using the Outlook automation class I put into my article https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_4316-Automate-Outlook-in-VBA-with-the-OutlookCreateItem-Class.html, you can pass in a semicolon-delimited string for the To, Cc, or Bcc properties.

Patrick

Thank you Experts!  I got it up and running.  Boag2000, your response was the quickest and easiest to get me going down the right road, while Matthewspatrick, your article was a ton of information and I did not really have the time to dissect what I needed.  You all contributed to this issue and I hope I fairly distribute the points.  Thanks again guys, it is great to have this resource.

Glad we were able to help.

enjoy the weekend