How to include a Microsoft Access report as the message body of an email?

[MDestructive]

First, let me say that VBA is not my strong point but I have been bestowed with creating a duplicate database of a former employee who sabotaged the original. And everyday this isn't working is creating a ton of manual work for other people. I have managed to get the entire database working with the exception of the final step.

Basically, you click three buttons, the first button imports many text files using the Open File Dialog window and shift key, sorts, manipulates and adds needed information to the line items. They are payments to customer accounts.

The final step used to be to click on a button and an Outlook New Mail Message window would pop up with a specific email distribution list in the To: Box, the email body was a nice looking summary (which I know originated from a report in Access and I have duplicated the report) and then automatically attached an excel document with all the data details (which I have also duplicated the excel doc). There was one week where I ran this database for him while he was out of the country and the document it attached would export to my desktop during the initial run of the data and I could delete it after the email was sent.

Long story short, my reports are called rpt_PaymentUploadData1 and rpt_PaymentUploadData2. There was one button for each email. They both go to different distribution lists but if I can figure out how to do one then the other will just be a copy with a few modifications.

I know this is asking a lot but does anyone have an example of something similar to what I speak of? I have searched many forums and do not know enough VBA to make this type of action work. I only understand about 20% of the VBA code the rest might as well be in another language. I have a course in February that should help me going forward but until then I am lost. Any ideas or pity out there on the Interweb?

[gershwyn]

Plenty of pity. Only one idea.

I have sent plenty of reports before as PDF attachments, but never included them in the body of the e-mail. You didn't say which version of Access you're using, but in 2007 at least you can export reports to HTML format.

Can your outgoing e-mails include HTML? If so, the first thing I would check is how your reports look when exported to HTML. I don't think they will include images, and the formatting may not be exactly as it appears on screen, but it may be workable for you.

I can't help with the code for putting the HTML into the body off the top of my head, but if this a route you may want to take, I'd be interested in knowing how to do it myself.

[MDestructive]

Thank you for the reply. I cant believe I forgot to mention I am using Microsoft Access 2003. Yes, I can export the report in a number of formats including html. It doesn't look spectacular but it would be acceptable. I basically just need the missing link to attaching the specific attachment and using the report as the email body. If I find a solution other than here then I will be glad to share it. I can usually figure VBA out but the codes I have found for this do not seem to work. I have to be missing something.

[ADezii]

Here is something that you can try in the meantime:

1. Export the Report, say rpt_PaymentUploadData1, to *.rtf Format.
2. Open the Outlook Window, and place the Cursor in the E-Mail Body.
3. Insert ==> Object ==> Create from File
4. Do NOT Link to File ==> Browse
5. Select the newly created rpt_PaymentUploadData1.rtf File
6. Insert
7. The Report should now exist as an Embedded Object in the Body of the E-Mail.
8. You can also experiment with other Output Formats to see which one produces the best results.
9. I'm not exactly sure how to Code this sequence, but it should not be that difficult using Automation Code.

[MDestructive]

Thanks for your reply ADezii. I am doing something similiar to that in the test database as a tempory fix but I think I like your idea better because it will look just like the automated result (when and if I can get it to work). I never thought of embedding it as an object. Good idea. Thanks!

[ADezii]

You are quite welcome.