Email Notifications not working using Access Runtime (1 Viewer)

[randallst]

Hi Everyone,

Im stumped and hoping someone might be able to point me in the right direction (or tell me it cant be done).

I have created a database to manage Internal Incidents and got a button that sends a generic email to the Investigator and Raiser, using the following VBA;

Private Sub Email_Notification_Button_Click()

'This function emails out an notification email to recipients to flag a Internal Incident has been raised

If MsgBox("Would you like to send an email notification to highlight concern?", vbYesNo + vbQuestion, "Email Notification") = vbYes Then

'send email notification
Dim ccto As String
Dim bccto As String
Dim mailto As String

'Mail Recipients
mailto = DLookup("Email", "Tbl_Users", "Name=" & "Investigation_Lead")
ccto = DLookup("Email", "Tbl_Users", "Alias=" & "Customer")
bccto = ""

'Email Subject
mailsub = "NEW INTERNAL CONCERN: " & Me.Full_ID & ""

'Email Message
emailmsg = "Incident ID: " & Me.Full_ID & vbNewLine & "Category: " & Me.Category & vbNewLine & "Incident Owner: " & DLookup("Name", "Tbl_Users", "Alias=" & "Customer") & vbNewLine & "Product: " & Me.Product & " " & Me.Denomination & vbNewLine & "Title: " & Me.Complaint_Description & vbNewLine & vbNewLine & "Please liaise with the Incident Owner for further information in relation to the above concern."

On Error Resume Next
DoCmd.SendObject acSendNoObject, , , mailto, ccto, bccto, mailsub, emailmsg, False

DoCmd.Save
DoCmd.Close

Else

DoCmd.Save
DoCmd.Close
Exit Sub

End If

End Sub

The majority of users use Access Runtime and not the full version of Microsoft Access. Runtime users are unable to use this button whereas people with the full software are able to use it with no issues.

Is there a workaround / better way to code it, or is it just not possible?

All the best
Stuart

 

[theDBguy]

Hi. When you say "users are unable to use this button," what actually happens? Do they get an error message? If not, you might remove the On Error Resume Next line to see what they might get.

 

[randallst]

Hi. When you say "users are unable to use this button," what actually happens? Do they get an error message? If not, you might remove the On Error Resume Next line to see what they might get.

Hi theDBguy!

Apologies should have explained a bit better haha. They click the button and nothing happens (no errors, no pop ups etc.), you can just keep clicking the button and it continues to do nothing.

I'll remove the On Error now and give it a go quickly

All the best
Stu

 

[theDBguy]

Hi theDBguy!

Apologies should have explained a bit better haha. They click the button and nothing happens (no errors, no pop ups etc.), you can just keep clicking the button and it continues to do nothing.

I'll remove the On Error now and give it a go quickly

All the best
Stu

Hi Stu. Thanks for the additional info. Please let us know how it goes...

 

[randallst]

Removed the On Error line and now i get the following pop up

 

[Isaac]

Are you using an MDE, ACCDR, or ACCDE?

Then this is happening because you have an unhandled error. now you have to take it a step further, and no only removing on error resume next, but also add actual error handling, so you know what is going on.

 

[theDBguy]

Removed the On Error line and now i get the following pop up
View attachment 86311

Hi. I agree with @Isaac. Now that we know there's an error, you can add a proper error handler to get more information about the error.

Quickly looking at your code though, I would question the following lines.

'Mail Recipients
mailto = DLookup("Email", "Tbl_Users", "Name=" & "Investigation_Lead")
ccto = DLookup("Email", "Tbl_Users", "Alias=" & "Customer")

Maybe you meant to do this?

mailto = DLookup("Email", "Tbl_Users", "[Name]='Investigation_Lead'")
ccto = DLookup("Email", "Tbl_Users", "Alias='Customer'")

 

[randallst]

Are you using an MDE, ACCDR, or ACCDE?

Then this is happening because you have an unhandled error. now you have to take it a step further, and no only removing on error resume next, but also add actual error handling, so you know what is going on.

It is an accdb.

I'll have a play with the "DoCmd.SendObject....." section in a mo as need to pop out. Using the full version of access it only errors if I dont send the email (outlook has loaded the message ready to send and i cancel it). On runtime it just displays that error.

 

[Isaac]

It is an accdb.

I'll have a play with the "DoCmd.SendObject....." section in a mo as need to pop out. Using the full version of access it only errors if I dont send the email (outlook has loaded the message ready to send and i cancel it). On runtime it just displays that error.

Hmm. Weird ... For me, 99% of the time if not 100% of the time that message pops up about this application will shut down - it's specifically an unhandled error in a runtime or compiled mode app

 

[randallst]

Hi. I agree with @Isaac. Now that we know there's an error, you can add a proper error handler to get more information about the error.

