Avoiding errors when using CDO

[andrewb90]

Hello All,

I am using CDO to email employees schedules to them, and everything works great, until someone accidentally types in an incomplete email address. The red section is where the error pops up. Basically I know with other VBA I can an onError then just have it exit sub, however, I need to just have this error ignored and continue running the code.

Here's the code:

' http://www.blueclaw-db.com/access_email_gmail.htm' http://msdn.microsoft.com/en-us/library/ms872547%28EXCHG.65%29.aspx
' Add CDO reference for early binding method
'  Tools > References > Microsoft CDO for Windows 2000 Library
'    c:\windows\system32\cdosys.dll
' http://www.rondebruin.nl/cdo.htm  'Other cdo tips for cdo to Outlook from Excel


'CDO to gmail requires lowering your security:
'https://myaccount.google.com/security#connectedapps
'at the end set, Allow less secure apps: ON


Function Gmail(sendUsername As String, sendPassword As String, subject As String, _
  textBody As String, sendTo As String, sendFrom As String, _
  Optional sAttachment As String = "")
  
  Dim cdomsg As New CDO.Message  'early binding method
  'set cdomsg=new CDO.Message 'early binding only
  'Dim cdomsg As Object 'late binding method
  Set cdomsg = CreateObject("CDO.message")  'late binding method or early binding
 
  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  '25 or 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") = sendUsername
    .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = sendPassword
    .Update
  End With
  ' build email parts
  With cdomsg
    '.To = sendTo
    .From = sendFrom
    .subject = subject
    .textBody = textBody
    .BCC = sendTo
    '.CC
    '.ReplyTo = sendFrom
    '.HTMLBody
    '.HTMLBodyPart
    If Dir(sAttachment) = "" Then sAttachment = ""
    If sAttachment <> "" Then .AddAttachment (sAttachment)
[COLOR=#ff0000]    .Send[/COLOR]
  End With
  Set cdomsg = Nothing
End Function

Is this doable? any help would be greatly appreciated.

 

[mole999]

to ignore you might want On Error Resume Next, or if the error causes a problem you might need to direct the Code On Error Goto Fix: and do something specific, like maybe log the issue for you somehow and then resume the sending

 

[andrewb90]

So I've tried putting:

OnError:
Resume Next

In several different spots, and I keep getting Run Time Error 20: Resume without error.

Is there a better way to go about this?

 

[Kyle123]

OnError:
Resume Next

Should be:

On Error Resume Next

 

[andrewb90]

thank you!!!

 

[andrewb90]

to ignore you might want On Error Resume Next, or if the error causes a problem you might need to direct the Code On Error Goto Fix: and do something specific, like maybe log the issue for you somehow and then resume the sending

Any suggestions on how I would go about logging any errors that come up? Skipping the errors is always helpful, but seeing the errors after would be very helpful, I'm just not really sure how I would get the process started.

 

[GTO]

Can you post the code you have now with the On Error Resume Next bit included?

 

[andrewb90]

Here's the code:

' http://www.blueclaw-db.com/access_email_gmail.htm
' http://msdn.microsoft.com/en-us/library/ms872547%28EXCHG.65%29.aspx
' Add CDO reference for early binding method
'  Tools > References > Microsoft CDO for Windows 2000 Library
'    c:\windows\system32\cdosys.dll
' http://www.rondebruin.nl/cdo.htm  'Other cdo tips for cdo to Outlook from Excel


'CDO to gmail requires lowering your security:
'https://myaccount.google.com/security#connectedapps
'at the end set, Allow less secure apps: ON


Function Gmail(sendUsername As String, sendPassword As String, subject As String, _
  textBody As String, sendTo As String, sendFrom As String, _
  Optional sAttachment As String = "")
  


  Dim cdomsg As New CDO.Message  'early binding method
  'set cdomsg=new CDO.Message 'early binding only
  'Dim cdomsg As Object 'late binding method
  Set cdomsg = CreateObject("CDO.message")  'late binding method or early binding
 
  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  '25 or 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") = sendUsername
    .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = sendPassword
    .Update
  End With
  ' build email parts


On Error Resume Next
  With cdomsg
    '.To = sendTo
    .From = sendFrom
    .subject = subject
    .textBody = textBody
    .BCC = sendTo
    '.CC
    '.ReplyTo = sendFrom
    '.HTMLBody
    '.HTMLBodyPart
    If Dir(sAttachment) = "" Then sAttachment = ""
    If sAttachment <> "" Then .AddAttachment (sAttachment)
    .Send
  End With
  Set cdomsg = Nothing
End Function

There are other functions and code that are used with it. I'm not entirely sure if you need them as well...But this is the one that I included the on error and it has fixed any potential problems.

 

[GTO]

I will check back later, but in the meantime, I notice that you have .To commented out. That would cause an error, as it means .Send would be sending the email to no one...

 

[andrewb90]

I have all emails sending as BCC. I haven't had any issues with that though.