Auto Email from a report or query

Hello

I need to find a way to have Access 2007 run a report or query at 00:01am of
everyday and then to generate an email, containing all the information
present in the report or query, to send via outlook 2007 to a predetermined
email account.

Plus where would this code be wrote, SQL, Forms, Basic etc...?

Your help with this question would greatly appreciated.

Brendan,

In most of my applications, I have a Splash screen (some people call it a
switchboard) that is the first form that users see. When they select one of
the options on that form, I generally hide the form, and then make it visible
again when the user exits whatever module they have selected.

You could put some code in that forms Timer event, to determine whether the
current date is the previous date, and if so, use the SendObject method to
run the report. It might look something like:

Private Sub Form_Timer

Static dtLastPass as Date

If dtLastPass = 0 Then
dtLastPass = Date
ElseIf dtLastPass Date Then
DoCmd.SendObject acSendReport, "ReportName", , _
", , , , _
"Daily report", strMsg, False
End If

End Sub

Then, set the Timer interval based on how close you want this to run to
Midnight. I would probably set it to something like 60000.

SendObject allows you to send a report, query, ... to one or more
recipients, allows you to put the subject in the email, and even put text in
the message body.

Depending on what version of Access you are running, and what Service Pack,
this might not work in an unattended mode (security patches prevent unwanted
email from being sent from your computer). I know I've read some other
messages about how to bypass that security, but have never implemented any of
them.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"Brendanpeek" wrote:

Hello

I need to find a way to have Access 2007 run a report or query at 00:01am of
everyday and then to generate an email, containing all the information
present in the report or query, to send via outlook 2007 to a predetermined
email account.

Plus where would this code be wrote, SQL, Forms, Basic etc...?

Your help with this question would greatly appreciated.

hi
first, if the access file is open and running then you can use a timer on an
open form to check the time and do a "DoCmd.SendObject" to the report you
want.
if the access file isnt running, use windows' scheduler to run it at the
right time.
the (big) problem is that from office 2002 onward, outlook will not allow
code-generated emails to be sent automatically without a manual confirmation
(something like "a program is trying to send email...") and as far as i know
there is no way to go around it. this was added to prevent robots sending
information on your behalf without you knowing it, but it cetainly made our
(programmers) life quite a bit harder.
the solution i use is a small .net dll i wrote, refered to from access, that
uses system.net.mail to send emails without outlook.
it's quite a bit of a detour, but once accomplished, it's doing the job
nicely, and i dont know of any other solution
if you need help with that, i'll try to help you
good luck
Erez.
"Brendanpeek" wrote:

Hello

I need to find a way to have Access 2007 run a report or query at 00:01am of
everyday and then to generate an email, containing all the information
present in the report or query, to send via outlook 2007 to a predetermined
email account.

Plus where would this code be wrote, SQL, Forms, Basic etc...?

Your help with this question would greatly appreciated.

Ok looks like i will need your help Erez Mor because the database i have made
will be all ways open on a sever with office 2007 loaded on it.

"Erez Mor" wrote:

hi
first, if the access file is open and running then you can use a timer on an
open form to check the time and do a "DoCmd.SendObject" to the report you
want.
if the access file isnt running, use windows' scheduler to run it at the
right time.
the (big) problem is that from office 2002 onward, outlook will not allow
code-generated emails to be sent automatically without a manual confirmation
(something like "a program is trying to send email...") and as far as i know
there is no way to go around it. this was added to prevent robots sending
information on your behalf without you knowing it, but it cetainly made our
(programmers) life quite a bit harder.
the solution i use is a small .net dll i wrote, refered to from access, that
uses system.net.mail to send emails without outlook.
it's quite a bit of a detour, but once accomplished, it's doing the job
nicely, and i dont know of any other solution
if you need help with that, i'll try to help you
good luck
Erez.
"Brendanpeek" wrote:

Hello

I need to find a way to have Access 2007 run a report or query at 00:01am of
everyday and then to generate an email, containing all the information
present in the report or query, to send via outlook 2007 to a predetermined
email account.

Plus where would this code be wrote, SQL, Forms, Basic etc...?

Your help with this question would greatly appreciated.

Erez Mor wrote:

the (big) problem is that from office 2002 onward, outlook will not allow
code-generated emails to be sent automatically without a manual confirmation
(something like "a program is trying to send email...") and as far as i know
there is no way to go around it. this was added to prevent robots sending
information on your behalf without you knowing it, but it cetainly made our
(programmers) life quite a bit harder.

There are solutions. See the Outlook specific links at the Access
Email FAQ at my website.
http://www.granite.ab.ca/access/email/outlook.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/