Access 97: Sending Report as Email Body not Attachment

[Lee]

As the subject states, I have been playing in the system trying to
figure out a method of using a report as the email body text.

So far, the closest I have come is the acFormatHTML. However, when
the report is more than one page, it autoformats the report to
multiple pages. I need one document.

Reason for doing this as text vs. attachment is in order to make the
message easier to view for the reader/receipent.

Any suggestions?

Here is the code:
Sub SendWithAtt(ByRef str_Message As str_EMAIL, Optional
str_ReportName As String, Optional dbl_OrderID As Double)
Dim CurrFile As String, olMailItem As Variant
Dim oApp As Object, m As Object
Dim SQL As String
Dim rst As Recordset, AppInTesting As Boolean

Set oApp = GetObject(, "Outlook.application")
If Err.Number <> 0 Then
Set oApp = CreateObject("Outlook.Application")
End If
On Error GoTo Err_SendWithAtt
If nz(dbl_OrderID, 0) > 0 Then
DoCmd.OpenReport str_ReportName, acViewPreview, "(orderid = "
& dbl_OrderID & ")"
Else
DoCmd.OpenReport str_ReportName, acViewPreview
End If

With oApp
Set m = .CreateItem(olMailItem)
With m
.To = str_Message.str_TO
.bcc = "my.email@address"
.Subject = str_Message.str_SUBJECT
.HTMLBody = ReportToHTML(Reports(str_ReportName))
.deferreddeliverytime = Now()
.deleteaftersubmit = True
.ReadReceiptRequested = False
.display
'.send
End With
End With
DoCmd.Close acReport, str_ReportName, acSaveNo
Set m = Nothing
Set oApp = Nothing

End Sub

Function ReportToHTML(Rpt As Report)

Dim obj_A As Object, obj_B As Object
Dim str_File As String

str_File = "C:\temp\report1.htm"

DoCmd.OutputTo acOutputReport, Rpt.Name, acFormatHTML, TempFile

Set obj_A = CreateObject("Scripting.FileSystemObject")
Set obj_B = fso.GetFile(TempFile).OpenAsTextStream(1, -2)

ReportToHTML = obj_B.ReadAll

obj_B.Close

Set obj_B = Nothing
Set obj_A = Nothing
Kill str_File

End Function

[Herbert Chan]

What do you actually have in the report?? If it's just basically text, why
don't you put the text in a procedure and run the procedure to generate the
email? Even you require HTML formating, I still think that it's possible to
generate HTML formatted email right within the procedure. This removes any
uncertainty brought by the direct export of report into HTML.

herbert

"Lee" <le*****@yahoo.com> ¦b¶l¥ó
news:fe**************************@posting.google.c om ¤¤¼¶¼g...

As the subject states, I have been playing in the system trying to
figure out a method of using a report as the email body text.

So far, the closest I have come is the acFormatHTML. However, when
the report is more than one page, it autoformats the report to
multiple pages. I need one document.

Reason for doing this as text vs. attachment is in order to make the
message easier to view for the reader/receipent.

Any suggestions?

Here is the code:
Sub SendWithAtt(ByRef str_Message As str_EMAIL, Optional
str_ReportName As String, Optional dbl_OrderID As Double)
Dim CurrFile As String, olMailItem As Variant
Dim oApp As Object, m As Object
Dim SQL As String
Dim rst As Recordset, AppInTesting As Boolean

Set oApp = GetObject(, "Outlook.application")
If Err.Number <> 0 Then
Set oApp = CreateObject("Outlook.Application")
End If
On Error GoTo Err_SendWithAtt
If nz(dbl_OrderID, 0) > 0 Then
DoCmd.OpenReport str_ReportName, acViewPreview, "(orderid = "
& dbl_OrderID & ")"
Else
DoCmd.OpenReport str_ReportName, acViewPreview
End If

With oApp
Set m = .CreateItem(olMailItem)
With m
.To = str_Message.str_TO
.bcc = "my.email@address"
.Subject = str_Message.str_SUBJECT
.HTMLBody = ReportToHTML(Reports(str_ReportName))
.deferreddeliverytime = Now()
.deleteaftersubmit = True
.ReadReceiptRequested = False
.display
'.send
End With
End With
DoCmd.Close acReport, str_ReportName, acSaveNo
Set m = Nothing
Set oApp = Nothing

End Sub

Function ReportToHTML(Rpt As Report)

Dim obj_A As Object, obj_B As Object
Dim str_File As String

str_File = "C:\temp\report1.htm"

DoCmd.OutputTo acOutputReport, Rpt.Name, acFormatHTML, TempFile

Set obj_A = CreateObject("Scripting.FileSystemObject")
Set obj_B = fso.GetFile(TempFile).OpenAsTextStream(1, -2)

ReportToHTML = obj_B.ReadAll

obj_B.Close

Set obj_B = Nothing
Set obj_A = Nothing
Kill str_File

End Function

[Lee]

Herbert,
Thanks.
Reason I want/need to use a more graphically appealing email look is
to allow the information to be more easily read.

There is information on the report which contains date of request,
date due, date delivered. Then there are memo fields which outline
what is to be delivered, and a few other ones.

In text mode, you can not force/state the font. Therefore, things
don't line up correctly. It gets difficult to provide an easily
readible format.

Hope this helped to answer your question. If not, please feel free to
ask more.
Lee