Email a report from a form with the push of one button

[djmurphy58]

I would like to export and email a form to single person all with the push of a single button.

It's probably not that important for you to understand the contents of the report - except for the fact that each report has a unique date. To make a long story short, the report is a "daily cost report" for a construction jobsite. The user will input the date, followed by the quantity of work that was done, how many dollars of budget were generated, and the cost that was incurred to do the work on that date.

Based on that input, I currently have a button on the form that, when pushed, views the report based on the information that was input. This report gets generated daily and must be emailed to a single person on a daily basis. So my goal is to add another button to the form that will export and email the report to the person, all with the push of the button. So the necessary code needs to automatically enter the: email address, subject field, and, message field.

My email is currently set-up in Outlook - so that's a good start. I would like the report to be exported and emailed in rich text format (.rtf). If at all possible, I would like the file to be saved to a specific directory on my computer and automatically be given a name that corresponds to the date of the report.

But as for writing the code - well I've never written and VBA code in my life. So I'm wondering if someone could help me out with that.

Hopefully I gave you enough info to go on.

Thanks in advance,
Dennis

 

[Krystoff]

Dennis,

You dont always have to use code. It helps for a lot of things but macros do some things very well.

For instance, there is a command in macro's called Sendobject. Currently I am using it to send a query result as a report every day and it works great. It also supports the use of reports.

Go into macros and select SendObject for the action. The action arguements are pretty self explanatory but if you need help with it, just let me know. I will check back.

Chris

 

[djmurphy58]

Chris-
Damn Chris, you're right! That was incredibly easy. But I'd like one more feature if possible.

Currently the name of the report gets emailed as "DailyCostSheetbyDate.rtf". I would like the macro to first automatically rename the file whatever date the user has entered. For example, if the user is sending out the report from 11/3/03, the name of the file is 110303.rtf

Can that be done with macros? Currently, when I run the report, the user is prompted to input the date as a paramater. So is there a way that the macro could export the file with the date that the user just entered previously, and then email it?

Because right now, when the recipient gets the email, the attachment is labeled "DailyCostSheetbyDate.rtf". I would like the attachment to be 110303.rtf, or 110703.rtf, or whetever the date of the report is.

You know what I mean?


Thanks,
Dennis

 

[Krystoff]

Dennis,

I haven't played around with it too much but I did come across these things.

You can make the subject of the email a date function ie =Date(). Or you can make it a control on a form like =forms!MyForm!DateField

From what I could find, you can not change the name of the object sent that way. There might be other ways using code to do that. When I did a quick search on google about Sendobject Access it gave me a couple of sites that said Sendobject isn't the best way to send email and there are better ways of doing it.

Here is a page that has all sorts of different ways that Access can send emails. Didn't look over the whole page but there might be something in there for that.

http://www.granite.ab.ca/access/email.htm

Anyways, I hope that helps!

Chris