Kevin-
What you need to do:
From A.D.'s database, import these modules:
M_MakeFolder
M_SendReport
The code uses the Scripting library, so you need to open a module and choose References from the Tools menu. In the list of libraries, fine Microsoft Scripting Runtime and select it. Close the dialog and compile and save the code. If you get compile errors, let me know what they are.
The code to export a report to HTML and then send it looks like this:
Private Sub CmdSend_Click()
On Error GoTo ErrTrap
' Turn on hourglass because this may take some time
DoCmd.Hourglass True
' Call the export and send routine, passing it the name of the report and the email "to" list
P_ReportInMailBody_DirectHTML _
Nz(Me.CboReport, ""), Nz(Me.TxtAddress, "")
' Wait a bit to be sure it finished
P_Wait 100
' Turn off the hourglass
DoCmd.Hourglass False
MsgBox "Finished" & vbCrLf & _
"Selected Report Has Been Placed " & _
"In The OutBox", vbOKOnly, "Task Completed"
On Error Resume Next
Me.CmdClose.SetFocus
ExitPoint:
DoCmd.Hourglass False
On Error GoTo 0
Exit Sub
ErrTrap:
MsgBox Err.Number & " - " & Err.Description
Resume ExitPoint
End Sub
What's so hard about that? Note that the call to P_ReportInMailBody_DirectHTML passes values of controls on the form in which this code runs. You will need to modify it to pass it the report name and email recipient list.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Nov 7, 2015, at 12:34 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John-
Here's the link:
Best Regards,
Kevin
Date: 2015-11-07 17:54Subject: Re: [MS_AccessPros] Send specific report with SendReportHTML functionKevin-
Do you still have the link to A.D.'s code? I didn't save it.John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)
On Nov 7, 2015, at 12:34 AM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:John-With your code, I can do something like : SendReportHTML "My Report Name"But I didn't find how to use AD's code to get this done. His code shows how to send mutiple reports or single report, but I didn't find the syntax for SendReportHTML function.Best Regards,Kevin
Date: 2015-11-07 04:44Subject: Re: [MS_AccessPros] Send specific report with SendReportHTML functionKevin-
If your report has multiple pages, you need to use AD's code. I found it fairly straightforward. What don't you understand?John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)
On Nov 5, 2015, at 10:37 AM, zhaoliqingoffice zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:John-
Would please change the code a little bit so that I can send the report pages all at once? AD's code is very abstruse. I couldn't handle it. Please help.
Best Regards,
Kevin
在 "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>，2015年11月5日 下午5:18写道：Kevin-
It's been ages since I used ..\ folder reference. I think that goes to whatever windows thinks is the current folder. Have you tried doing a search in Windows Explorer for TestReport.html?"C:\Test\…" will fail if there's not a Test folder on the C drive.John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)
On Nov 5, 2015, at 9:46 AM, zhaoliqingoffice zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:John-
I got it. One more question, "C:\Test\TestReport.html" always gets error,so I changed it to "..\TestReport.html", it works perfect. Then I wonder where the output html goes to? since I couldnt find it in me computer at all. If it doesn't exist that would be great for me. But I worried this would take a lot of space after running some time. Please help.
Best Regards,
Kevin
在 "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>，2015年11月5日 下午3:58写道：Kevin-
I *think* you can output just one page if you first open the report filtered on the on GroupBookingID that you want. You can open it in Print Preview but hidden.DoCmd.OpenReport strReportName, acViewPreview, WhereCondition:="GroupBookingID = " & Me.Parent.GroupBookingID, WindowMode:=acHiddenBe sure to close the report after you have output it.John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)
On Nov 5, 2015, at 6:36 AM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:John-For the SendReportHTML function, is that possible to send report from a specific record? For example: to send a report (rptItineraryPart) with ID=Me.Parent.GroupBookingID. By the way, here's again, the function you sent to me before:Sub SendReportHTML(strReportName As String, strEmail As String, strSubject As String)
Dim strHTML As String, strInput As String
Dim strEmailAddress As String
Const olMailItem = 0
Const olFormatHTML = 2
Dim objOlApp As Object, objOlMail As Object
' Output the 1-page report to html
DoCmd.OutputTo acOutputReport, strReportName, acFormatHTML, "C:\Test\TestReport.html"
' Open the resulting file
Open "C:\Test\TestReport.html" For Input As #1
' Create an Outlook session
Set objOlApp = CreateObject("Outlook.Application")
' Start a new email
Set objOlMail = objOlApp.CreateItem(olMailItem)
' Read in the created HTML and put together an output string
Do While Not EOF(1) ' Loop until end of file
' Get a line from the file
Input #1, strInput
' Add it to the accumulated HTML
strHTML = strHTML & strInput
Loop
' Close the file
Close #1
' Use the mail item for several tasks
With objOlMail
.To = strEmail
.Subject = strSubject
.BodyFormat = olFormatHTML
.HTMLBody = strHTML
.Send
End With
' Clear the objects
Set objOlMail = Nothing
Set objOlApp = Nothing
End SubBest Regards,Kevin
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (14) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar