Search code examples
excelvbscriptcdo.message

Error Code: 80040213 Source CDO.Message.1 Failed to connect

This is really only my second VBS script so be gentle... I did **** out anything that was personal or company related I am sure all those fields are correct anyways. The SMTP server is correct I double checked with the provider as that was the number 1 reason i found on other sites. This script will also pull information from a certain cell and paste it into the body... Any help would be greatly appreciated! Also its saying the error is on line 46 which is the "ObjSendMail.Send". Everything works except the emailing portion...

    Dim ObjSendMail
Set ObjSendMail = CreateObject("CDO.Message")
Set objExcel = CreateObject("Excel.Application")
StopDate = DateAdd("d", -1 - Weekday(Date), Date)
StartDate = StopDate-13

Dim xlApp
Dim xlWkb
Dim monthEnd
Set xlApp = CreateObject("excel.application")

Set xlWkb = xlApp.Workbooks.Open("******")
xlWkb.RunAutoMacros 1
xlApp.Run ("UpdateAll")
monthEnd = xlApp.cells(2,7).value
xlApp.ActiveWorkbook.SaveAs strSaveFile & "Monthly Revenue Report " & Year(Now) & "." & Month(Now) & "." & Day(Now) & ".xls", 56 

xlApp.Quit
Set xlWkb = Nothing
Set xlApp = Nothing

WScript.Sleep 10000
mailSubject = "Monhtly Revenue Report " & PrevMonthName
mailBody = "The Monthly Revenue Report is no ready. Month End: " & monthEnd

ObjSendMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
ObjSendMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.office365.com"
ObjSendMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
ObjSendMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
ObjSendMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 240
ObjSendMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
ObjSendMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "********"
ObjSendMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "********"
ObjSendMail.Configuration.Fields.Update

ObjSendMail.To = "*********"
ObjSendMail.Subject = mailSubject
ObjSendMail.From = "*******"
'ObjSendMail.HTMLBody = "this is the body"
ObjSendMail.TextBody = mailBody
ObjSendMail.Send


'Set ObjSendMail = Nothing
Solution

  • When in doubt, read the documentation. Office365 uses the submission port (587/tcp) for mail submission. Replace this:

    ObjSendMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

    with this:

    ObjSendMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 587

    and the error should go away (provided that outbound connections to port 587/tcp are allowed on your network).

    You can test accessibility of the port with a port scanner like nmap, scanline, or PortQry), or manually with telnet:

    telnet smtp.office365.com 587

    Outbound connections to port 25/tcp are most likely blocked by your provider, as a measure to prevent/reduce botnet spam.