Solved: Access 2003 E-Mail Report

[computerman29642]

Does anyone know if there is a way to have Access e-mail a report at the end of the month on the last working day of the month (Monday - Saturday)?

 

[Ziggy1]

hmm..that's a good one... I don't have a quick answer, but I'll just give you the pieces of the puzzle I think you will need...when I get time I'll give it a stab....

Macro name Autoexec ... it will launch when the database is opened plus code to close
Database placed in Task Sched
program to bypass email security ( like "Clickyes")

build a table for every Month and days ....with a query use: year(Now()) as a reference to the current calender ... you can use it to get the Max per month..use weekday expression to identify the weekdays.

there will be other ways, just thought I'd throw it out there...

 

[computerman29642]

Ziggy,

Thank you for the reply. I am very new to Access, but I will try to take the information given, and see if I can figure anything out.

 

[Ziggy1]

I made up some query to explain how I figured out what the last day is, you would need another table for holidays if that is a concern.

when I steal some more time I'll post more, but if anyone else wants to jump in go ahead..

 

[computerman29642]

Ziggy,

I will take a look at the db you attached, and see if I can figure out what has been done.

Thank you for all the help.

 

[Ziggy1]

Ok, check this out...this Database is set to Execute the send code as soon as it opens, so Hold the SHIFT key down while it opens to interupt the sequence...otherwise open as normal and it will generate an email and close automatically.

*** but only if it is equal to the last day of the month based on criteria


In this Query :q ryLastofMonth

I have the query set to add 4 days to the current date...which today is the March 27. You need to remove the +4 for it to use the last weekday...but for testing modify based on the current date.....

Sendcheck: IIf([curCal]=(Date()+4),"SEND","")

I used some VBA to trigger the Send code if the field in query qrySendDate = SEND



Public Function SendMail()
On Error GoTo Sendmail_Err

Dim Sendchk As String

sendck = DMin("sendcheck", "qrySendDate")

If sendck = "SEND" Then

DoCmd.SendObject acTable, "tblMonth", "MicrosoftExcelBiff8(*.xls)", "", "", "", "", "", False, ""

End If


mcrSend_Exit:
Exit Function

Sendmail_Err:
MsgBox Error$
Resume Sendmail_Err

End Function


so your next steps will be to schedule the task scheduler to run the DB...go to Control panel for Scheduled Tasks... Follow the wizard to browse to find this Database ...you can set the schedule of when it should run...there is no setting for what you want so it will have to run everyday?


you will also need to download this to automatically click YES to send when the security warning comes up....

http://www.snapfiles.com/get/clickyes.html

I've used this in the past.


Either way there could be better ways of doing this, but I think this will work for you

* my example is sending an Excel sheet, but it can easily be changed

 

[computerman29642]

Ziggy,

I will take a look at the db. Thank you for all your efforts.