Excel VBA CDO Mail

Question

I'm trying to send a mail with Microsoft Office Excel 2007 VBA code but I'm getting the error:

Run-time error '-2147220973 (80040213)':

Automation error

The code that I'm using is:

Dim cdomsg As Object

Set cdomsg = CreateObject("CDO.message")

With cdomsg.Configuration.Fields

  .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
  .Item("http://schemas.microsoft.com/cdo/configuration/smptserverport") = 25
  .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
  ' .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
  .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "excel.**********@gmail.com"
  .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "**********123"
  ' .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
  .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
  .Update

End With

With cdomsg

  .Subject = "Automated mail"
  .From = "excel.**********@gmail.com"
  .To = "**********@hitbts.com" ' https://temp-mail.org/
  .TextBody = "Automated mail"
  .AddAttachment ("*:\*****\***********\****************\***********\*****\*****.xlsm")
  .Send

End With

Set cdomsg = Nothing

I have tried other smpt servers, the server name and address that shows in the cmd when I type in nslookup, the computer's IP and another IP but I don't know what's the correct smpt server.

Edit after answer:

To anyone searching for this in the future, the code I used and worked is the following (taken from this video):

Dim Mail As New Message
Dim Config As Configuration
Set Config = Mail.Configuration

Config(cdoSendUsingMethod) = cdoSendUsingPort
Config(cdoSMTPServer) = "smtp.gmail.com"
Config(cdoSMTPServerPort) = 25
Config(cdoSMTPAuthenticate) = cdoBasic
Config(cdoSMTPUseSSL) = True
Config(cdoSendUserName) = "[email protected]"
Config(cdoSendPassword) = "password123"
Config.Fields.Update

Mail.AddAttachment ("C:\path\file.ext")
Mail.To = "[email protected]"
Mail.From = Config(cdoSendUserName)
Mail.Subject = "Email Subject"
Mail.HTMLBody = "<b>Email Body</b>"

Mail.Send

Make sure to change "[email protected]", "password123", "C:\path\file.ext" and "[email protected]" for the example to work and the subject and body to change the mail.

I also went to the top menu "Tools" on the VBA, option "References...", enabled "Microsoft CDO for Windows 2000 Library" and pressed OK as shown in the video linked above.

Direct link to enable the "Less Secure" option for GMail taken from here.

Solution

As you're using Gmail; did you check whether enabling 'less secure apps' made a difference? Support.google.com Reference

Comments

[Admin]

Take a look at this for good working CDO.Mail code.

[Slaqr]

I see an error in the following line: .Item("http://schemas.microsoft.com/cdo/configuration/smptse‌​rverport") = 25. smptserverport should be smtpserverport

[user7393973]

@Slaqr You're correct but that still didn't fixed the error.

[user7393973]

@Jeeped That code didn't worked. I get the same error without the On Error GoTo.

[Slaqr]

As you're using Gmail; did you check whether enabling 'less secure apps' made a difference? link

[user7393973]

@Slaqr That fixed the issue and the mail was sent, thank you so much! If you can make it the offial answer so it can be accepted.

[user7393973]

The error certanly gives almost no help to solving it. I tried so many different ways and it was always the same. Now I finally know how to send the mail.