Email current record from button click?

[kbrooks]

I have a simple db for one user who will enter doctor credential informations. When she adds a new one, I need to be notified so I can add it in our main registration system. I've played around with the Send Object macro but have had little luck. I got it to send an email, but it had all my headings and no actual data.

What I would like, is to add a button on the form that she can click to send an email with all the information from the record she is currently on. (Doctor Name, UPIN#, Address, SS#, etc) Do I need to first create a report I want and then use the SendObject macro to send that? That's kind of the route I went but I'm not sure how to create the report for only the current record. I'd also like the info within the email, and not as an attachment, but beggars can't be choosers so I'll take what I can get.

Come to think of it, I think it would also work to send the email when a new record is added, without the need to click a button.

Any help is, as always, much appreciated.

 

[ktariske]

Have you figured this out?

I'm try to do almost the identical thing, and I've read all the post, but none actually explain in detail the steps. So, if you figure this out, please let me know.

Thanks.

Kriss
Redlands, CA

 

[Kevin_S]

Here is how to do it with an attachment:

This example is also based on the assumption that each record in your table has unique ID (Primary Key)

1.Create a query that contains the information you want to send
2.Open the query in design view and enter in the criteria section (under your primary key field) this: forms!YourFormName!YourUniqueFieldName
3.Create a report based on this new query
4.Add a command button to your form (you can also modify this to work with the addition of a new record too)
5.select the command button in design view - go to the properties box and under events select 'On Click'
6. in the 'On Click event put this in the code window:

DoCmd.SendObject acSendReport, "NameofYourReport", "FormatYouWantForReport", "YourEmailAddress", , , "TitleforEmailMessage", "TextofEmailMessage", False

Now - which ever record you are currently viewing in on the form - when the user pushes the button - a report with that record's information will be sent to the e-mail address.

HTH,
kevin

 

[kbrooks]

I thank you for your reply, I think this is going to be just what I need. However, I got a couple errors so I'm not sure if I entered something incorrectly or what.

First, on the query. Under the primary key (ID), I typed in :
forms!PhysicianTable!ID

and when I hit enter, it re-formatted it to read:
[forms]![PhysicianTable]![ID]

When I try to run the query, it's prompting me to enter the above. I thought maybe it was because I wasn't on a form so it could see the current record, so I went on.

Then on the OnClick event of the command button, I copied and pasted in your code and changed the appropriate fields. I ended up with:
DoCmd.SendObject acSendReport, "ToSend", ".txt", "myemailaddress", , ,
"New Physician", "New Physician Attached", False

Does that look right? I know next to nothing about code. When I complete the form and click on the command button, it again prompt for "Enter Parameter Value Forms!PhysicianTable!ID"

Can you see where I went wrong?
Thanks much!

 

[Kevin_S]

can you zip and attach your db So I can take a look?

 

[kbrooks]

Have never zipped before, hopefully this works.

 

[Kevin_S]

I tried to e-mail your db corrected back to you but I wasn't able to get it through our internet security.

You almost had it the right way anyways - to fix the e-mail command button open the query and change the line you have like this:

[Forms]![PhysciansTable]![ID]

To

[Forms]![PhysiciansAdd]![ID]

And it should work! All you had wrong in the setup was that you had the criteria statement looking to the table when what you needed to do was have it look to the form for the ID field

HTH,
Kevin

 

[ktariske]

you rock!

Thanks Kevin, you rock. I've finally got the purchasing dept. infomed per record, awesome. thanks again.

Kriss.

 

[Kevin_S]

No Problem Kriss - Glad I could help...

Take Care,
Kevin

 

[kbrooks]

I love finding things I want it to do are actually possible! Thank you!

Now, another question. Can I put the same code that I put in the OnClick event of my button into the AfterUpdate event of the form? Is THAT how I'd do it without the button click, or am I simplifying it too much? The code I have now is:

DoCmd.SendObject acSendReport, "ToSend", ".txt", "myemailaddress", , ,
"New Physician", "New Physician Attached", False

 

[Kevin_S]

That is the code that you would/could use to trigger the e-mail for a new record however, I'm not entirely sure which event procedure you would use to trigger it. I would play around with "Before Insert" , "After Update" , & "On Lost Focus" to start with. Try puting that snippet of code in those events and she which one does waht you want. You have to make sure that the code fires BEFORE the a new record is added though.

OR

Another way to do it would be to create your own record navigation buttons and then you could control when/where the code fires. If you have the wizard function on all you have to do is click the command button icon in the toolbox and then place it on your form - when the wizard fires up then choose record operations - add a new record. Follow the steps then open the code for the buttons "On Click" event and put the e-mail code in before the goto new record code. (Like This: )

DoCmd.SendObject acSendReport, "ToSend", ".txt", "email", , , "New Physician", "New Physician Attached", False
DoCmd.GoToRecord , , acNewRec

OR

If you will only be adding 1 record and then closing the form each time you could put the code in the "On Close" event...

HTH,
Kevin

 

[rplohocky]

Here is how to do it with an attachment:

This example is also based on the assumption that each record in your table has unique ID (Primary Key)

1.Create a query that contains the information you want to send
2.Open the query in design view and enter in the criteria section (under your primary key field) this: forms!YourFormName!YourUniqueFieldName
3.Create a report based on this new query
4.Add a command button to your form (you can also modify this to work with the addition of a new record too)
5.select the command button in design view - go to the properties box and under events select 'On Click'
6. in the 'On Click event put this in the code window:

DoCmd.SendObject acSendReport, "NameofYourReport", "FormatYouWantForReport", "YourEmailAddress", , , "TitleforEmailMessage", "TextofEmailMessage", False

Now - which ever record you are currently viewing in on the form - when the user pushes the button - a report with that record's information will be sent to the e-mail address.

HTH,
kevin

Kevin,
You wrote this method over 10 years ago. I was wondering if this method will work for Access 2010? I am trying to do exactly what this person was trying to do. The only difference is making this work without using Outlook as the mail handler. Do you have a step by step instructions on how to have a macro fire off an email or alert to one persons email in the body of the message instead of attachments?