How Can I send Access report to email address saved in database?

Set the report's filter programmatically in VBA before using the sendobject method

like this:

'1. set the reports filter then close the report
DoCmd.OpenReport "MyReportName", acViewDesign
Reports!MyReportName.Filter = "[SampleFieldName] = 3"
Reports!MyReportName.FilterOnLoad = True
DoCmd.Close acReport, "MyReportName"

'2. Set mail properties then send the report
EmailSubject = "Subject Header"
EmailBody = "Main Body Text"
EmailAddress = "me@mail.com; etc@mail.com"
ReportName = "MyReportName"
DoCmd.SendObject acSendReport, ReportName, acFormatPDF, EmailAddress,,,EmailSubject,EmailBody,False

Select allOpen in new window

Hope this helps

Hi,

Yes this sounds like a good idea, hope to test it later today when I have access to clients pc.

Thanks,
Ben

Hi,

few questions,

1-Am currently using 2003 getting error with Reports!MyReportName.FilterOnLoad = True, can I ignore that?Any alternative?
2-Since its opening report in design mode, will that work in MDE?
3-Can I have users edit email before sending?

Thanks,
Ben

Through VBA one can limit the data that goes into a report to generate what you want. You can then automate Outlook to send emails, however Outlook/MAPI has security features that limit the emails that can be sent.

We offer a commercial product, Total Access Emailer, that lets you send emails to everyone in your list with personalized data from your data source and filtered reports as attached PDF files. Multiple files can even be zipped up and password protected. Emails can be text and/or HTML. It uses SMTP to bypass the limitations of Outlook/MAPI.

A free trial is available that lets you send email blasts up to 50 at a time. Hope that helps.

Hi Experts,

Keep us posted...

Sure I will, just arriving today little late at work, sorry for delay in response..

Thanks,
Ben

@Scott, @Jeff

You basically have similar solutions, tested and they work well.

Also, note with 2003 you cannot use the PDF format. That was introduced in 2007.

Here is my issue, I have an app that's used by various users, some have already upgraded their office package while others still use Access2K, and therefore I'm still keeping my app in that earlier format.

Now my question is, how would it be possible to save in a table the format I want to print (like acFormatRTF, acFormatSNP or acFormatPDF) and then have this be retrieved by the code?

@Luke Chung,
I'm familiar with your software, had actually purchased it for a particular client a while back and they were very happy, however here its not such a big requirement and dont think its even necessary to use the Outlook object model for the qty its being send, the SendObject would probably be good enough (or perhaps your free trial version-:)

Thanks,
Ben

You can use this for the older versions
http://www.lebans.com/reporttopdf.htm

@Jeff,

I dont have an issue printing snapshot format for older version users, my concern is now how to code acFormatPDF in older version w/o receiving compilation error, see attached.

Thanks,
Ben
untitled.png

This worked out well,
Thank you!