Email report as body of email (RTF)

[TheDeceived]

Hi all,

Having spent the last hour Googling sites for answers to the issue I'm having, I haven't been able to find a solution so apologies for creating another thread about this (it seems this is quite a popular problem people are having!).

So basically, I want to email a report I'm generating in Access to someone but putting the report into the body of the email, not sending it as an attachment. After looking through these forums, I found the following thread which was really useful on getting the basics done:

https://www.accessforums.net/program...ody-10101.html



This is the code I've skilfully copied and pasted into my Access db:

Function sendRTFEmail()

    Const ForReading = 1, ForWriting = 2, ForAppending = 3
    
    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, "SignOff", acFormatRTF, "C:\Users\john_smith\Desktop\ExampleRTFReport.rtf"
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.OpenTextFile("C:\Users\john_smith\Desktop\ExampleRTFReport.rtf", ForReading)
    RTFBody = f.readall
    f.Close
    
    With MyItem
        .To = "john.smith@example.com"
        .Subject = "Sign Off email"
        .body = RTFBody
    End With
    
    MyItem.display
End Function

So the above code correctly creates an email and populates the "To" and "Subject" fields fine. However, when it goes to populate the body of the email, this is what I get:

http://i47.tinypic.com/2wcenb5.jpg

It's not replicating the layout of the report in the body of the email. It just seems to be giving me a load of rubbish instead!

I've also tried changing the ".body" to ".HTMLBody" but that doesn't seem to help.

Does anyone have any idea where I'm going wrong?

Thanks!

[John_G]

I know what the problem is, but I'm not sure how to fix it.

What's happening is that you are reading the rtf file as a text file, and OpenTextFile just gives you whatever the file contains, as plain text - it has no idea how to decode the rtf formatting, and Outlook doen't know its getting rtf either.

I'm not sure if you can do what you want to do - my guess is no, bul I'll leave that to the real gurus. I think you would be better off just using an attachment.

HTH

John

[pbaldy]

I suspect John is right. An untested option would be to export the report in HTML format instead of RTF, and obviously use HTMLBody. That may not work if the report is longer than a page. If memory serves, Access creates a separate HTML file for each page.

[Rod]

Do you have Word? You could open the file in Word and then 'Save As' a text file to be used in your email. It should be possible to do this automatically and behind the scenes. I have no idea what happens to the indenting, line spacing, etc.

[Rawb]

You need to set .BodyFormat to olFormatRichText

Function sendRTFEmail()
    Const ForReading = 1, ForWriting = 2, ForAppending = 3

    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, "SignOff", acFormatRTF, "C:\Users\john_smith\Desktop\ExampleRTFReport.rtf"

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.OpenTextFile("C:\Users\john_smith\Desktop\ExampleRTFReport.rtf", ForReading)
    RTFBody = f.readall
    f.Close

    With MyItem
        .To = "john.smith@example.com"
        .Subject = "Sign Off email"
        .body = RTFBody
        .BodyFormat = olFormatRichText
    End With

    MyItem.display
End Function