Sabtu, 07 November 2015

Re: [MS_AccessPros] Send specific report with SendReportHTML function

 

Kevin-


Ah.  A.D.'s code does this:

    ' Insert lines in lieu of LineMarkers
    TxtBody = Replace(TxtBody, LineMarker, "<hr>")

The LineMarker constant is defined as:

Private Const LineMarker As String = "*^*"

Right after the Close #1 statement, try this:

    strHTML = Replace(strHTML, "*^*", "<hr>")

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 6:41 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-
This works great! Thank you so so much. 
By the way I still have a question. The report I created with tables, all the table lines won't show in the html body, it won't show any picture either. I checked AD's report. there's table line in html mail. When I preview my report in within ms access. I can see the table lines.... It's just won't show in the html mail body. What's wrong? 
Best Regards,
Kevin

 
Date: 2015-11-08 01:13
Subject: Re: [MS_AccessPros] Send specific report with SendReportHTML function
 

Kevin-


No need to start Outlook twice nor to create two emails.  Notice that my code already does that - you just need to add the steps to attach the PDF.

Sub SendReportHTML(strReportName As String, strEmail As String, strSubject As String, strAttachmentReportName 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

' NOT NEEDED Dim appOutlook As Outlook.Application
' NOT NEEDED  Dim myItem As Outlook.MailItem
 Dim myAttachments As Object   ' Outlook.Attachments

' First, output the PDF
DoCmd.OutputTo acOutputReport, strAttachmentReportName, acFormatPDF, "C:\TestReport.pdf"
 
 ' NOT NEEDED Set appOutlook = CreateObject("Outlook.Application")
 ' NOT NEEDED Set myItem = appOutlook.CreateItem(olMailItem)
 ' MOVED TO BELOW Set myAttachments = myItem.Attachments
 ' MOVED TO BELOW myAttachments.Add "..\TestReport.pdf"
 ' NOT NEEDED myItem.Display

    ' Output the 1-page report to html
    DoCmd.OutputTo acOutputReport, strReportName, acFormatHTML, "C:\TestReport.html"
    ' Open the resulting file
    Open "C:\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
        ' Set the recipient
        .To = strEmail
        ' Set the subject
        .Subject = strSubject
        ' Tell Outlook email is in HTML format
        .BodyFormat = olFormatHTML
        ' Add the HTML message
        .HTMLBody = strHTML
        ' Point to the attachments of the message
        Set myAttachments = .Attachments
        ' Add the attached file
        myAttachments.Add "C:\TestReport.pdf"
        ' Send the email
        .Send
    End With
    
    ' Clear the objects
    Set objOlMail = Nothing
    Set objOlApp = Nothing
    ' Delete the two files
    Kill "C:\TestReport.pdf"
    Kill "C:\TestReport.html"
End Sub

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 5:57 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-
I did something like this, please check: 

Sub SendReportHTML(strReportName As String, strEmail As String, strSubject As String, strAttachmentReportName 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

 Dim appOutlook As Outlook.Application
 Dim myItem As Outlook.MailItem
 Dim myAttachments As Outlook.Attachments

DoCmd.OutputTo acOutputReport, strAttachmentReportName, acFormatPDF, "..\TestReport.pdf"
 
 Set appOutlook = CreateObject("Outlook.Application")
 Set myItem = appOutlook.CreateItem(olMailItem)
 Set myAttachments = myItem.Attachments
 myAttachments.Add "..\TestReport.pdf"
 myItem.Display

    ' Output the 1-page report to html
    DoCmd.OutputTo acOutputReport, strReportName, acFormatHTML, "..\TestReport.html"
    ' Open the resulting file
    Open "..\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 Sub

Best Regards,
Kevin
 
Date: 2015-11-08 00:06
Subject: Re: [MS_AccessPros] Send specific report with SendReportHTML function
 

Kevin-


Yes, that's what I tried to explain to you.  You must put that code inside mine - do the attach to the MailItem my code creates before sending it.

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 4:56 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-
It did attached the pdf successfuly, but it didn't attach to the same email with SendReportHTML. 
Best Regards,
Kevin

 
Date: 2015-11-07 23:12
Subject: Re: [MS_AccessPros] Send specific report with SendReportHTML function
 

Kevin-


Ah, I should have explained that the example code is set to run inside Outlook, so the Application object in that code expects the Outlook application, not Access.  You need to start up Outlook like this:

 Dim appOutlook As Outlook.Application
 Dim myItem As Outlook.MailItem
 Dim myAttachments As Outlook.Attachments
 
Set appOutlook = CreateObject("Outlook.Application")
 Set myItem = appOutlook.CreateItem(olMailItem)
 Set myAttachments = myItem.Attachments
 myAttachments.Add "..\TestReport.pdf"
 myItem.Display

But the rest of your code doesn't make any sense.  First, you're creating a PDF file from the report named R_Books_ALL.  Next, you're opening rptTest filtered on some ID.  Then you're creating an email and attaching the pdf you just created.  (I see you're still using that strange "..\TestReport.pdf" reference.)  You show the email on the screen by executing the Display method.  Finally, you're calling my code to create another email and send it using the HTML from rptTest.

You need to embed the add attachment code inside my code - add the attachment to the email that that code is creating and sending.  The email you create in the code below will never get sent unless the user chooses to send it.

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 3:51 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-
I did something like this. After compiling it. The error message shows: Method or data member not found.

Private Sub Command1_Click()
On Error GoTo ErrHandle

DoCmd.OutputTo acOutputReport, "R_Books_ALL", acFormatPDF, "..\TestReport.pdf"

DoCmd.OpenReport "rptTest", acViewPreview, , "TestID = " & Me.ReportID, acHidden

 Dim myItem As Outlook.MailItem
 Dim myAttachments As Outlook.Attachments
 
 Set myItem = Application.CreateItem(olMailItem)
 Set myAttachments = myItem.Attachments
 myAttachments.Add "..\TestReport.pdf"
 myItem.Display

SendReportHTML "rptTest", Me.EmailAddress, Me.Subject
DoCmd.Close acReport, "rptTest", acSaveNo



ErrExit:
    Exit Sub
    
ErrHandle:
    Resume ErrExit

End Sub

Best Regards,
Kevin
 
Date: 2015-11-07 22:19
Subject: Re: [MS_AccessPros] Send specific report with SendReportHTML function
 

Kevin-


The purpose of this group is to help people who are using Microsoft Access by teaching them how to get stuff done.  We are not here to write all your code for you.  I'm more than happy to help you debug code that you've tried, but I'm not going to write it all from scratch for you.

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 3:09 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-
Yes, If you can. And I know that I have occupied too much of your time... Don't know to thank you... I have trid to copy and paste few lines, and I've already known it won't work.
Best Regards,
Kevin

 
Date: 2015-11-07 22:01
Subject: Re: [MS_AccessPros] Send specific report with SendReportHTML function
 

Yes, it's possible.


I suppose you want me to write the code for you!

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 2:54 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-
I hope to achieve something this:  SendReportHTML "rptTest", Me.EmailAddress, Me.Subject, acFormatPDF
Is it possible. Thanks.
Best Regards,
Kevin

 
Date: 2015-11-07 21:30
Subject: Re: [MS_AccessPros] Send specific report with SendReportHTML function
 

Kevin-


Yes, it's possible to attach a file to an email you're sending via code.


I don't understand why can't follow a couple of simple steps to use A.D.'s code.

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 2:22 PM, Kevin zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-
Thanks. This still looks difficult for me. Plus I have to use reference to get this work...I will just use your code with short-length report. By the way, is it possible to add pdf attachment while sending html mailbody  with your code?
Best Regards,
Kevin



发自我的小米手机
在 "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>，2015年11月7日 下午9:08写道：

 

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:54
Subject: Re: [MS_AccessPros] Send specific report with SendReportHTML function
 

Kevin-


Do you still have the link to A.D.'s code?  I didn't save it.

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 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:44
Subject: Re: [MS_AccessPros] Send specific report with SendReportHTML function
 

Kevin-


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, 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 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, 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 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:=acHidden

Be sure to close the report after you have output it.

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 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 Sub

Best 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 (37)

.

__,_._,___
Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)