Is it possible to automate a report to email everyday?

[parsok]

I have an access report that I email in outlook everyday. Is there anyway to automate this process without human intervention?

 

[smedvid]

One way is to create a seperate Access database with an Autoexec macro. That Macro would call a function/procedure within a Module. The function/procedure can then execute a query, etc. and e-mail the results.

I previously did something similar and just provided the users' a Excel file that was the raw data (a single query)used to generate the report. Then I used to Docmd.SendObject... I think...

htwh, Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents - Please Show Your Support...

 

[scriverb]

I generate a report daily and email the results to a list of email addresses as Steve has suggested by having a procedure that is called by a AutoExec. This database application is setup on a schedule so that it runs by itself, executes the reports, and emails the results to the addressee's. The email addresses are kept in a table with the following data fields.

Name, EmailAddress, and ToCCBC(values TO,CC,and BC)
The ToCCBC field identifies where to place the email address on the group email. Each group is selected with a seperate query: qryEmailAddressesTO, qryEmailAddressesCC, and qryEmailAddressesBC.

Dim MyDB as database
Dim MyRS as recordset
Dim vEmailAddresses as string
Dim vEmailType as string
Dim vEmailSubject as string
Dim vEmailBody as string
Dim vTOAddresses as string
Dim vCCAddresses as string
Dim vBCAddresses as string
Set MyDB = currentdb
Set MyRS = MyDB.OpenRecordset("qryEmailAddressesTO", dbOpenDynaset)
MyRS.MoveFirst
vEmailType = "TO"
vEmailAddresses = ""
Do
Do
vEmailAddresses = vEmailAddresses & IIf(vEmailAddresses = "", MyRS("EmailAddress&quot, ", " & MyRS("EmailAddress&quot)
MyRS.MoveNext
Loop Until MyRS.EOF
Select Case vEmailType
Case "TO"
vToAddresses = vEmailAddresses
vEmailAddresses = ""
MyRS.Close
Set MyRS = MyDB.OpenRecordset("qryEmailAddressesCC", dbOpenDynaset)
vEmailType = "CC"
Case "CC"
vCCAddresses = vEmailAddresses
vEmailAddresses = ""
MyRS.Close
Set MyRS = MyDB.OpenRecordset("qryEmailAddressesBC", dbOpenDynaset)
vEmailType = "BC"
Case "BC"
vBCAddresses = vEmailAddresses
vEmailAddresses = ""
vEmailType = ""
End Select
Loop Until vEmailType = ""
MyRS.Close
vEmailSubject = "Subject text"
vEmailBody = "Body of email message text"

DoCmd.SendObject acReport,"ReportName" ,"SnapshotFormat(*.snp) , vToAddresses, vCCAddresses, vBCAddresses, vEmailSubject, vEmailBody, False
End If

The report format that I use always is Microsoft's SnapShotViewer. The above command includes the correct syntax for this selection. This does not come loaded with Windows but you can download it from Microsoft's page and load it to all PC's free of charge.

Link:

http://www.microsoft.com/accessdev/prodinfo/snapshot.htm

This output type can be emailed without losing any of ACCESS formatting capabilities.

I hope this helps you.

Bob Scriver

 

[parsok]

Thank you so much!! This is great.