1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access 2003 E-Mail Report

Discussion in 'Business Applications' started by computerman29642, Mar 26, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. computerman29642

    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)?
     
  2. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    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...
     
  3. computerman29642

    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.
     
  4. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    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..
     

    Attached Files:

  5. computerman29642

    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.
     
  6. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    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
     

    Attached Files:

  7. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Ziggy,

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

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/697353

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice