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.
Answer
As you're using Gmail; did you check whether enabling 'less secure apps' made a difference? Support.google.com Reference