Automatically Send a notification email once a record is added (1 Viewer)

[Levar65]

I am new to Access. Any help would be greatly appreciated.

I have created a helpdesk service request DB.
The user enters the pertinent info into a form. At the button of the form I have placed a command button with an On Click embedded macro that Saves and Closes. I would like to incorporate into that same button the task to automatically send a notification email to our helpdesk email address. I would imagine that the send email command would have to be place in between the SaveRecord and CloseWindow commands in the macro.

I'd like the email to included certain fields from the record that was just entered and saved; ID, Dept, User name, Issue, and Problem Description.

Record table: tblTickets
Record entry form: frmITTickets
Command Button: cbtSaveClose

Thank you in advance for any help you may provide.

 

[pbaldy]

Most of us use VBA rather than macros. There is an action titled "EmailDatabaseObject" that will let you send an email using the user's default mail client. You can refer to form controls in the body.

 

[Levar65]

Thank you for your response.

I tried "EmailDatabaseObject" and set up as follows:
Object type: Form
Object Name: frmITTickets
Output format: Text files
To: email address
Subject: New Help Desk Service Request Ticket

It did create an email but it doesn't automatically send it. It won't send it until the send button is pressed. Also, it attaches a report with ALL records. I only need the record that was just saved to preferably appear in the body of the email and not as an attachment. Is this possible thru a macro?

Is what I'm trying to achieve only possible by using VBA?

 

[pbaldy]

VBA is certainly more powerful and flexible. To your issues, there's an "Edit Message" argument that you need to set to No if you want the email to just go. I'd leave the object type blank since you don't want to send an object (though you could create a report based on a query with a criteria and send that). In the body argument you should be able to include form references like:

"The user was " & Forms!FormName.TextboxName & " and the issue is " & Forms!FormName.OtherTextbox & "..."

 

[Gasman]

I believe you would set up the macro as you appear to have done, then run that macro from one of your other macroes with the RunMacro command and the macro name in question.

 

[theDBguy]

Is what I'm trying to achieve only possible by using VBA?

Hi. Welcome to AWF!

You should be able to use a macro to send an email. But if your needs get too complicated for a macro, you may have to convert them to VBA.

We can't tell you exactly how to fill out all the arguments in your macro, because we can't see your database. Try doing a test email first where you only send out sample text. Once you get that working, you can try to figure out how to send data from your form or table.

 

[Levar65]

Hi. Welcome to AWF!

You should be able to use a macro to send an email. But if your needs get too complicated for a macro, you may have to convert them to VBA.

We can't tell you exactly how to fill out all the arguments in your macro, because we can't see your database. Try doing a test email first where you only send out sample text. Once you get that working, you can try to figure out how to send data from your form or table.

Ok, thank you. The emailing tested ok.

I'd like the email to include, in the body, certain fields from the record that was just entered and saved. I would prefer not to have create a report for just one record. Can the info for each field be grabbed or gathered some other way?

The Fields are: ID, Dept, User name, Issue, and Problem Description.
Record entry form: frmITTickets
Record table: tblTickets

I appreciate your help.

 

[theDBguy]

Can the info for each field be grabbed or gathered some other way?

One way is to use the approach @pbaldy mentioned in post #4. Add a button on your form to execute the macro, so that the form will stay open on the record you want to email.

 

[Levar65]

Thank you

Before adding the email function, I already had a Save and Close Button. I was hoping to incorporate the emailing into the same button so all is done just by one click.

I think your idea of emailing while the record is still on screen is a good idea. For this reason, I have placed the Email action before the SaveRecord action in the macro followed by the CloseWindow.

I added what @pbaldy suggested in the body of the email, to be able to get a few fields included in the email (only 2 for now till I can figure out how to get it to work), but it didn't work. It simply added it as text. What am I doing wrong?

 

[pbaldy]

Just tested, needs an "=", like:

="test " & [Forms]![frmTasks].[TaskDesc] & "more text " & [Forms]![frmTasks].[TaskDate]

 

[Isaac]

Is your back end SQL Server?