2

Running the Excel VBA code below I get the following error:

Run-time error -2147220973
The transport failed to connect to the server

Public Function send_email()

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") = 465
.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") = 10
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "mymail@gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "mypass"
.Update
End With
' build email parts
With cdomsg
.To = "mymail@gmail.com"
.From = "mymail@gmail.com"
.Subject = "the email subject"
.TextBody = "the full message body goes here. you may want to create a variable to hold the text"
.Send
End With
    Set cdomsg = Nothing
End Function
YowE3K
  • 23,852
  • 7
  • 26
  • 40
Ihidan
  • 419
  • 1
  • 6
  • 20

2 Answers2

0

Trouble source

A typo caused this mess - smptserverport should be smtpserverport!

Things I tried

On the way to solve this issue, I tried many things. But now I'm not so sure if all were actually needed. still, someone might gain something from the list:

Few tips:

  • Port 465 should be in use when sending the password in clear text
  • Port 587 should be used when sending a secure password.

if you need to have secure password within VBA/VBS, you can use this little trick with PowerShell (source):

Dim myPassword, cmd, shell, executor, securePassword
myPassword = "ABCABCABC....."
cmd = "powershell.exe ConvertTo-SecureString "& myPassword
Set shell = CreateObject("WScript.Shell")
Set executor = shell.Exec(cmd)
executor.StdIn.Close
securePassword = executor.StdOut.ReadAll
itsho
  • 4,640
  • 3
  • 46
  • 68
-1

Now I am using the bellow code and it works fine. Don't forget to change your gmail config to allow sending email from other apps.

Sub sendEmail(gmail, password, targetEmail, subject, bodyContent)

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

    Config(cdoSendUsingMethod) = cdoSendUsingPort
    Config(cdoSMTPServer) = "smtp.gmail.com"
    Config(cdoSMTPServerPort) = 465
    Config(cdoSMTPAuthenticate) = cdoBasic
    Config(cdoSMTPUseSSL) = True
    Config(cdoSendUserName) = gmail
    Config(cdoSendPassword) = password
    Config.Fields.Update

    Mail.To = targetEmail
    Mail.from = Config(cdoSendUserName)
    Mail.subject = subject
    Mail.HTMLBody = bodyContent

    Mail.Send

End Sub
Ihidan
  • 419
  • 1
  • 6
  • 20