CDO_Mail_Small_Text Run time error

Louis chiron

New Member
Joined
Mar 10, 2024
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi,

I use the following VBA CDO code to send an email with an attachment to an email address once a week. This has worked fine for several years but this week it returns the following runtime error:
Run-time error '-2147220975 (80040211)':
The message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response was not available.


The error is showing on the ".Send" line of the code

I've searched for causes/solutions which seem to point to a problem with login details but I have checked both username and password for the sending address and they're correct, and the recipients email address is also correct.

The code itself has not been altered so I cant understand what has now caused this error.

Does anybody know what the actual run-time error code means and how to fix it? Thanks

The macro is being run from:
Microsoft Excel for Microsoft 365 MSO (Version 2409 Build 16.0.18025.20030) 64-bit
O/S: Windows 10 Home
Version 22H2



VBA Code:
Sub CDO_Mail_Small_Text()

Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    Dim title As String
        Dim Flds As Variant

 Const myEmailAddress = "xxmy emailxx@hotmail.com"
 Const myEmailPassword = "xxmy passwordxx"
 
   ' Defines Title
 title = Sheet1.Range("c2").Value
 
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

        iConf.Load -1    ' CDO Source Defaults
        Set Flds = iConf.Fields
    With Flds
           .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp-mail.outlook.com"
            .Item("http://schemas.microsoft.com/cdo/configuration/smptserverport") = 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") = myEmailAddress
            .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = myEmailPassword
     
            .Update
        End With



    strbody = "Hi," & vbNewLine & vbNewLine & _
          "Please find attached the wages sheet for w/c " & title & vbNewLine & vbNewLine & vbNewLine & _
          "Kind Regards," & vbNewLine & vbNewLine & _
         

    With iMsg
        Set .Configuration = iConf
        .To = "xxrecipient's emailxx@hotmail.com"
        .CC = ""
        .BCC = ""
        .From = myEmailAddress
        .Subject = "Wages w/c " & title
        .TextBody = strbody
        .AddAttachment ("C:\Users\xxfile locationxx.xlsx")
        .Send
    End With

    Set iMsg = Nothing
    Set iConf = Nothing
    Set Flds = Nothing
End Sub
 
Last edited by a moderator:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Forum statistics

Threads
1,223,885
Messages
6,175,183
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top