Question CDO Gmail Email Failure

[Dhamdard]

Dear Experts,

We are using MS Access database to send email to a recipient. We have designed it in Visual Basic that is supplemented with any Access application. Each user has to give its gmail email address and password.

Suddenly, some users can't send email now. On clicking the 'send' button, after a while, the database displays this message "The message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response was not available".

But some users can send email. What is the reason for this message and how can it be solved?

Much appreciate your usual help!

Regards,
Hamdard

 

[vbaInet]

I don't think this is a VBA specific error message. I suspect the e-mail server the message is being sent to is being non-responsive or busy or just having problems.

What you should do is get a list of all the users and all the address that they can't send to and see if there's a pattern. Also, get all the users to send to a gmail address that you created to test if they can ALL send messages to that gmail address.

 

[JANR]

Which smtpserverport are you using, default port 25? If so change it to port 587 since Gmail don't accept serverport 25.

JR

 

[Dhamdard]

Thank you for your replies. All users can send and receive emails from Google email web site. I checked their username and passwords and they work from web site. But from the database, some users can't. I used 587 as serverport. Below is the coding:

et cdomsg = CreateObject("CDO.message")
With cdomsg.Configuration.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'NTLM method
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smptserverport") = 587
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = DLookup("[UserEmailAddress]", "User_Tab")
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = DLookup("[UserEmailPassword]", "User_Tab")
.Update
End With
' build email parts
With cdomsg
.To = Forms!RequestForm!Recipient
.From = DLookup("[UserEmailAddress]", "User_Tab")
.subject = "DoNotReply: Vehicle Request"
.TextBody = Forms!RequestForm!EmailText
.Send
End With

 

[Dhamdard]

Thank you for your replies. All users can send and receive emails from Google email web site. I checked their username and passwords and they work from web site. But from the database, some users can't. I used 587 as serverport. Below is the coding:

et cdomsg = CreateObject("CDO.message")
With cdomsg.Configuration.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'NTLM method
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smptserverport") = 587
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = DLookup("[UserEmailAddress]", "User_Tab")
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = DLookup("[UserEmailPassword]", "User_Tab")
.Update
End With
' build email parts
With cdomsg
.To = Forms!RequestForm!Recipient
.From = DLookup("[UserEmailAddress]", "User_Tab")
.subject = "DoNotReply: Vehicle Request"
.TextBody = Forms!RequestForm!EmailText
.Send
End With

 

[vbaInet]

I think you misunderstood my diagnostics advice. What I mean is create a gmail e-mail address and ask them to send mails to that e-mail address from the "database".

 

[Dhamdard]

Thanks, VbaInet. Probably I didn't clarify the issue completely. Each person who uses the database must save his/her gmail email address and password the first thing on the database to be able to use the database.

Now, when I gave my email address and password to database and tried to send an email I could do it normally. Email goes directly to recipients. Many other users can also do it successfully.

Some other users gave their email addresses and passwords the same way as I did but the email doesn't go through. The database displays the above mentioned message after a while.

I apologize if I could still not clear my email. I appreciate your help.

Regards,
Hamdard

 

[vbaInet]

Some other users gave their email addresses and passwords the same way as I did but the email doesn't go through.

So how is this process handled? Does the error pop-up when the user tries to send the message or when the email/password is verified?

 

[Dhamdard]

The message pop ups 4+ seconds after the user clicks on 'send' button. I think the database communicates with server and something goes wrong between the two and the server rejects to send the email.

The confusing part is that it works very well with some other users.

 

[vbaInet]

Can we see the full code that's in the Click event of the button. And also please put it in code tags. Here's how -->

http://www.access-programmers.co.uk/forums/showthread.php?p=1009015#post1009015

 

[Dhamdard]

Thanks, here you go.

Private Sub Command81_Click()
On Error GoTo Command81_Click_Err

Set cdomsg = CreateObject("CDO.message")
With cdomsg.Configuration.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'NTLM method
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smptserverport") = 587
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = DLookup("[UserEmailAddress]", "User_Tab")
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = DLookup("[UserEmailPassword]", "User_Tab")
.Update
End With
' build email parts
With cdomsg
.To = Forms!RequestForm!Recipient
.From = DLookup("[UserEmailAddress]", "User_Tab")
.subject = "DoNotReply: Vehicle Request"
.TextBody = Forms!RequestForm!EmailText
.Send
End With 

If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If


Command81_Click_Exit:
    Exit Sub

Command81_Click_Err:
    MsgBox Error$
    Resume Command81_Click_Exit
End Sub

Regards,
Hamdard

 

[vbaInet]

I think some of your users have changed their passwords in gmail but not changed the password in your database.

Also, I don't understand these lines:

.Item("[URL]http://schemas.microsoft.com/cdo/configuration/sendusername[/URL]") = DLookup("[UserEmailAddress]", "User_Tab")
.Item("[URL]http://schemas.microsoft.com/cdo/configuration/sendpassword[/URL]") = DLookup("[UserEmailPassword]", "User_Tab")
.From = DLookup("[UserEmailAddress]", "User_Tab")

The DLookup() is getting the password and username in the first row of the User_Tab table.

 

[Dhamdard]

DLookUp gets the sender's email address and password to login to gmail server and send the email.

You are right, some users recently changed their passwords. I checked their passwords through signing in into their accounts on internet explorer to check if they enter the right password. I couldn't find any issues with their passwords there. But the database doesn't accept it.

 

[vbaInet]

Ok, I understand what the DLookup() is doing but is the User_Tab table local? I mean is it in the front end or the back end?

Hmmm... I think you should check for Case and trailing/leading spaces.

 

[Dhamdard]

Yeah, the User_Tab table is local. I also searched on Google and the suggestion was that server has blocked that email address because of wrong password and it was considered spam. Don't know if this could be the reason.

I did check for extra space, upper and lower case etc. Couldn't get a clue what is wrong...

 

[Dhamdard]

I just changed my Google account password. After 10 minutes, I signed in to Google mail with my new password on internet explorer. Here is the funny part, when I tried the new password from database, the database gave me the same transport error message with the new password - but the old password still works from database. Maybe its a kind of delay in updating password change in Gmail system. I will try to do it with other colleagues next morning.

Will share my findings with you. All this looks a big fun...

 

[G37Sam]

I used to get a similar error when the daily limit was reached, I then upgraded to an SMTP relay server and everything went well

 

[vbaInet]

I just changed my Google account password. After 10 minutes, I signed in to Google mail with my new password on internet explorer. Here is the funny part, when I tried the new password from database, the database gave me the same transport error message with the new password - but the old password still works from database. Maybe its a kind of delay in updating password change in Gmail system. I will try to do it with other colleagues next morning.

Will share my findings with you. All this looks a big fun...

Sounds like you are having fun Perhaps there is a delay. Who knows!

Keep us posted.

G37Sam has a point!

 

[Dhamdard]

I used to get a similar error when the daily limit was reached, I then upgraded to an SMTP relay server and everything went well

Thanks, Sam. Can you illustrate what you mean by relay server? What would I need to change in the coding to connect to relay server?

Hamdard

 

[Dhamdard]

Google mail is hosting email service for our organization. We use domain of our organization i.e abcd@ndi.org to open our emails on Google server. Probably change in password in our domain doesn't synchronize with Google SMTP server...does it make sense...???