Solved: Access 2003 E-Mail Report

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

computerman29642

Thread Starter
Joined
Dec 4, 2007
Messages
2,895
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)?
 
Joined
Jun 17, 2002
Messages
2,556
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

Thread Starter
Joined
Dec 4, 2007
Messages
2,895
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.
 
Joined
Jun 17, 2002
Messages
2,556
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..
 

Attachments

computerman29642

Thread Starter
Joined
Dec 4, 2007
Messages
2,895
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.
 
Joined
Jun 17, 2002
Messages
2,556
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
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top