Results 1 to 7 of 7
  1. #1
    broecher is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    38

    automatic report export/emailing

    Hi,

    I would like to email a report each night. I would imagine this needs to be done with an external vb script that I would schedule. Any suggestions?

    If both exporting and emailing is too involved, I could make this work with python if I could only export the report automatically somehow.

    Thanks in advance!



    John

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The only way I could figure out how to do this is to have a task set up in Windows Task Manager that runs a vbScript. The script opens Access project. Project is set with a default form to open. Code in form Open event runs the email procedure.

    Common topic. Search forum or Google.

    http://forums.aspfree.com/microsoft-...ro-447084.html

    https://www.accessforums.net/reports...ent-19277.html

    I explored having vbScript execute a Sub in Access without having to open the project but had no success with that.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    broecher is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    38
    Thanks June7,

    I think this could work for me. The report i want to email is kind of complected with many sub-queries. I will just have to make a copy of the database, delete the tables, and replace with links to the original data. then change the open event to do it all...

    Do you know of any tricks to replacing data tables without affecting any queries, etc.? Seems like sometimes I get away with it and other times I don't...

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Not following that. Why do you need to make copy, delete tables, replace links? If you delete or rename a table, query will still look for the table and try to execute and will give an error message then open in SQL View so you can edit it. If you create another table with same name and fields, the query should resume working.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    broecher is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    38
    If I make an open event that sends the report by email, then it would be emailing throughout the day while people are opening it to add data. I would want the database to just open to a data entry form normally, but at night I want an email.

    I believe it would be possible to make a copy of the database. In the copy I would change the open event to send the email. But in order for it to reflect the data that is in the original database, I could delete the tables and then add the tables from the original as 'external data'. They would have the same name so hopefully the sql would not notice any change.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You can set conditions in code that allow email only if they are true.

    I have a table that stores the last date email was sent. My code only sends the email at particular time of day every Monday except during winter months it's only monthly and if the saved date is not current date and only if I am the one opening project. My code:
    Code:
    If Me.tbxUser = "HJF" Then
        If Not IsNull(DLookup("DateEnter", "UserComments", "Isnull(DateReview)")) Then DoCmd.OpenTable "UserComments"
        If Format(Now(), "hhmmss") <= "080000" And Weekday(Date) = 2 And ((Month(Date) >= 6 And Month(Date) <= 9) Or Month(Date) <> Month(DLookup("ConstructionExtract", "Updates"))) Then
            ConstructionExtract
        End If
    End If
    Construction Extract is the procedure that has the email code. Last line in that procedure is:
    CurrentDb.Execute "UPDATE Updates SET ConstructionExtract=#" & Date & "#"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Update information. I have learned how to run Access procedure from VBScript.

    Dim accessApp
    set accessApp = CreateObject("Access.Application")
    accessApp.OpenCurrentDataBase("\\servername\path\f ilename.accdb")
    accessApp.Run "ConstructionExtract"
    accessApp.Quit
    set accessApp = nothing
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 12-05-2011, 02:19 PM
  2. Emailing Personalized Report
    By rgrwatson85 in forum Reports
    Replies: 3
    Last Post: 10-20-2011, 10:21 AM
  3. Emailing Report with Attachments
    By Rookie in forum Reports
    Replies: 1
    Last Post: 08-19-2011, 06:52 PM
  4. Emailing a report
    By prv in forum Access
    Replies: 1
    Last Post: 12-20-2010, 04:03 PM
  5. Emailing report at the Email content
    By ylivne in forum Reports
    Replies: 0
    Last Post: 07-07-2009, 05:31 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums