I need to e-mail a report at the end of each month. How can I get this done automatically with code?

Hi
I'm not what process code your using to email the report but with regard to the end of the month check.
You can evaluate the current date against the end date of the current month.

Thanks for replying so quickly,

I currently e-mail reports on specific days in the week using the On Load event, but didn't know the code for the month end, with different dates etc.

Your code works perfectly, but my manager has asked if the report could be sent at the end of the day, i.e. 4.00pm to take into account sales on that last day.

Do you know how I can set this up?

I appreciate all your help very much.

Regards

Martin

Hi,
just include that in your condition e.g.:

If Date() = DateSerial(YEAR(Date), Month(Date) + 1, 0) AND Time() > #04:00:00 PM# Then
...

However, this does NOT guarantee that the report will be send. What happens if no one opens the file after 4 anymore? You might want to consider using a macro to execute your email function and automate the calling of this macro with a batch file and the macro command line switch. You can then schedule the batch file with the windows scheduler or some other scheduling software. This would take the human aspect out of the process.
HTH
Good luck

Thanks very much,

You're right, the file will be opened in the morning, and left open all day. I hoped that the On Timer event might trigger the e-mail at 4.00 pm.

I am self taught with access, and setting up macros is something I know nothing about. Would I enter the same code for the On Load event when I create the macro?

Please help.

Thanks very much,


Martin.

Hi,
the on timer event would work if you can guarantee that the application will be open 24/7.
There is nothing fancy about the macro approach.
Take your email code (everything without the evaluation part since you won't need it when utilizing the scheduler).
Paste it into a new public function in a new module e.g.:

Public Function ScheculedEmail()

'your email code

End Function

Then close and save the module as something different then the function within it.
Create a new macro...in the action argument pick RunCode...in the Function Name property write the name of your function you just created e.g. ScheculedEmail(). When automating this with the scheduler you should add another action to the macro or the function itself which shuts down the application after it is done with its process to make sure it doesn't stay open.
Now you can create a new batch file and call your created macro with the macro command line switch e.g.:

C:
CD\Program Files\Microsoft Office\Office
Msaccess.exe C:\Northwind.mdb /x YourMacroName

Then schedule the batch file. You can read a little more detail about this process here.
HTH
Good luck

Brilliant,

This is a great help, thanks very much for your advice, it is very much appreciated.

All the best


Martin

You're welcome.
Good luck on future projects!