Quickly looking at your code though, I would question the following lines.


Maybe you meant to do this?

mailto = DLookup("Email", "Tbl_Users", "[Name]='Investigation_Lead'")
ccto = DLookup("Email", "Tbl_Users", "Alias='Customer'")

I've tried eliminating all the different bits i've added. The only bit of code in there now is;

Private Sub Email_Notification_Button_Click()

DoCmd.SendObject acSendNoObject, , , , , , , , False

End Sub

This still returns the same pop up as above.

 

[theDBguy]

I've tried eliminating all the different bits i've added. The only bit of code in there now is;

Private Sub Email_Notification_Button_Click()

DoCmd.SendObject acSendNoObject, , , , , , , , False

End Sub

This still returns the same pop up as above.

What were you expecting to happen with that? By using False, you are telling Access to send the email without previewing it first. However, without a To address, it can't send the email; hence, the error. Try using True or just leave it out. For example:

DoCmd.SendObject acSendNoObject

Just a thought...

 

[randallst]

What were you expecting to happen with that? By using False, you are telling Access to send the email without previewing it first. However, without a To address, it can't send the email; hence, the error. Try using True or just leave it out. For example:

DoCmd.SendObject acSendNoObject

Just a thought...

When i read your message back i did laugh and put my head in my hands ha-ha!

Took your advice and just put

DoCmd.SendObject acSendNoObject

but i still get the same error.

On a computer with full Microsoft Access, it loads an empty email and if i close the email without sending it returns the following;

 

[theDBguy]

When i read your message back i did laugh and put my head in my hands ha-ha!

Took your advice and just put

DoCmd.SendObject acSendNoObject

but i still get the same error.

On a computer with full Microsoft Access, it loads an empty email and if i close the email without sending it returns the following;
View attachment 86318

Hi. Just so we're clear, the DoCmd.SendObject did work and gave you an email message to edit and send, correct?

It's expected to get the 2501 error message if you cancel and don't send the email message generated by SendObject. Again, that's expected.

So, what I'm trying to clarify is you did not get an error just because we used the SendObject method, correct?

If so, then maybe I was close to the problem when I asked about the code to populate the To and Cc lines using DLookup(). If DLookup() fails, you'll get an error.

So, now, let's try those DLookups in the Runtime. Take out the SendObject line, since we think it works.

 

[randallst]

Hi. Just so we're clear, the DoCmd.SendObject did work and gave you an email message to edit and send, correct?

It's expected to get the 2501 error message if you cancel and don't send the email message generated by SendObject. Again, that's expected.

So, what I'm trying to clarify is you did not get an error just because we used the SendObject method, correct?

If so, then maybe I was close to the problem when I asked about the code to populate the To and Cc lines using DLookup(). If DLookup() fails, you'll get an error.

So, now, let's try those DLookups in the Runtime. Take out the SendObject line, since we think it works.

It allowed me to edit and send using the full Microsoft Access with no issues as expected, but when i ran the code on a Runtime version of access i get the "Execution of this application has stopped due to a run-time error"

 

[Isaac]

On a computer with full Microsoft Access, it loads an empty email and if i close the email without sending it returns the following;

Ok, so - this goes back to what I said previously.

On the machine you got the original error on, you weren't running an accdb. You were running an accdr, accde, mde, etc.

 

[randallst]

Ok, so - this goes back to what I said previously.

On the machine you got the original error on, you weren't running an accdb. You were running an accdr, accde, mde, etc.

Just to confirm, where i am looking to confirm this? In the file properties (right click file, properties), its showing as an .accdb file.

 

[randallst]

Ok, so - this goes back to what I said previously.

On the machine you got the original error on, you weren't running an accdb. You were running an accdr, accde, mde, etc.

Now saved as a .accde file and i get the following error when running the code

 

[theDBguy]

It allowed me to edit and send using the full Microsoft Access with no issues as expected, but when i ran the code on a Runtime version of access i get the "Execution of this application has stopped due to a run-time error"

Hi. What we can't see is what happens after the SendObject command is executed. Are you saying you got an email message show up on the screen? Was it an Outlook email message?

Again, did you go back to what I said about removing the SendObject line in your original code and see what happens with the DLookup() lines?

 

[randallst]

Hi. What we can't see is what happens after the SendObject command is executed. Are you saying you got an email message show up on the screen? Was it an Outlook email message?

Again, did you go back to what I said about removing the SendObject line in your original code and see what happens with the DLookup() lines?

Updated and it brings up the same error for both lines

 

[theDBguy]

Updated and it brings up the same error for both lines
View attachment 86321

That's exactly what I was thinking earlier. The DLookup() functions were failing, so the actual problem had nothing to do with sending the email in runtime.

Did you try the replacement code I suggested earlier?