Access Report Via E-Mail

[spike250]

Hi All,

Is there any way to get access to send a report via e-mail automatically without the need to click on anything.

I need it to send a report via e-mail every monday morning I have set up the macro to do this but cannot find a way to send it without clicking a button.

Regards


Spike

 

[AlienRay]

First off, Access is going to have to be running for this to work. As far as I know, there's no way to schedule a report to run unless at least one copy of your database is running somewhere.

Assuming that someone is going to have a copy of the database open on Monday morning, you could tie your macro to the OnTimer event of a form that you know will always be open. If no such form exists, you can create one, and have it open in Hidden mode.

The trick is making sure the report isn't sent out more than once. To do that, you should establish a log table that records whenever the report is sent out. Then, set your OnTimer event to trigger every 10 to 15 minutes or so, check the table to see if your report has been sent out for the current week. If not, it will send it out and make an entry in the log table to prevent any further duplication.

 

[spike250]

Hi,

Thanks for the response.

As I am new to Access I am unsure how to start this.

Any further help would be appreciated

Spike

 

[Mike375]

Go to the form's properties and Events. Timer is one of them and Timer Interval is the time but 1000 is 1 second.

So say you set the interval at 60,000 then every 60 seconds the code or macro on the Timer event would run.

The you put a condition that would stop the code or macro. For example when the macro or code runs you might have it insert an "X" in a text box and the macro will only run when there is no X in the text box. Or you might have insert the current date, that is Date() and it will only run if the field is Null.

From a coding or macro point of view this kind of thing is usually simple, it is just a case of writing out on paper what you need to stop it running such as continually sending the Report.

 

[spike250]

Hi thanks for the response.

I can find the on Timer Event and Timer Interval functions,
but I cannot understand how to stop the macro from running more than once.

I like the idea of a log table so I can go back to see when they where sent.

I am sorry if this is a basic function but I am new to Access and still trying to learn.

Regards

Spike

 

[Mike375]

I have not used A2007 so might be different but I think only in layout in macro design.

You should have a Condition column. In A2003 and earlier in the Macro Design page View on the tool bar gets it.

A condition might look like

[forms]![formname]![textbox name] >0

[forms]![formname]![textbox name] Is Null

You can have more than one and with Or and And

[forms]![formname]![textbox1] Is Null And [forms]![formname]![textbox2] Like "X"

In the last example the macro action would only run if [textbox1] is null and [textbox2] has an X in it.

Conditions in a macro are similar to If Then in VBA.

There is no end to what you can do and "conditions" is what makes Access come alive.

With the timer event it is just like "clicking" on a button or label every 60 seconds or whatever time interval was used. But the "conditions" determine if the macro action is performed.

You can also have a StopMacro macro action as the first line of your macro and a "condition" will determine if the StopMacro action is executed.

Something else you might want to consider is a macro or VBA that removes the macro from the timer even after it is run. A Timer Even can cause screen flicker. You can have a macro or VBA open the form in Design view and change any of the properties and then close and save. When the data base was restarted the properties would be altered to put the macro back on the timer event and set the interval.

If wish to run VBA on the timer event I don't think that can be changed. However, you have the VBA in a module and a macro RunCode action and then the macro name can be put on or removed from the timer event.

Another way (simpler, if applicable) is to have the form with the timer event closed by the macro action. In other words have a form specifically for what is required.