Emailing a notification on close

[jtm013]

Hello,

I am setting up a database that will have a form used to provide suggestions.
It is a relatively simple form only the following fields are applied:
-Suggestion Name
-Record Number
-Suggestion
-Date Suggested
-Suggested By [whom]

Currently the database is set up so that an employee can open it up, the form pops up right away and after they have filled out the form all they have to do is close access.
----
What I would like, is to set up an OnClose event that would send a notification email to the reviewer letting that person know (at a minimum) that a suggestion has been made, and preferably including the record number, suggestion name, and date suggested in the notification email.
I don't need anything fancy just that it would work. Unfortunately, this is beyond me and the many 'how to automate access to send an email' threads I have found haven't worked for my purposes. Any and all help will be appreciated.


Thanks in advance!

[June7]

Check into the SendObject method.

What exactly have you tried? Was SendObject one attempt? Why did it not work - error message, wrong results, nothing? What exactly don't you understand?

[jtm013]

June7-

Okay so I actually have it working now, but this has lead me to another question.
Below is the code I am using to send the email. (This part is now working fine)

Private Sub notificationemail_Click()

DoCmd.SendObject acSendNoObject, , , "Staticemailrecipient@mycompany.com", , , "New CI Database Suggestion", "Hello, A new suggestion has been made in the CI Database and is now ready for review. This is an automated message please do not reply. Hal2000 signing off.", False


End Sub

However, when this activates I get a popup message which says, "A program is trying to send an email message on your behalf. If this is unexpected, click Deny and verify your antivirus software is up-to-date. For more information about e-mail safety and how you might be able to avoid getting this warning, click Help" It then has an allow, deny, or help button. I need the email to just send without this popup coming up or our employees will be frigging confused when they try to exit and instead get this popup talking about emails.

[June7]

Not sure what will work. Explore:

1. each user sets Outlook to allow programmatic access: File > Options > Trust Center > Trust Center Settings > Programmatic Access > make choice
We have this set to "Warn ..." and don't get warning, maybe because our antivirus software is active and up to date.

2. use CDO (collaboration data object) for email https://www.accessforums.net/program...ens-43311.html
Presumes IT does not block.

[Thompyt]

Try adding this code

Dim strBody As String
strBody = strBody & "Hello," & vBCrlf & "A new suggestion has been made in the CI Database and is now ready for review." & vBCrLf & vBCrLf
strBody = strBody & "Record Number: " !RecordNumber & CHR(9) & !DateSuggested & vBCrLf & vBCrLf
strBody = strBody & "Suggestion: " !Suggestion & vbCrLf
strBody = strBody & !Suggestor & vbCrLf & vbCrLf & vbCrLf
srBody = strBody & "This is an automated message please do not reply." & vBCrLf & Chr(9) & Chr(9) "Hal signing off"
DoCmd.SendObject acSendNoObject, , acFormatTXT,Staticemailrecipient@mycompany.com", , , "New CI Database Suggestion", strBody, True

There is additional coding that needs to be done But this will get you the email formatted as:

To: Staticemailrecipient@mycompany.com

Subject: New CI Database Suggestion

Hello,
A new suggestion has been made in the CI Database and is now ready for review.

Record Number: #### 6 Nov 2014

Suggestion: here is where the person is giving the suggestion

John Q. Public

This is an automated message please do not reply

Hal signing off

[jtm013]

Thompyt-

So I tried the code you provided but when I click the button (I have it attached to an onclick event for testing purposes) I get a syntax error.
The debugger is highlighting the problem as coming from the below red sections.

Private Sub SAVESend_Click()


Dim strBody As String
strBody = strBody & "Hello," & vbCrLf & "A new suggestion has been made in the CI Database and is now ready for review." & vbCrLf & vbCrLf
strBody = strBody & "Record Number: " !RecordNumber & CHR(9) & !DateSuggested & vBCrLf & vBCrLf
strBody = strBody & "Suggestion: " !Suggestion & vbCrLf
strBody = strBody & !Suggestor & vbCrLf & vbCrLf & vbCrLf
srBody = strBody & "This is an automated message please do not reply." & vBCrLf & Chr(9) & Chr(9) "Hal signing off"
DoCmd.SendObject acSendNoObject, , acFormatTXT, "Jmoore@Vermillioninc.com", , , "New CI Database Suggestion", strBody, True


End Sub

[June7]

Missing & for concatenation:

"Record Number:" &

"Suggestion:" &

Chr(9) & "Hal

[Thompyt]

Thanks June7,

Always forgetting something

[jtm013]

I added the extra "&" but now I am getting a "compile error: Invalid or unqualified reference"
When the debugger loads it looks like this.
Private Sub Command235_Click() This line highlighted yellow
Dim strBody As String
strBody = strBody & "Hello," & vbCrLf & "A new suggestion has been made in the CI Database and is now ready for review." & vbCrLf & vbCrLf
strBody = strBody & "Record Number: " & !RecordNumber & Chr(9) & !SuggestedDate & vbCrLf & vbCrLf
strBody = strBody & "Suggestion: " & !Suggestion & vbCrLf
strBody = strBody & "This is an automated message please do not reply." & vBCrLf & Chr(9) & Chr(9) & "Hal signing off"
DoCmd.SendObject acSendNoObject, , acFormatTXT, "Jmoore@Vermillioninc.com", , , "New CI Database Suggestion", strBody, True
End Sub

----
I'm not sure if it matters, but "!RecordNumber" is highlighted when the debugger loads.
I have checked the field names against the table and they all match up. So I am not sure where the invalid reference is coming into play.
Any help would be appreciated as I would really like to be able to include the record #, etc in the email.
--- Update---
I tried directly referencing fields using:

