advanced programming

[Guest]

Can Access be programmed to self generate reports at the beginning of every
month and automatically email reports to a predefined group?

 

[Guest]

Sure, Put code in your start up procedure. If you start with a form, call
it from the Load event. If you use an Autoexec macro, write it as a function
and put a runcode line in it to call the function.

You will probably need a table so you know when the reports were last run.
When you start the routine, check the current month and compare it to the
table to see if the reports have been run for the month. If they have not,
run them, then update the table so you know they have been done for the month.

I will admit to being a semi-dummy using automation with Outlook, but the
theory would be to set up a group in contacts, add the people and their
E-mail addresses to the group, then use that to send the E-mails.

 

[pietlinden]

Can Access be programmed to self generate reports at the beginning of every
month and automatically email reports to a predefined group?

You could do it in the open event of your startup form or something
like that.
This syntax is going to be way wrong, but hopefully you get the idea...

Something along the lines of

If Datepart("d",Date())<3 then
'open recordset of recipients
rsRecip=dbengine(0)(0).OpenRecordset("tblRecipients")
do until rsRecip.EOF
DoCmd.sendobject acReport, "MyReport", rsRecip("EMail")
rsRecip.Movenext
loop
end if

 

[pietlinden]

I will admit to being a semi-dummy using automation with Outlook, but the
theory would be to set up a group in contacts, add the people and their
E-mail addresses to the group, then use that to send the E-mails.

There's code all over the place for automating outlook (Access Power
Programming, by Scott Barker; Lyle posted some stuff here to do CDO,
which is really easy)... pick the one you want. And if that fails,
check the outlook NG.

 

[Guest]

Thats a great little routine, but this line:

If Datepart("d",Date())<3 then

is problematic.
Examples.
The first of the month is on Tuesday. You will get reports run on Tuesday
and Wednesday.
The first of the month is Saturday. You wont get any reports for the month.

The OP still needs a way to check to see if the report has been run and
record that is has for the month.

 

[pietlinden]

Klaatu,
true, but I was leaving it to the OP to sort out. Maybe it's over his
head. He could modify the IF statement a little bit to suit his needs.
I was just giving him a direction to go in, not the answer. No skin
off my nose if he changes it. So add some stuff to get the day of the
first of the month and modify accordingly...

but then I wouldn't consider this "advanced programming", so maybe it
is over his head. IN that case, search the NG for "First of month". I
know there are several functions to do that. I've written them myself
before.

Okay how about...
Get the first Weekday of the month...
add 1 to it until you hit Monday or non-holiday...