Automated Report Export

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Automated Report Export

Post by Leesha »

Hi!
I have a request from a user to have a report exported every day in excel format. The second step is to have it automatically emailed to a set of email addresses. Is this possible and if so how would I approach it?
Thanks,
Leesha

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Automated Report Export

Post by HansV »

Do you already have the code to export the report?
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Automated Report Export

Post by Leesha »

Hi Hans,
I haven't even started to set it up yet as I wasn't sure if it was possible. This is a sample of the code I use to export an excel file now (which of course I got from you :-) ).

DoCmd.OutputTo acQuery, "qryReferralsAnnul5YearCrosstab", "MicrosoftExcelBiff8(*.xls)", "", True, "", 0

The file prompts to be saved, however I would only need it saved to a specified location. The next step would be to have it auto emailed to the email(s) indicated. I thought about having a table that stores that email address and recipient name so that if there are multiple people it would sent to everyone in the table and/or the user can update the recipient list as needed.

The other thing I considered is that multiple people use the database at any given time. I don't want the report to go out from each user's database so thought that it could be set up to go out only if the supervisor is logged in and have the code look to their username/password.

That is as far as I've gotten in my planning as I'm not sure how to set the report to run at a specified time and then email it out so the user doesn't have to do anything but make sure that the database is open.

Thanks,
Leesha

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Automated Report Export

Post by HansV »

If the database will be open, you'd have to make sure that a specific form is open all the time the database is open. That form could be hidden, though.
You can then use the On Timer event of the form to export and send the report.
You could also store the date the report was last sent in a table. The code would check this, to avoid sending the report twice in the same day.

Does that sound OK to you?
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Automated Report Export

Post by Leesha »

That sounds great! I was just thinking that I could create a small DB that is used specifically for this that links to the tables that contain the report data and keep it running on one of the desktops that is always up. That should keep it from sending the same report repeatedly. I love the idea of storing the date/time it was last sent in case the recipients state they didn't get it.

I've never used the On Timer event. Is there anything specific I need to know about it?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Automated Report Export

Post by HansV »

The On Timer event works together with the Timer Interval property of the form.
The Timer Interval is the number of milliseconds between automatic runs of the On Timer event.
For example, if you set it to 60000, the event will fire every 60000/1000 = 60 seconds, i.e. once every minute.
I don't recommend setting the TimerInterval to less than, say, 10000, otherwise users will experience choppy performance.
In this situation it could be quite large, for example 600000 for every 10 minutes.
If you set the Timer Interval to 0, it will disable the On Timer event.

Here is an (incomplete!) example of what the event procedure could look like:

Code: Select all

Private Sub Form_Timer()
    Dim dtmLastSent As Date
    Dim strRecipients As String
    ' Check the time - is it past 12 noon?
    If Hour(Now) >= 12 Then
        ' Check whether the report has been sent
        dtmLastSent = DLookup(...)
        ' Only act if report was last sent before today
        If Int(dtmLastSent) < Date Then
            ' Assemble the recipients here
            ...
            ' Send message
            DoCmd.SendObject _
                ObjectType:=acSendReport, _
                ObjectName:="rptMyReport", _
                OutputFormat:=acFormatXLSX, _
                To:=strRecipients, _
                Subject:="Daily Report", _
                MessageText:="This is a test og the emergency broadcasting system.", _
                EditMessage:=False
            ' Update last sent date
            DoCmd.RunSQL "UPDATE sometable SET LastSent = Now()"
        End If
    End If
End Sub
Last edited by HansV on 08 Oct 2022, 19:54, edited 2 times in total.
Reason: to correct typos
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Automated Report Export

Post by Leesha »

Thanks Hans! Just to be sure I'm clear:

If I wanted the Timer interval to check ever 10 minutes, I would replace the 0 with 600000 and I would use code similar to the code you provided in the on timer event?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Automated Report Export

Post by HansV »

Yes, indeed.
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Automated Report Export

Post by Leesha »

Cool! I just tested the code with a message box popup and it worked! I'm so excited! My next question, in the code where I assemble the recipients, how would that look it I'm using a table to store their names and email addresses? Would I do a query with the email addresses or do I need to put their addresses directly in the code.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Automated Report Export

Post by HansV »

It could look like this. You'll have to use the name of your table and field, of course.

