ACCESS FEED: Re: [MS_AccessPros] Email Report Formating question

Jumat, 17 April 2015

Re: [MS_AccessPros] Email Report Formating question

Jim - try this one: http://www.rogersaccesslibrary.com/forum/topic52.html

-Bill

---In MS_Access_Professionals@yahoogroups.com, <luvmymelody@yahoo.com> wrote :

Bill,

I went through rogers library and the search is not helping me any. I keep getting errors stating that the string is less than 3 words. even though it is about five.

Jim Wagner

On Friday, April 10, 2015 7:19 AM, "wrmosca@comcast.net [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

John

It's been a long time since I generated HTML reports. I forgot about each page being a file. The report I worked with never had more than one page. Thanks for the reminder. I might need it in the future.

Come to think of it, A.D. probably has a sample in his library at http://www.rogersaccesslibrary.com/forum/tejpal-ad_forum45.html?SID=924911537a152b7ce1a4adef8149d637662037

-Bill

---In MS_Access_Professionals@yahoogroups.com, <JohnV@msn.com> wrote :

Bill-

That's sorta what he's doing except reading in the source line-by-line. What he's missing is the second and third page files. Would have to do a loop using Dir or something similar to pick it all up.

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 Apr 9, 2015, at 8:25 PM, wrmosca@comcast.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Jim
I've outputted reports to email body as HTML. First I run the report as HTML using DoCmd.OutputTo and save it to a text file with an HTML extension.

Next step is to read the file into a string and insert it into a table. Then I use a recordset to put the HTML into the email. There might be a better way but this one works.

The routine that creates the file and inserts the string into a table:
'Delete record if it exists.
CurrentDb.Execute "DELETE FROM tblStatDailyCensusHTML " _
& "WHERE DateCensus = #" & datMidnight & "# " _
& "AND EntityID = '" & strEntityID & "'", dbSeeChanges

Set fso = CreateObject("Scripting.FileSystemObject")
strFolderPath = Environ("TEMP") & "\FRS_Temp"

'Delete folder if it exists so all files are removed.
If fso.FolderExists(strFolderPath) Then
fso.DeleteFolder strFolderPath, True
End If

fso.CreateFolder strFolderPath
Set fldr = fso.GetFolder(strFolderPath)
strFilePath = fldr.path & "\DailyCensus_" & Format(datMidnight, "yyyymmdd") & ".html"

DoCmd.OutputTo acOutputReport, strRptFile, acFormatHTML, strFilePath

Set f = fso.OpenTextFile(strFilePath, ForReading)

strHTML = f.ReadAll

'Clean up quotes and nul character at end of HTML
strHTML = Replace(Replace(strHTML, vbNewLine, ""), vbNullChar, "")
CurrentDb.Execute "INSERT INTO tblStatDailyCensusHTML(DateCensus, CensusHTML, EntityID) " _
& "Values(#" & datMidnight & "#,'" & strHTML & "', '" & strEntityID & "')", dbSeeChanges

---In MS_Access_Professionals@yahoogroups.com, <luvmymelody@yahoo.com> wrote :

Duane

I wanted the report to be in the body of the message. Html seemed like the logical format

Jim Wagner

On Thursday, April 9, 2015 10:53 AM, "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Jim,
Your issue stem from the HTML formatting. You haven't stated why you need to do this with HTML versus PDF or other.

Duane Hookom, MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Thu, 9 Apr 2015 17:41:56 +0000
Subject: Re: [MS_AccessPros] Email Report Formating question

John,

Follow up question. So my code is outputting an html file based on the pages. So because there are 3 pages, there are 3 html files.

How would I output the report without the multiple pages?

I found the href in the html but the files are overwritten each time I click the send button.

Jim Wagner

On Thursday, April 9, 2015 10:06 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Jim-

You would need to look for the HTML code that does this and not output it. From a sample I generated using the old Northwind database, it's in the next to last line and looks like this:

<A HREF="#">First</A> <A HREF="#">Previous</A> <A HREF="Customer Address BookPage2.html">Next</A> <A HREF="Customer Address BookPage2.html">Last</A>

In your code, you could look for a line that begins with <A HREF= and not include it in the output.

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 Apr 9, 2015, at 5:41 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hello all,
I have a report that I send to my boss at the beginning of the year of my accomplishments. The code is below that sends the report. My issue is that I have the report footer with the date show up between each page. The report shows in the body of the message in outlook as html. The issue is the report footer showing and then text with links for the next page like this. and also the report header.
Is there a way to remove the FIRST Previous..... and the headers and footers in the html? I hope the image comes across
Thank You
Jim Wagner

the code
Private Sub cmdEmailAccomplishmentListReport_Click() Dim fs, f
Dim objOL As Outlook.Application
Dim MyItem As Outlook.MailItem Set objOL = New Outlook.Application
Set MyItem = Outlook.Application.CreateItem(olMailItem) DoCmd.OutputTo acOutputReport, "rptAccomplishmentList", acFormatHTML, "S:\Jim\rptAccomplishmentList.html"
myPath = "S:\Jim\"
Set fs = CreateObject("Scripting.FileSystemObject") Dim i As Integer
i = 1
Dim myFileName As String

strline = ""
strHTML = ""
myFileName = myPath & "rptAccomplishmentList" & ".html"
Do While Dir(myFileName) <> vbNullString And Email <> "NULL"

' Open the .html file (the report generated via the Emissions Process)
Open myFileName For Input As 1
Do While Not EOF(1)
' Read in every line of it and store it in strHTML
Input #1, strline
strHTML = strHTML & strline
Loop
Close 1

i = i + 1
myFileName = myPath & "rptAccomplishmentListPage" & i & ".html"
Loop
' Set f = fs.OpenTextFile("S:\Jim\rptAccomplishmentList.html")
' HTMLBody = f.ReadAll
' f.Close With MyItem
.To = "josephwagner2@outlook.com"
.Subject = "Accomplishments List between " & " " & Forms!frmDates.txtDateFrom & " and " & Forms!frmDates.txtDateTo
.HTMLBody = strHTML
.BodyFormat = olFormatHTML End With MyItem.Display
End Sub

[Non-text portions of this message have been removed]

------------------------------------
Posted by: Jim Wagner <luvmymelody@yahoo.com>
------------------------------------

------------------------------------

Yahoo Groups Links