Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    Texas
    Posts
    37

    Unanswered: Sending a report to a file in vba ... and more

    I want to auto distribute a database activity report for each of the 3 shifts. I can do this currently, manually, using a form/report and then using the sendto email ... but I would like to do this automatically by using the windows scheduler and launching a form (for each shift) which would pipe the report to a file and then launch an email (to a list of recipients) attaching the report to the email (w/o the email verification popup window).

    Any suggestions and/or sample code would be truly appreciated.
    A2K - W2k
    Thanks & Regards
    Pete

  2. #2
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147
    Is this report sent as an Excel file ?

    What format is it ?

  3. #3
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147
    Ok,

    I had a play around with Access and forms..

    What you need to do,

    1. Create a form with a timer (events section timer interval = 15000). Say 15 seconds, BUT have a command button to stop the On_Timer event - incase you want to edit the database at some point.

    Me.TimerInterval = 0
    docmd.close


    Something like that anyways..

    2. Under the On_timer event use this code:


    Dim fs
    Dim Rpt As String, Rpt_Location As String
    Dim Msg As String

    Rpt = "Your_Report"
    Rpt_Location = "C:\report.xls"

    DoCmd.OutputTo acOutputReport, Rpt, acFormatXLS, Rpt_Location

    Msg = "Hi " & Chr(13) & Chr(13)
    Msg = Msg & "Please find attached your report" & Chr(13) & Chr(13)
    Msg = Msg & "Coolest regards" & Chr(13) & Chr(13)
    Msg = Msg & "Ken" & Chr(13) & Chr(13)



    Set myOlApp = CreateObject("Outlook.Application")
    Set myitem = myOlApp.CreateItem(olMailItem)
    Set myRecipient = myitem.Recipients.Add("You@where-ever.com")

    Set myAttachments = myitem.Attachments

    myAttachments.Add Rpt_Location
    myitem.Subject = "Shift Report"
    myitem.Body = Msg
    myitem.Send

    'Remove ' from below to delete spreadsheet afterwards...

    'Set fs = CreateObject("Scripting.FileSystemObject")

    'fs.deletefile Rpt_Location

    Docmd.quit



    3. Under tools>startup, select this form to open when database opens.


    Right, the code itself is fairly straight forward, it outputs your report to a specific location, then creates an email. Then if you want you can delete the file.

    I have disabled by use of ' the deletion of the file, as I like keeping copies of what was sent. You may want to create a reports folder and have the location changed to match this, then use a now() in the file name to show time and date. (Maybe ? - its up to you)

    Now all you need to do is set your scheduler to open the database.

    If you have any problem, let me know and I will write a form and stuff, then zip it here for you to canabalise !

    Hope this helps you.

    Ken.
    Last edited by Ken_Hart; 04-07-04 at 08:30.

  4. #4
    Join Date
    Jan 2004
    Location
    Texas
    Posts
    37
    Problem: I've got a where condition that qualifies which records to report. Don't see how to perform same in docmd.outputTo



    'DoCmd.OpenReport "report", acViewPreview, , "([start_date] >= #" & sdate & "# Or [sudate] >= #" & sdate & "# Or [fixdate] >= #" & sdate & "#) And ([start_date] <= #" & edate & "# Or [sudate] <= #" & edate & "# Or [fixdate] <= #" & edate & "#)"
    A2K - W2k
    Thanks & Regards
    Pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •