Email a report in the body

[lekmall]

I have looked on the site here forever and can't find a way to email a report in the body of the email.

I tried:

Set myOlApp = New Outlook.Application
Set myItem = myOlApp.CreateItem(olMailItem)

DoCmd.OutputTo acOutputReport, "rptShortReport", acFormatRTF, strOutputFile

If strOutputFile <> "" Then
fnum = FreeFile
Open strOutputFile For Input As #fnum
Do While Not EOF(fnum)
Line Input #fnum, strLine
BodyMsg = BodyMsg & strLine & vbCrLf
Loop
Close fnum
Else
MsgBox "Error in Email Transition!", vbCritical
End If

With myItem
.TO = strTo
.CC = strCCs
.subject = "My Report"
.Body = BodyMsg
.Display
End With

This works fine on my computer because of the Outlook settings and it looks great, but doesn't on some other computers. It will return the rich text format tags along with the text.

If I export the report as a text file and then read it back into the body, I lose the formatting.

I also tried the HTML export, but it seems to have bugs, because the report has lines missing or misplaced.

Anyone know how to do this?

Thanks!

 

[lekmall]

Hi!
Here's what I have so far.
I was able to use the Word object model from MS Access to at least get the document and embed it in the email. That's great! But, it loses it's formatting.

Dim oWord As Word.Application
Dim oWordActiveDoc As Word.Document
Set oWord = New Word.Application

Set oWordActiveDoc = oWord.Documents.Open("c:\test.doc")
oWord.Options.SendMailAttach = False

oWordActiveDoc.SendMail

Now, can anyone please tell me how to send the email and retain it's RTF? I am going crazy over this!!!!

Thank you!

 

[CannDoIT]

I would like to get the answer to this also.

I have been able to get reasonable results by using excel and the following code. However, if the amount of data to be exported into a single cell in the excel is greater than about 256 characters then it truncates the data. So this will not work for my application which contains a lot of text in a memo field. What I need to be able to do is do something similar for an rtf or document file.

However, I have a certain amount of control over the settings on the outlook clients. What are they? I got all of the rtf tags when I tried your code.

With appExcel
.Application.DisplayAlerts = False
.Workbooks.Open FileName:="c:\WeekRepExp.xls"
.ActiveSheet.Range("a1").CurrentRegion.Select
.Selection.Copy
.Workbooks.Close
' note this is a tempate file with the correct format for output
.Workbooks.Open FileName:=strDataPath & strTemplate
.ActiveSheet.Range("A2").Activate
.ActiveSheet.Paste
' need to do some work to get the formating down the page
.Range("A2:c2").Select
.Selection.Copy
.ActiveSheet.Range("a12").CurrentRegion.Select
.Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'output the details as an html file
.ActiveWorkbook.SaveAs FileName:="C:\weekrepexp.htm", _
FileFormat:=xlHtml, ReadOnlyRecommended:=False, CreateBackup:=False
.Workbooks.Close
End With



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

Dim ts

Set fs = CreateObject("Scripting.FileSystemObject")

Set ts = fs.OpenTextFile("c:\weekrepexp.htm", 1)
strtext = ts.ReadAll



strSubject = Combo15.Column(1) & " Weekly Report for the week ended: " & dteWeekEnd


With objMail
.BodyFormat = olFormatHTML
.Subject = strSubject
.Display
.HTMLBody = strtext

End With

 

[lekmall]

Thanks, CannDoIt!

Yes, it is the case where I have memo fields with a great deal of text. What I ended up doing was just writing the report manually with VB to a text file. I found that I had more control over the formatting that way.

I will hold on to your code. It looks like a great solution if memo fields aren't involved.

Thanks!