ACCESS FEED: Re: Re: [MS_AccessPros] send email in html format

Kamis, 10 September 2015

Re: Re: [MS_AccessPros] send email in html format

John-

I did it like this:

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

Then, in a form, I did like this:

Private Sub Command1_Click()
SendReportHTML "R_Books_ALL", Me.EmailAddress, Me.Subject
End Sub

After click on the button. Error appears.

What's wrong?

Kevin

Regards,

Kevin Zhao

From: John Viescas JohnV@msn.com [MS_Access_Professionals]
Date: 2015-09-10 23:43
To: MS_Access_Professionals
Subject: Re: [MS_AccessPros] send email in html format

Kevin-

OK, to add the first parameter, I told you to change

Sub SendReportHTML()

to

Sub SendReportHTML(strReportName As String)

To add a second parameter, I told you to add a comma, then the name of the new parameter, and its data type. Like this:

Sub SendReportHTML(strReportName As String, strEmail As String)

What was so hard about that?

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
http://www.viescas.com/
(Paris, France)

On Sep 10, 2015, at 5:34 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-
I put the following code in a module, and created a button "sendhtml" (SendReportHTML "My Report Name"). It worked perfect, except that I don't know how to add a second parameter so that I would be able to speccify the email address and email subject in a form. Even though you have given some instruction, as I am not good at writting code, I still didn't know how to do it. Please help!

