Automated Report Export

[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

[HansV]

Do you already have the code to export the report?

[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

[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?

[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?

[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

[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?

[HansV]

Yes, indeed.

[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.

[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)

[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

[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,

[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]

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

[HansV]

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

[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)

[HansV]

What is the value of your sql variable?

[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?

[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.

[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!