Email Access Report as "Body"

[Dan2kx]

Hello to all,

i have a new interesting problem to solve....
i have a report that i would like to email to a list of users,
the report is a full page document and is personalised to each user (basically) more than one user can have the same email address so i will create a recordset to list all of the users (who need the email) and loop through the users and send to the associated email address, i have a few problems

1)currently the report is set up to list all the reports in the same report.... make sense? so i would need to send only the correct page... is that possible? if not then i will have to alter the reprt to build using the info from my aforementioned loop.

2)the biggest obsticle is that i want the report (sent as html) to be the body of the email, i have tried this:

[code=vb]
DoCmd.SendObjec t acSendReport, "report1", acFormatHTML, EmailAddress, , , "Subject:__ __", "report1"
[/code]

this opens the email to be edited (which i dont want to do) but the report does send as the body this way

so i tried this (to NOT edit)

[code=vb]
DoCmd.SendObjec t acSendReport, "report1", acFormatHTML, EmailAddress, , , "Subject:__ __", "report1", false
[/code]

which does send the email straight away... but sends the report as an attachment... any ideas?

thanks for ya help guys

Dan Out

[Dan2kx]

Any ideas anyone?
Cheers

[maxvernon]

good luck getting the result you want natively from Access. I send data from Access via email all the time for various purposes but have never been able to do it "the way I want" using Access by itself. More recently I have been building an HTML version of the report in code, then sending that as the body of an email using either the Redemption Outlook DLL or a DLL I wrote in VB.NET.

If you like I will put together some sample VB.NET source code for you...

[Dan2kx]

That would be useful if you could, but it would need some explaining as to how i would implement it (and alter it) and i have no experience with VB.net

if you could help then that is great, if not then can i assume that i will just have to accept the report being sent as an attachment?

[maxvernon]

Sending it the way you mention in your first post is the easiest by far. If you have no experience with .Net you are going to be in for more than you expected...

That would be useful if you could, but it would need some explaining as to how i would implement it (and alter it) and i have no experience with VB.net

if you could help then that is great, if not then can i assume that i will just have to accept the report being sent as an attachment?

[maxvernon]

I will work on some sample code for you and get back to you

[maxvernon]

I have just created a page detailing how to create an SMTP DLL which allows you to specify the entire body of the email as SMTP. In order to fully implement your report as the body you will need to do further research into compiling your data into an HTML page.

The SMTP DLL code is at ** Edit - Removed link to external code **

I hope this helps you out to some small degree...

[NeoPa]

Max,

You are welcome to include your code in a post, but linking offsite to code is not allowed.

Admin.

[Denburt]

I almost always use CDO for sending email as you stated I like to send my reports via the email body verses an attachment. Using CDO you may find you have to mess around with the configuration a bit to get it to work but it works and quite nicely I might add. Here is a link to a post with some sample code that may help.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2623601&SiteID=1

To use this you will need to add a reference (in the VBA window under tools/references)

I generally export to HTML like so then read the info and into the email it goes.

[Code=vb]strPath = Application.Cur rentProject.Pat h
DoCmd.OutputTo acOutputReport, "Rate Division", acFormatHTML, strPath & "\RD.HTML", False
Set fso = CreateObject("S cripting.FileSy stemObject")
Set ts = fso.OpenTextFil e(Application.C urrentProject.P ath & "\RD.HTML", ForReading, False) '.CreateTextFil e(vFile)
'Exit Function
tsR = ts.ReadAll
ts.Close
Set ts = Nothing


.HTMLBody = tsR
[/code]


Sometimes exporting a report to HTML can be quite nasty so in those circumstances I XML with an XSL style sheet to produce my desired results then transfer that into the email (again opening it as text then .readall etc.).

Good luck any questions feel free to ask.

[Denburt]

I posted then I saw your post Neo. I know Microsoft informational pages are O.K. but what about the Microsoft forum? The topic is inline and I thought the thread was informative as well. Please edit my post as you see fit. Thanks

[NeoPa]

Denburt,

This is not the same thing at all. Microsoft links are fine. They are not there to advertise their site. They are, after all, the vendor of the software that we're all working in.

What was removed was a link to the poster's own web site where they'd posted the code that should have been posted in here.

I'm glad you asked though as it illustrates the situation more clearly.
In short, no edit required :)