Sub SendReportHTML(strReportName 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 = qingqinga@yahoo.com
        .Subject = "HTML Email"
        .BodyFormat = olFormatHTML
        .HTMLBody = strHTML
        .Send
    End With

    ' Clear the objects
    Set objOlMail = Nothing
    Set objOlApp = Nothing

End Sub

Regards,
Kevin Zhao

From: John Viescas JohnV@msn.com [MS_Access_Professionals]
Date: 2015-09-10 23:24
To: MS_Access_Professionals
Subject: Re: [MS_AccessPros] send email in html format

Kevin-

What did you try to do? Please post the revised code. If I see that, then I can tell you if anything is wrong.

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
http://www.viescas.com/
(Paris, France)

On Sep 10, 2015, at 5:04 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-
I didn't get it well. Would you please give an shot example in context? Please help!
By the way, I just solved the pop up error. You're right, it caused by wrong setting of the email account.
Thanks,
Kevin

Regards,
Kevin Zhao

From: John Viescas JohnV@msn.com [MS_Access_Professionals]
Date: 2015-09-10 22:41
To: MS_Access_Professionals
Subject: Re: [MS_AccessPros] send email in html format

Kevin-

I'm trying to teach you a bit by giving you examples that are easy to follow (I comment nearly every line of code). I previously showed you how to add a parameter:

Sub SendReportHTML(strReportName As String)

To do a second parameter, add a comma and the parameter declaration as VariableName As VariableType. When you do the call to the procedure, add a comma after the first parameter that has the report name and add the email you want to use or a reference to the control on the form that contains the email. To reference a control on a form in code behind the form, use Me and the name of the control - as in:

SendReportHTML "<name of report>", Me.txtEmail

In the code in the procedure you call, you can use the second parameter name you declared in the Sub statement just like a variable. Let's say you decide to use strEmail as the name of the parameter. To use it, you would then do:

.To = strEmail

Simple as that…

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
http://www.viescas.com/
(Paris, France)

On Sep 10, 2015, at 4:17 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-
I am not good at writing code, can you please give an example in details? Thanks. By the way, It sent the email successfully.
Kevin

Regards,
Kevin Zhao

From: John Viescas JohnV@msn.com [MS_Access_Professionals]
Date: 2015-09-10 21:53
To: MS_Access_Professionals
Subject: Re: [MS_AccessPros] send email in html format

Kevin-

Sure. Add another parameter and pass the value typed in an unbound text box to the code. Change the assignment of the .To property to use the parameter value.

Is it actually sending the email successfully? My code sets the Outlook object to Nothing, which should disconnect your app from Outlook. I suspect there's some setting in your copy of Outlook that's causing the extra messages - especially since you also report that AD's code does the same thing.

====>> Any Outlook experts want to jump in???

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
http://www.viescas.com/
(Paris, France)

On Sep 10, 2015, at 2:41 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-

It worked, but when I try to close the form, Mircrosoft Outlook will pop up a window saying that "The Sent Items folder is unavailable." , and after a while, another window pops up saying that "Your IMAP server closed the connection.". Can this be solved?
One more thing, is it possilbe that when clicking on the send html button in the form, the email address in the form will be used, instead of using the spacific email address written in the module. and the subject in the form will be used as well.

Thanks!

Best Regards,
Kevin

Regards,
Kevin Zhao

From: John Viescas JohnV@msn.com [MS_Access_Professionals]
Date: 2015-09-10 17:18
To: MS_Access_Professionals
Subject: Re: [MS_AccessPros] send email in html format

Kevin-

You can put it in any standard module. It would probably be better to modify the code to accept the report name as a parameter. Change two lines:

Sub SendReportHTML(strReportName As String)

and:

DoCmd.OutputTo acOutputReport, strReportName, acFormatHTML, "< path to the folder you want here >"

Then you can call the code from something like the Click event of a command button:

SendReportHTML "My Report Name"

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
http://www.viescas.com/
(Paris, France)

On Sep 10, 2015, at 2:09 AM, zhaoliqingoffice zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-

Where should I put these codes to? thanks!

Kevin

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

Kevin-

Yes, if you dink with the page size, you can make your report output onto one page.

Here's some code to get you started. It compiles, but I don't guarantee that it works. I'm using a technique called "late binding" so that you don't even have to have the Outlook library loaded as a reference. I do that by hand-coding the couple of Outlook constants that the code needs, then using CreateObject to dynamically open a copy of Outlook. Note that this will send only the first page if the report outputs multiple pages.

Sub SendReportHTML()
Dim strHTML As String, strInput 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, "Customer Address Book One Page", 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 = "johnv@msn.com"
.Subject = "HTML Email"
.BodyFormat = olFormatHTML
.HTMLBody = strHTML
.Send
End With

' Clear the objects
Set objOlMail = Nothing
Set objOlApp = Nothing

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
http://www.viescas.com/
(Paris, France)

On Sep 6, 2015, at 1:07 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-
Thanks a lot. Would you please give an example in code? Would it be possible to create a long page without break?

Regards,
Kevin Zhao

From: John Viescas JohnV@msn.com [MS_Access_Professionals]
Date: 2015-09-06 17:53
To: MS_Access_Professionals
Subject: Re: [MS_AccessPros] send email in html format

Kevin-

The easiest way to do that would perhaps to export the report as HTML then open the file created in code and copy the HTML from it into your mail body line by line. But if the report has multiple pages, I seem to recall that exporting as HTML creates web pages with buttons to go forward / backward between the pages. Each page is a separate file.

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
http://www.viescas.com/
(Paris, France)

On Sep 6, 2015, at 11:30 AM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-
For this part: "Enter the message text here." Is that possible to convert rptReport to html and put it in the mail body? I have tables in in the report.
Thanks,
Kevin

zhaoliqingoffice@163.com

From: John Viescas JohnV@msn.com [MS_Access_Professionals]
Date: 2015-09-06 15:38
To: MS_Access_Professionals
Subject: Re: [MS_AccessPros] send email in html format

Kevin-

Yes, you can do it using Outlook automation. Set a reference to the Outlook library in VBA, then use code like this:

Sub CreateHTMLMail()
'Creates a new e-mail item and modifies its properties.

Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem
Set olApp = Outlook.Application
'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)

With objMail
' Set recipient
.To = "Kevin@someMail.com"
' Set subject
.Subject = "A message in HTML format…"
'Set body format to HTML
.BodyFormat = olFormatHTML
.HTMLBody = "<HTML><BODY>Enter the message text here. </BODY></HTML>"
.Send
End With
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
http://www.viescas.com/
(Paris, France)

On Sep 6, 2015, at 3:07 AM, zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Dear All,
I have learnt how to send html as attachment by the code below.

DoCmd.SendObject acSendReport,"rptReport", acFormatHTML,To:= Email here, Subject:=, Message:=False

My question is, is that possible to send html in email content, instead of as attachment?

Many thanks,
Kevin