Code: Select all

            ' Assemble the recipients here
            Dim dbs As DAO.Database
            Dim rst As DAO.Recordset
            Dim sql As String
            Set dbs = CurrentDb
            ' SQL statement - modify as needed
            sql = "SELECT Recipient FROM tblRecipients ORDER BY Recipient"
            ' Open recordset
            Set rst = dbs.OpenRecordset(sql, dbOpenForwardOnly)
            ' Loop through the records
            Do While Not rst.EOF
                ' Concatenate recipient
                strRecipients = strRecipients & ";" & rst!Recipient
                ' On to the next record
                rst.MoveNext
            Loop
            rst.Close
            ' Remove the first semicolon
            strRecipients = Mid(strRecipients, 2)
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Automated Report Export

Post by Leesha »

Getting close! I'm so excited. This is the code that I have with my specific table info. It gave me a warning stating "Named Argument not found" and highlighted Objectype:=acSendReport. Also one more question, what would the code look like if I need to attach more than one report?
Thanks!

Code: Select all

Dim dtmLastSent As Date
    Dim strRecipients As String
    ' Check the time - is it past 12 noon?
    If Hour(Now) >= 12 Then
        ' Check whether the report has been sent
        dtmLastSent = DLookup("DateSent", "qryDateLastSent")
        ' Only act if report was last sent before today
        If Int(dtmLastSent) < Date Then
          
        ' Assemble the recipients here
             Dim dbs As DAO.Database
            Dim rst As DAO.Recordset
            Dim sql As String
            Set dbs = CurrentDb
            ' SQL statement - modify as needed
            sql = "SELECT Recipient FROM tblEmailRecipients ORDER BY Recipient"
            ' Open recordset
            Set rst = dbs.OpenRecordset(sql, dbOpenForwardOnly)
            ' Loop through the records
            Do While Not rst.EOF
                ' Concatenate recipient
                strRecipients = strRecipients & ";" & rst!Recipient
                ' On to the next record
                rst.MoveNext
            Loop
            rst.Close
            ' Remove the first semicolon
            strRecipients = Mid(strRecipients, 2)
     
     ' Send message
            DoCmd.SendObject _
                Objectype:=acSendReport, _
                ObjectName:="qryCDSLease", _
                OutputFormat:=acFormatXLSX, _
                To:=strRecipients, _
                SubjectMessage:="Daily Report", _
                MessageText:="This is a test og the emergency broadcasting system.", _
                EditMessage:=False
    
            
            ' Update last sent date
            DoCmd.OpenQuery "qryUpdateLastSent"
            Else: MsgBox "The report has already been sent"
            
        
        
        End If
    End If

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Automated Report Export

Post by HansV »

Sorry, it should be ObjectType (two t's)

You wrote that you wanted to send a report, but it looks like you're sending a query. So change acSendReport to acSendQuery.
Or is qryCDSLease a report?

If you want to attach multiple objects, you'd need completely different, more complicated code - like the code you already have to automate Outlook to generate email messages,
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Automated Report Export

Post by Leesha »

Sorry, I didn't realize it would make a difference. The query I listed is the one of the ones they currently export to excel and then attach to an email.

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Automated Report Export

Post by Leesha »

Now I get the same error pointing to this:
SubjectMessage:="Daily Report", _

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Automated Report Export

Post by HansV »

Sorry, that's what you get when you try to write code in your head. It should be Subject:=...
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Automated Report Export

Post by Leesha »

No need to be sorry! I couldn't write this if my life depended on it. I fixed that code but unfortunately now get an error that says "Too few parameters. Expected 1. and it points to Set rst = dbs.OpenRecordset(sql, dbOpenForwardOnly)

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Automated Report Export

Post by HansV »

What is the value of your sql variable?
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Automated Report Export

Post by Leesha »

So your last message stumped me but when I "thought like Hans" I realized that the Order by clause had the wrong info. I corrected that and now the email goes out. Unfortunately I get this popup which freezes the code unless I hit allow. Is there a way around this?
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Automated Report Export

Post by HansV »

Yikes. I thought that wasn't necessary anymore. We'll probably have to go with automating Outlook instead. That would enable you to attach multiple attachments too.
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Automated Report Export

Post by Leesha »

OK, I'll try to replicate some of the code you've given me in the past and make it work. I'm sure to be back with questions!! Thanks!