Access Crashes after sending E-Mail

[jco23]

I found several threads on this topic, but could not find an appropriate solution for my issue.

I created a database (frontend/backend) for my team where the frontend is installed on their PC and the backend table is located on a shared network. There can be as many 30 folks in the backend table at one time with about 40 tables (most of which have less than a few hundred rows and less than 20 columns/fields).

The issue that is some folks (some of the time) experience the database crashing after sending an e-mail (event after clicking a checkbox, changing a drop-down menu, entering a figure into a textbox, etc).

Below is a my coding for one of the events (names/fields/messages changed to protect the innocent):


Private Sub Text128_AfterUpdate()

Dim mymsg As String
mymsg = "messageline1" & vbCrLf
mymsg = mymsg & "messageline2" & vbCrLf
mymsg = mymsg & "messageline3" & [text1] & vbCrLf
mymsg = mymsg & "messageline4" & [text2] & vbCrLf
mymsg = mymsg & "messageline5" & [text3] & vbCrLf
mymsg = mymsg & "messageline6" & [text4] & vbCrLf

If Text128 = -1 Then

DoCmd.SendObject acSendNoObject, , , DLookup("", "[table1]", "[field1] = '" & [text5] & "'"), "person1@email.com; person2@email.com; person3@email.com; person4@email.com; person5-lastname@email.com; person6@email.com", , [text6] & "subjectmessage" & [text7], mymsg, True
End If
End Sub

i intentionally left the hyphen in person5's name in case that could be an identifying issue (although, I would HOPE that it shouldn't matter). The crash happens quite frequently, but when the user removes the names and has just one name in the CC: box, then it does NOT crash. The e-mail does get sent though, but obviously, I don't want the database to crash.


below is a second coding from a different form:

Private Sub text1_Click()

If Me!text1 = -1 Then

Me!text2 = Forms![frm1].[user_name]
Me!text2 = Now()
Me!field1 = Forms![frm1].[user_name]
Me!field2 = Now()

Dim message As String
message = "message1"
If MsgBox(message, vbYesNo, "comment") = vbNo Then
Dim strwhere As String
strwhere = "[field3] = """ & Me.[text3] & """"
Debug.Print strwhere
If DLookup("[Email]", "table1", strwhere) <> "NoEmail" Then
DoCmd.SendObject acSendNoObject, , , DLookup("[Email]", "table1", strwhere), , , "message2" & DLookup("[field3]", "table1", strwhere), , True
End If
End If
End If

Exit Sub
ErrorHandler:
DoCmd.CancelEvent
MsgBox Err.Description & " " & Err.Number, vbCritical, "File Export"
End Sub

For this second event, the crash happens about 5% of the time for everyone on the team collectively. The crash is not consistent enough to develop a pattern (not the same field3 name, not the same time of day/month, etc).

any ideas would be greatly appreciated.

thanks!

 

[Ranman256]

2 things, try:

1. instead of exporting all at once, loop thru sending 1 email at a time.
The program doesnt care if it does extra work.
2. dont put dLookups in the send statement, something could go wrong if null.
To prevent null, add an empty string at the end

for i = 1 to NumPersons
vEmail = DLookup("", "[table1]", "[field1] = '" & [text5] & "'") & ""
vMsg = DLookup(...) & ""

DoCmd.SendObject acSendNoObject, , , vEmail, msg, ,
next

 

[jco23]

2 things, try:

1. instead of exporting all at once, loop thru sending 1 email at a time.
The program doesnt care if it does extra work.
2. dont put dLookups in the send statement, something could go wrong if null.
To prevent null, add an empty string at the end

for i = 1 to NumPersons
vEmail = DLookup("", "[table1]", "[field1] = '" & [text5] & "'") & ""
vMsg = DLookup(...) & ""

DoCmd.SendObject acSendNoObject, , , vEmail, msg, ,
next[/QUOTE]

thanks for the quick response.

however, I don't think that will be very efficient to send seven e-mails when one should suffice.
in this case, there are no nulls, but I'll certainly try that to see if it makes a difference. every field does populate correctly on the e-mail, and the e-mail gets sent out just fine.

i did notice, however, that after I decompile the database (which I usually do prior to issuing a version release), and then before sending the e-mail, I press ctrl+K in ther to: or cc: boxes of the e-mail, the database is less likely to crash.

i'm curious to see if there is some sort of communication breakdown between the two programs. I'm thinking that I need some type of cancel event or DoCmd.SetWarnings False.

 

[Ranman256]

the pc doesn't care.

 

[JHB]

...but when the user removes the names and has just one name in the CC: box, then it does NOT crash.

Are you sure all the E-mails/names are correct/found?
Below is a section of Microsoft description of Sendobject, (as far as I understand it, it covers both the To, Cc and Bcc).

...
If the recipient names aren't recognized by the mail application, the message isn't sent and an error occurs.

Here is the link:
https://msdn.microsoft.com/en-us/library/office/ff197046.aspx
So I would suggest, put one name in first and send the mail, then put in another name and send the mail, if both mails got sent, then put in both and send the mail, if no error occur then add another name until you find the problem one. If an error occur, then the way you put them in is not correct.

 

[jco23]

thanks for the response, but I found a solution. with the help of a colleague, I set up a module function for the e-mail logic and used that rather than docmd.sendobject. no more crashes. the key in the module could be better identifying Outlook as the application.