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