Private Sub Command235_Click()
Dim strBody As String
strBody = strBody & "Hello," & vbCrLf & "A new suggestion has been made in the CI Database and is now ready for review." & vbCrLf & vbCrLf
strBody = strBody & "Record Number: " & Forms!SuggestionForm!RecordNumber & Chr(9) & Forms!SuggestionForm!SuggestedDate & vbCrLf & vbCrLf
strBody = strBody & "Suggestion: " & Forms!SuggetionForm!Suggestion & vbCrLf
strBody = strBody & "This is an automated message please do not reply." & vbCrLf & Chr(9) & Chr(9) & "Hal signing off"
DoCmd.SendObject acSendNoObject, , acFormatTXT, "Jmoore@Vermillioninc.com", , , "New CI Database Suggestion", strBody, True
End Sub

However this returns "Run-time error '2450': Continuous Improvement Database cannot find the referenced form 'SuggestionForm'."
This is confusing me even more because that is the name of the form in question.

---Update 2---

Okay I have taken a new tac based on the boss changing what he wants. New plan is to 'simply' send a pdf of the current form.
So new code is:

Private Sub Command235_Click()


DoCmd.SendObject acSendForm, SuggestionForm, , "Jmoore@Vermillioninc.com", , , "Testing", "Testing 1 2 3", False


End Sub

The problem I am having is that while this works...the pdf it creates includes every which utilized this form. I need it to only utilize the current record.
Any help is greatly appreciated! Thanks

[June7]

Need to send PDF of a filtered report, not form.

Why send pdf if the data can be included in the body of email?

[jtm013]

Need to send PDF of a filtered report, not form.

Why send pdf if the data can be included in the body of email?

Because the powers that be are impatient and I still can't get that information to populate in the body of the form.
See the 1st update on my last post. I keep getting run-time errors saying invalid source and I don't know why. Since the field names are valid. Ugh. I hate access at times. lol Thanks for the continued assistance.

[June7]

Is this a form/subform arrangement? Is button on main form or subform?

If button and code behind the form with the data should simply refer to the fields of the form's RecordSource or to textbox name, like:

Reference to field:
Me!Suggestion

Reference to textboxname:
Me.tbxSuggestion

If button and code are on main form and data is on subform, like:
Reference to field:
Me.subformcontainername!Suggestion

Reference to textboxname:
Me.subformcontainername.Form.tbxSuggestion

If you want to provide db for analysis, follow instructions at bottom of my post.

[jtm013]

Is this a form/subform arrangement? Is button on main form or subform
If button and code behind the form with the data should simply refer to the fields of the form's RecordSource or to textbox name, like:
Reference to field:
Me!Suggestion
Reference to textboxname:
Me.tbxSuggestion
If button and code are on main form and data is on subform, like:
Reference to field:
Me.subformcontainername!Suggestion
Reference to textboxname:
Me.subformcontainername.Form.tbxSuggestion
If you want to provide db for analysis, follow instructions at bottom of my post.

The button is on the main form.
There is no subforms whatsoever.
I just tried the subform arrangement me.subformname!Suggestion, because me!Suggestion wasn't working.

Based on your information I don't see a reason the below (which was my original setup) doesn't work.

Private Sub Command235_Click()
Dim strBody As String
strBody = strBody & "Hello," & vbCrLf & "A new suggestion has been made in the CI Database and is now ready for review." & vbCrLf & vbCrLf
strBody = strBody & "Record Number: " & !RecordNumber & Chr(9) & !SuggestedDate & vbCrLf & vbCrLf
strBody = strBody & "Suggestion: " & !Suggestion & vbCrLf
strBody = strBody & "This is an automated message please do not reply." & vBCrLf & Chr(9) & Chr(9) & "Hal signing off"
DoCmd.SendObject acSendNoObject, , acFormatTXT, "Jmoore@Vermillioninc.com", , , "New CI Database Suggestion", strBody, True
End Sub

However, this results in "Error Unqualified or invalid reference"

If i change it to:

Private Sub Command235_Click()
Dim strBody As String
strBody = strBody & "Hello," & vbCrLf & "A new suggestion has been made in the CI Database and is now ready for review." & vbCrLf & vbCrLf
strBody = strBody & "Record Number: " & Forms!RecordNumber & Chr(9) & Forms!SuggestedDate & vbCrLf & vbCrLf
strBody = strBody & "Suggestion: " & Forms!Suggestion & vbCrLf
strBody = strBody & "This is an automated message please do not reply." & vBCrLf & Chr(9) & Chr(9) & "Hal signing off"
DoCmd.SendObject acSendNoObject, , acFormatTXT, "Jmoore@Vermillioninc.com", , , "New CI Database Suggestion", strBody, True
End Sub

This results in "Compile Error: type mismatch."

[jtm013]

Okay I have attached a sample version of the db for review.
The button in question is the "SAVE" button on the form "Suggestion Form" (It should load automatically upon opening the DB).

Thanks again for the assistance.

[June7]

Neither of your posted code excerpts have proper references to form controls. If you are going to use the bang (!) or dot (.) must have either the actual form name or the Me alias as shown in my post 12 examples. So either add the Me qualifier with each field reference or use:

With Me
'your code
End With

Sorry, I did not pick that up earlier.

Also, your actual field names have spaces in them, which you are not including in your code. Enclose in [].

![Record Number]

Advise not to use spaces or special characters/punctuation (underscore is exception) in naming convention, otherwise must always enclose in [].

The code works after these edits.

Be sure to run Debug > Compile after code edits.