Hello,
I have been trying to read up on how to automaticall e-mail reports from a database to different people but I'm not sure I'm getting the whole picture.
I want to e-mail 3 sepaprate reports to 3 diffrent people (on a daily basis) and I was wondering:
1.) Is the "SendObject" macro the best way to do it?
2.) Is there a better way?
3.) Is there a way to have Access do it automatically every day?
I've seen some examples of code but I'm not sure about what's the best way?
Thanks!!
Allie
Full Version: E-mailing a Report
Hi,
have a look at this site !
It includes sample code and applications which should be similar to what you want.
You should move away from macros. They are very limited and do not support error handling. You an achieve this through VBA (visual basic for applications) code.
To automatically do it you need to automatically open the db every day unless it is being open manually by someone for sure. If so then you can just run it on an on open event of a form or so if not you need to call a macro from the windows scheduler which then calls your module to run this stuff. You can read more on how to achieve this here.
HTH
Good luck
have a look at this site !
It includes sample code and applications which should be similar to what you want.
You should move away from macros. They are very limited and do not support error handling. You an achieve this through VBA (visual basic for applications) code.
To automatically do it you need to automatically open the db every day unless it is being open manually by someone for sure. If so then you can just run it on an on open event of a form or so if not you need to call a macro from the windows scheduler which then calls your module to run this stuff. You can read more on how to achieve this here.
HTH
Good luck
Could I ask you which one would be the best? I took a look at some and they seem a little confusing.....
Which do you think would be the best (and easiest to follow) for a beginner?
Which do you think would be the best (and easiest to follow) for a beginner?
Well the easiest would be sendobject. But it is very very limited and has flaws. You can read more on that here!
You could also look at redepmtions: outlook redemptions
or
search UA for alternatives. There have been many discussions on this e.g.:
email01
email02
...
HTH
Good luck
You could also look at redepmtions: outlook redemptions
or
search UA for alternatives. There have been many discussions on this e.g.:
email01
email02
...
HTH
Good luck
Allie,
this is ONE way -- not necessarily the best ...
(btw, freakazeud, thanks for the links -- you have posted them before -- When I was working on email functions my research brought me to you)
this code goes into a general module
this assumes the report has already been filtered and the filter has been saved
both of these procedures are posted here:
code - basic procedures
http://www.utteraccess.com/forums/showflat...;Number=1036232
In my opinion, the best format to send a report is SNAP if the users only need to look at it -- because it is an image, it looks exactly the same.
The above procedure sends it in RTF format -- lines and such don't translate well.
for SNAP format (user has to have the free reader), use
acFormatSNP
instead of
acFormatRTF
If you cannot render files in SNP (snapshot) format,
here is a link to the Microsoft site to download the SNAPSHOT viewer for Access
http://www.microsoft.com/downloads/details...;displaylang=en
this is ONE way -- not necessarily the best ...
(btw, freakazeud, thanks for the links -- you have posted them before -- When I was working on email functions my research brought me to you)
this code goes into a general module
CODE
'------------------------------------ EMailReport
Sub EMailReport(pReportName As String, pEmailAddress As String, pFriendlyName As String _
, pBooEditMessage As Boolean, pWhoFrom As String)
'Email a report to someone and construct the subject and message
'example useage: on the command button code to process a report
' EMailReport "MyReportname", "anyone@mymailbox.com", _
"The 3rd Quarter Profits Report", false, "Sally Smith"
'PARAMETERS
'pReportName --> "MyReportname"
'pEmailAddress --> "anyone@mymailbox.com"
'pFriendlyName --> "The 3rd Quarter Profits Report"
'pBooEditMessage --> true if you want to edit the message before mail is sent
' --> false if you want it to get sent automatically
'pWhoFrom --> "Sally Smith"
On Error GoTo EMailReport_error
DoCmd.SendObject acSendReport, pReportName, acFormatRTF, pEmailAddress _
, , , pFriendlyName & Format(Now(), " ddd m-d-yy h:nn am/pm"), _
pFriendlyName & " is attached --- " _
& "Regards, " & pWhoFrom, pBooEditMessage
Exit Sub
EMailReport_error:
MsgBox Err.Description, , "ERROR " & Err.Number & " EMailReport"
'press F8 to find problem and fix -- comment out next 2 lines when code is done
Stop
Resume
End Sub
Sub EMailReport(pReportName As String, pEmailAddress As String, pFriendlyName As String _
, pBooEditMessage As Boolean, pWhoFrom As String)
'Email a report to someone and construct the subject and message
'example useage: on the command button code to process a report
' EMailReport "MyReportname", "anyone@mymailbox.com", _
"The 3rd Quarter Profits Report", false, "Sally Smith"
'PARAMETERS
'pReportName --> "MyReportname"
'pEmailAddress --> "anyone@mymailbox.com"
'pFriendlyName --> "The 3rd Quarter Profits Report"
'pBooEditMessage --> true if you want to edit the message before mail is sent
' --> false if you want it to get sent automatically
'pWhoFrom --> "Sally Smith"
On Error GoTo EMailReport_error
DoCmd.SendObject acSendReport, pReportName, acFormatRTF, pEmailAddress _
, , , pFriendlyName & Format(Now(), " ddd m-d-yy h:nn am/pm"), _
pFriendlyName & " is attached --- " _
& "Regards, " & pWhoFrom, pBooEditMessage
Exit Sub
EMailReport_error:
MsgBox Err.Description, , "ERROR " & Err.Number & " EMailReport"
'press F8 to find problem and fix -- comment out next 2 lines when code is done
Stop
Resume
End Sub
this assumes the report has already been filtered and the filter has been saved
CODE
'------------------------------------ SetReportFilter
Sub SetReportFilter(pReportName As String, pFilter As String)
'Save a filter to the specified report
'You can do this before you send a report in an email message
'You can use this to filter subreports instead of putting criteria in the recordset
' USEAGE:
' example: in code that processes reports for viewing, printing, or email
' SetReportFilter "MyReportname","someID=1000"
' SetReportFilter "MyAppointments","City='Denver' AND dt_appt=#9/18/05#"
' written by Crystal
' Strive4peace2004@yahoo.ca
' PARAMETERS:
' pReportName is the name of your report
' pFilter is a valid filter string
On Error GoTo SetReportFilter_error
'---------- declare variables
Dim rpt As Report
'---------- open design view of report and set the report object variable
DoCmd.OpenReport pReportName, acViewDesign
Set rpt = Reports(pReportName)
'---------- set report filter and turn it on
rpt.Filter = pFilter
rpt.FilterOn = IIf(Len(pFilter) > 0, True, False)
'---------- save and close the changed report
DoCmd.Save acReport, pReportName
DoCmd.Close acReport, pReportName
'---------- Release object variable
Set rpt = Nothing
Exit Sub
SetReportFilter_error:
Resume Next
MsgBox Err.Description, , "ERROR " & Err.Number & " SetReportFilter"
'press F8 to step thru code and fix problem
Stop
Resume
'next line will be the one with the error
End Sub
Sub SetReportFilter(pReportName As String, pFilter As String)
'Save a filter to the specified report
'You can do this before you send a report in an email message
'You can use this to filter subreports instead of putting criteria in the recordset
' USEAGE:
' example: in code that processes reports for viewing, printing, or email
' SetReportFilter "MyReportname","someID=1000"
' SetReportFilter "MyAppointments","City='Denver' AND dt_appt=#9/18/05#"
' written by Crystal
' Strive4peace2004@yahoo.ca
' PARAMETERS:
' pReportName is the name of your report
' pFilter is a valid filter string
On Error GoTo SetReportFilter_error
'---------- declare variables
Dim rpt As Report
'---------- open design view of report and set the report object variable
DoCmd.OpenReport pReportName, acViewDesign
Set rpt = Reports(pReportName)
'---------- set report filter and turn it on
rpt.Filter = pFilter
rpt.FilterOn = IIf(Len(pFilter) > 0, True, False)
'---------- save and close the changed report
DoCmd.Save acReport, pReportName
DoCmd.Close acReport, pReportName
'---------- Release object variable
Set rpt = Nothing
Exit Sub
SetReportFilter_error:
Resume Next
MsgBox Err.Description, , "ERROR " & Err.Number & " SetReportFilter"
'press F8 to step thru code and fix problem
Stop
Resume
'next line will be the one with the error
End Sub
both of these procedures are posted here:
code - basic procedures
http://www.utteraccess.com/forums/showflat...;Number=1036232
In my opinion, the best format to send a report is SNAP if the users only need to look at it -- because it is an image, it looks exactly the same.
The above procedure sends it in RTF format -- lines and such don't translate well.
for SNAP format (user has to have the free reader), use
acFormatSNP
instead of
acFormatRTF
If you cannot render files in SNP (snapshot) format,
here is a link to the Microsoft site to download the SNAPSHOT viewer for Access
http://www.microsoft.com/downloads/details...;displaylang=en
anyway to send a report as say word or something else? In a corporate environment, it is difficult o have a user download something like snapshot. You would think that Microsoft, which owns word and access (duh!), could output in one of their formats.
cfmoorejr, Welcome to Utter Access 
... which is why I posted the code to use RTF ... but with a caveat
People WITHOUT Access can download the SNAPSHOT viewer. Everybody gets the Adobe Viewer without any problems...
Even if rendering SNAP files was built into Access, those without the product would still have to get the viewer
... which is why I posted the code to use RTF ... but with a caveat
People WITHOUT Access can download the SNAPSHOT viewer. Everybody gets the Adobe Viewer without any problems...
Even if rendering SNAP files was built into Access, those without the product would still have to get the viewer
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.