Email report as body of email

[chrish20202]

I have searched and googled and searched for several months without finding an answer to my problem.



I want the user to click a button (on the form) that generates an html report and sends an email, through Outlook 2010, with that report being the body of the email not as an attachment.

I'm looking to accomplish this through VBA and I have tried everything that I can think of and still am unable to get it to work.

Any help appreciated. I'm pulling out my hair out over this.

Thanks a lot,
Chris

[June7]

Why doesn't work - error message, wrong results, nothing happens? Post code and/or attach project for analysis.

[chrish20202]

Why doesn't work - error message, wrong results, nothing happens? Post code and/or attach project for analysis.

To clarify, I haven't got any Access 2010 code to show. I was using the below code in Excel 2003 but, in researching, found that this won't work in Access 2010. It works perfect in Excel 2003. Also, to be fair, the code is not my personal work but primarly an example I found googling a few years ago.

I guess my question would be more accurate as: How do I do the below in Access 2010? I can figure out how to create an html file but this part seems to escape me.

Thanks

Excel VBA code:

Sub email()

    Dim FSO As Object
    Dim HTMLcode As String
    Dim HTMLfile As Object
    Dim myOlApp As Object
    Dim myOlMail As Object

    ' Read all data from the TempFile which now in HTML
       Set FSO = CreateObject("Scripting.FileSystemObject")
       Set HTMLfile = FSO.GetFile(sFNmht).OpenAsTextStream(1, -2)
         HTMLcode = HTMLfile.ReadAll
         HTMLfile.Close
         HTMLcode = Replace(HTMLcode, "align=center x:publishsource=", _
                          "align=left x:publishsource=")
         
    ' Start Outlook
       Set myOlApp = CreateObject("Outlook.Application")
    ' Create the mail item and send it.   
       Set myOlMail = myOlApp.CreateItem(0)
        With myOlMail
           .To = "DL-team; DL-SRT"
           .Subject = "Paramedic Telemetry DSEL for " & Format(Sheets("TSEL").Range("B2"), "dd-mmm-yyyy")
           .HTMLBody = HTMLcode
           .Send
        End With
     
    ' Cleanup and Release Objects from memory
       Set myOlMail = Nothing
       Set myOlApp = Nothing
       Set HTMLfile = Nothing
       Set FSO = Nothing

End Sub

[June7]

Everything I read says cannot make an Access report the body of an email. But you say you are generating an HTML report. HTML report would be a bunch of text with HTML tags. Should be able to make this the body of email. Access VBA can send email with HTML tagged text. I guess trick is getting the HTMLCode variable populated. I really don't see why the VBA won't work in Access as well as Excel. Have you tested? Do you want to provide files for analysis?

[chrish20202]

Everything I read says cannot make an Access report the body of an email. But you say you are generating an HTML report. HTML report would be a bunch of text with HTML tags. Should be able to make this the body of email. Access VBA can send email with HTML tagged text. I guess trick is getting the HTMLCode variable populated. I really don't see why the VBA won't work in Access as well as Excel. Have you tested? Do you want to provide files for analysis?

Thanks a lot for the reply.

I have not tried to create an html report but surely access is able to generate a web page??

I'm still searching and trying but not giving up yet! Thanks

[June7]

Access can export to HTML, XML, CSV, Excel, Word, ODBC, dBase, Sharepoint List. There is a wizard for that. At least some of which can be replicated with VBA.

[chrish20202]

Access can export to HTML, XML, CSV, Excel, Word, ODBC, dBase, Sharepoint List. There is a wizard for that. At least some of which can be replicated with VBA.

I think I might be making a little headway. I'm gonna create a sample database and I'll ul it here when i'm done.

thanks for help!