VBA Send Email using DoCmd.SendObject

[malamute20]

I'm having trouble getting this to work as it should, and maybe there's a better way I don't know about. I'm trying to write a VBA script to send an email out when a "Submit" command box is clicked on a form. I have this in the OnClick event.

Private Sub Submit_Click()
DoCmd.SendObject acSendForm, Me.[Entry Log], acFormatXLSX, "various.company_personnel@company.com", "", "", "Incident Report", "", True
 
End Sub

Help is very welcome.

[pbaldy]

Welcome to the site. It usually helps us if you describe the problem. "I'm having trouble getting this to work as it should" doesn't tell us what the problem is. Are you getting an error? If so, what is it? Are you getting an unexpected result?

[malamute20]

Yes, sorry.

RunTime error '2465': Microsoft Access cannot find the field '|1' referred to in your expression.

Then I get an opportunity to debug and the entire line of code is highlighted.

I'm running MSA 2010

[pbaldy]

What does Me.[Entry Log] contain? Access is expecting the name of a form, since that's what you specified you were sending.

[malamute20]

Entry Log is the name of the form. The form contains various fields to be filled out prior to submitting.

[pbaldy]

Access is trying to find a control (textbox, etc) on the form named [Entry Log]. Try Me.Name or "[Entry Log]"

[malamute20]

Ok. Using "[Entry Log]" I now get:

Run-time error '2102':

The form name '[Entry Log]' is misspelled (it's not) or refers to a form that doesn't exist. (It is the active form.)

An option to debug is given, it still highlights the entire string of script.

[pbaldy]

Try without the brackets. The space in the name isn't a good idea; normally it requires brackets, but not always. This just worked in a test:

DoCmd.SendObject acSendForm, "FormName", acFormatXLS, "", "", "", "Incident Report", "", True

[malamute20]

Thank you, that works.

One little thing: is there any way to prevent an error from popping when I close out the email without sending? As it is it pops up with an error indicating the "SendObject action was cancelled." And prompts for the debugger.

[pbaldy]

You have to trap for the error (I think it's 2501). Here's a primer on that if you're not familiar:

http://www.baldyweb.com/ErrorTrap.htm

In this case, you probably wouldn't want a message box for that error.

[malamute20]

Sweet. Marking solved- Thanks a bunch.

[pbaldy]

No problem, glad we got it sorted out for you.

[Tomfernandez1]

The information on this thread is great! As a follow-up, say, I filtered a form that results in 4 records and 4 different email addresses. How do I adjust the code to show all 4 email address in the "To:" section of the email message?

Tommy

[pbaldy]

You could open a recordset on the recordset clone of the form, though I'm not sure how that would work if you had a applied a filter or something. You might need to open the recordset on the source of the form using the same criteria as the filter. Then you loop through the recordset and build a string of delimited email addresses.

[Tomfernandez1]

I am not sure how to open a recordset. Where do I start this process? What information do you need to help me get started?

Thanks a bunch!!

Tommy