Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi all,
    I am trying to send a report automatically to one person from Access using Microsoft Outlook. The report needs to go to this person every two weeks (or 14 days which ever is easier). The report is a list of patients that need either a 30 day, 90 day or 180 day follow-up from their procedure date. So on this report will be a mixture of some patients needing 30 day, 90 day etc.

    I attached the report and query that it is based on.

    The test patients in the database have procedure dates between March 4 and April 29 2009. The report criteria is based on 3 date ranges for the patients that need a follow-up 30, 90 and 180 days after their procedure. For testing purposes, if I run the report for 6/1/09 8/30/09 it returns patients that need follow-up in that date range (even though we will be running it for two-week intervals.
    Ive been looking through the threads and other internet sites to find what I need but I only find variances. Im not good enough at the code to manipulate what Ive found into something workable. This is something that I will need in many of my databases (except usually only one date range criteria) so I am anxious to learn it.
    Thank you,
    Vicky
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Which date range do you want to use when you e-mail the report?

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is your CollateReports function used anywhere in the mdb? Which actually makes me think...are there any utilities around that allow one to find where a certain function is used anywhere in an Access file, be it VBA code, queries, macros, control/record sources on forms or reports and so on?

  4. #4
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='790636' date='25-Aug-2009 19:49']Which date range do you want to use when you e-mail the report?[/quote]

    Hi Hans,
    I need it to capture all three date ranges - like it does in the current query now.

    I had it all set up to click a button to send the report - but they want it to come to their email.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    So the following should happen:

    Somebody will open the form, enter a start date and end date, then click a command button that e-mails the report (using the start and end date from the form) to a predefined e-mail address.

    Is that correct?

  6. #6
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='grovelli' post='790637' date='25-Aug-2009 20:05']Is your CollateReports function used anywhere in the mdb? Which actually makes me think...are there any utilities around that allow one to find where a certain function is used anywhere in an Access file, be it VBA code, queries, macros, control/record sources on forms or reports and so on?[/quote]

    No, it isn't used that I know of.
    I am not familliar with the CollateReports function - but I only have one report to be sent.
    Vicky

  7. #7
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='790726' date='26-Aug-2009 11:39']So the following should happen:

    Somebody will open the form, enter a start date and end date, then click a command button that e-mails the report (using the start and end date from the form) to a predefined e-mail address.

    Is that correct?[/quote]
    Nope, what they can do now is enter the date range and print the report. The person entering the date is the one that the email would go to - so there is no need to send himself an email.

    What they want is 'not to do anything'. Not even go into Access - just have the report appear in their inbox.
    Is that possible?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The report currently takes the start and end dates from the form.
    What should be used as start and end dates if the report is to be generated automatically, without somebody opening the form and entering those dates? (That's what I tried to ask in my first reply)

  9. #9
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='790730' date='26-Aug-2009 12:50']The report currently takes the start and end dates from the form.
    What should be used as start and end dates if the report is to be generated automatically, without somebody opening the form and entering those dates? (That's what I tried to ask in my first reply)[/quote]

    Sorry,
    Could it generate every two weeks from a system date (or today) plus 14 days to capture the two week period?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    That should be doable. I'll be away from my PC for a bit, but I'll get back to you in 2 or 3 hours.

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='grovelli' post='790637' date='25-Aug-2009 14:05']Is your CollateReports function used anywhere in the mdb? Which actually makes me think...are there any utilities around that allow one to find where a certain function is used anywhere in an Access file, be it VBA code, queries, macros, control/record sources on forms or reports and so on?[/quote]
    The only tool that I am familiar with that does that sort of thing exhaustively is Total Access Analyzer - there are other utilities that you can use to rename objects that might do the trick - Rick Fisher's "Find and Replace" comes to mind.
    Wendell

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='WendellB' post='790745' date='26-Aug-2009 13:52']The only tool that I am familiar with that does that sort of thing exhaustively is Total Access Analyzer - there are other utilities that you can use to rename objects that might do the trick - Rick Fisher's "Find and Replace" comes to mind.[/quote]
    Thanks Wendell, I had just posted a new thread about the same topic; how do I delete it?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I have attached a modified version of the database. It contains a form frmSendReport that should be set as the startup form for the database, and that should remain open all the time (it could be hidden). Also, the database needs to remain open all the time.
    The TimerInterval of the form has been set to 3600000 (milliseconds, corresponding to 1 hour), so every hour the On Timer event will fire. The event procedure checks whether 14 days have passed since the last time the report was sent, and if so, it sends the report and updates the last sent date/time in the single-record table tblReportSent. This table also contains the e-mail address of the recipient - please change the e-mail address before testing. (I'd test with your own e-mail address first, then change it to the real one).

    You can edit the date/time in tblReportSent to re-initialize it.

    Warning: if you use Outlook, you'll be asked for permission to send an automated e-mail each time. See Post 488173 for three different ways around this; ClickYes is the easiest workaround.

    This is the code:

    Code:
    Private Sub Form_Timer()
      Dim dtmDate As Date
      Dim strEmail As String
      Dim strSQL As String
      dtmDate = DLookup("dtmLastSent", "tblReportSent")
      strEmail = DLookup("strEmail", "tblReportSent")
      If Now >= dtmDate + 14 Then
    	DoCmd.SendObject ObjectType:=acSendReport, ObjectName:="rptEpPtFU", _
    	OutputFormat:=acFormatHTML, To:=strEmail, Subject:="Report for " & Date, _
    	MessageText:="Here is the two-weekly report.", EditMessage:=False
    	strSQL = "UPDATE tblReportSent SET dtmLastSent = dtmLastSent + 14"
    	CurrentDb.Execute strSQL
      End If
    End Sub
    Attached Files Attached Files

  14. #14
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='790774' date='26-Aug-2009 15:49']I have attached a modified version of the database. It contains a form frmSendReport that should be set as the startup form for the database, and that should remain open all the time (it could be hidden). Also, the database needs to remain open all the time.
    The TimerInterval of the form has been set to 3600000 (milliseconds, corresponding to 1 hour), so every hour the On Timer event will fire. The event procedure checks whether 14 days have passed since the last time the report was sent, and if so, it sends the report and updates the last sent date/time in the single-record table tblReportSent. This table also contains the e-mail address of the recipient - please change the e-mail address before testing. (I'd test with your own e-mail address first, then change it to the real one).

    You can edit the date/time in tblReportSent to re-initialize it.

    Warning: if you use Outlook, you'll be asked for permission to send an automated e-mail each time. See Post 488173 for three different ways around this; ClickYes is the easiest workaround.

    This is the code:

    Code:
    Private Sub Form_Timer()
      Dim dtmDate As Date
      Dim strEmail As String
      Dim strSQL As String
      dtmDate = DLookup("dtmLastSent", "tblReportSent")
      strEmail = DLookup("strEmail", "tblReportSent")
      If Now >= dtmDate + 14 Then
    	DoCmd.SendObject ObjectType:=acSendReport, ObjectName:="rptEpPtFU", _
    	OutputFormat:=acFormatHTML, To:=strEmail, Subject:="Report for " & Date, _
    	MessageText:="Here is the two-weekly report.", EditMessage:=False
    	strSQL = "UPDATE tblReportSent SET dtmLastSent = dtmLastSent + 14"
    	CurrentDb.Execute strSQL
      End If
    End Sub
    [/quote]


    Thank you so much for yout time with this. I will work on it today.

  15. #15
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans,
    At my hospital, we are still debating about leaving the application open to generate the email/report.
    Is there a way, rather than have the application loop over the time
    interval can it be "launched" by a task scheduler? Every 7 days the
    task would start up the Access application and then the code would do
    the calculations and generate the report?
    Thanks for all your help,
    Vicky

Page 1 of 2 12 LastLast

Posting Permissions

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