Sending mail from Excel with CDO

Hi all

I update my CDO page today.
Code is working now in Excel 97-2007 or 2000-2007.
http://www.rondebruin.nl/cdo.htm

If you have problems let me know.

Ron de Bruin
http://www.rondebruin.nl/tips.htm

Update:

I can’t post comments to this blog because of the new spam filter on this blog that’s why i do it this way.

I add a GMail example to the example workbook on my CDO page today.
Let me know if it is working for you.

Posted in Uncategorized

33 thoughts on “Sending mail from Excel with CDO

  1. got the same error……
    ‘the sendusing configuration value is invalid’, though i have outlook configured and runningin my pc..

  2. Please read the information on my site
    *****************************************

    It is possible that you get a Send error when you use one of the examples.
    AFAIK : This will happen if you haven’t setup an account in Outlook Express or Windows Mail.
    In that case the system doesn’t know the name of your SMTP server.
    If this happens you can use the commented green lines in each example.
    Don’t forget to fill in the SMTP server name in each code sample where
    it says “Fill in your SMTP server here”

  3. I am using CDO_Mail_Small_Text code in your workbook
    I have filled up sendusing as 2, Smtp server as “smtp.gmail.com” and port as 465
    It gives me an error Run-time error ‘-2147220973(8004123)’
    “The Transport failed to connect to server”

    Regards
    Sam

  4. Hi ,

    I am using CDO_Mail_Small_Text code in your workbook
    I have filled up sendusing as 2, Smtp server as “smtp.gmail.com”
    “The Transport failed to connect to server”
    Please let me know what to do
    Regards
    Srinivas

  5. Ron,
    You need to add the below two lines as well…. else it gives the error I mentioned earlier

    .Item(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “xyz@abc.com”
    .Item(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “abcd”

    Regards
    Sam

  6. I needed to add this line in order to send via gmail SMTP:
    .Item(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True

    I hope this helps others :)

  7. Hi RondeBruin

    Experts exchange pointed me in your direction. Most appreciated. I uncommented the “iconf.load -1 … end with” area, filled in my SMT server (Put in quotes). When I run it, I get a multitude of errors – the latest is

    Run-time error ‘-2147220978(8004020e)’:
    Automation error Cannot modify or delete an object that was added usig the COM+ Admin SDK

    When I comment out the code previously commented out, I get the following:

    Run-time error ‘-2147220960(80040220)’:
    Automation error.

    I turned off norton – no improvement

    Any help you have would be greatly appreciated.

  8. Ron –

    First of all – your code and suggestions have help me a lot. I am trying to use your code to send out an HTML Web Page NewsLetter. The email get’s created and the html file get’s attached but the links no longer work. I have used a sample mailing list program using the same port, SMTP, etc and the web page links work.

    Is there something other than the following that will resolve this?
    Or file on your computer
    .CreateMHTMLBody “file://C:/test.htm”

    Thanks,

    Stapuff

  9. BTW – I am using Yahoo.

    In addition for others – when using Yahoo you need to add the following to Ron’s code:
    .Item(“http://schemas.microsoft.com/cdo/configuration/smtpport”) = 465
    .Item(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True

    Thanks again,

    Stapuff

  10. Hi

    There is a note about the Port number in the example workbook with the code

    ‘If you get this error : The transport failed to connect to the server
    ‘then try to change the SMTP port from 25 to 465

  11. Hi Ron,

    I am using your cdo tamplete it is working fine and i need to send the same in .PDF format.

    If it is possibel please let me know.

    Your help is most appreciated.

    Thanks-Sekhar

  12. Hi,
    I have a strange issue with the script, it works fine from a station and from another station I get the error message -2147220973 “The Transport failed to connect to server”. Could you help me?

  13. Hi Ron,
    thanks for your script, I also get an error message (-2147220973) – could it be that this problem is caused because I am behind a proxy server? Can you give me a hint what to do to bypass the proxy server?
    (for all other tools that have Internet-Access I need to set up some proxy server settings)

    best regards,
    Jürgen

  14. Hello Ron,

    I also get an error Message. I’m using my web.de account.

    Die Nachricht konnte nicht an den SMTP-Server gesendet werden. Der Transportfehlercode lautet 0×800ccc15. Die Serverantwort lautet not available

    I run the code with the following settings:

    .Item(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True
    .Item(“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1
    .Item(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “xxxxxxx@web.de”
    .Item(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “xxxxxxx”
    .Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.web.de”
    .Item(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2
    .Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 25
    .Update

    when I do an ping from my machine to “smpt.web.de” the server is available.

    Can you help me ? Thanks very mucch!

    Regards
    Matthias

  15. Sorry, I only test it with gmail and not with other web mail systems
    There is a gmail example in the workbook that you can download

    Do you have a gmail account ?
    Maybe you can try that first

  16. Here is a Outlook example

    Almost the same with CDO

    Sub Mail_small_Text_Outlook()
    ‘Working in Office 2000-2010
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim MyPath As String, FilesInPath As String
    Dim MyFiles() As String
    Dim Fnum As Long

    ‘Fill in the pathfolder where the files are
    MyPath = “C:UsersRon DesktopTest”

    ‘Add a slash at the end if the user forget it
    If Right(MyPath, 1) “” Then
    MyPath = MyPath & “”
    End If

    ‘If there are no files in the folder exit the sub
    FilesInPath = Dir(MyPath & “*.*”)
    If FilesInPath = “” Then
    MsgBox “No files found”
    Exit Sub
    End If

    ‘Fill the array(myFiles)with the list of files in the folder
    Fnum = 0
    Do While FilesInPath “”
    Fnum = Fnum + 1
    ReDim Preserve MyFiles(1 To Fnum)
    MyFiles(Fnum) = FilesInPath
    FilesInPath = Dir()
    Loop

    Set OutApp = CreateObject(“Outlook.Application”)
    Set OutMail = OutApp.CreateItem(0)

    strbody = “Hi there” & vbNewLine & vbNewLine & _
    “This is line 1? & vbNewLine & _
    “This is line 2? & vbNewLine & _
    “This is line 3? & vbNewLine & _
    “This is line 4?

    On Error Resume Next
    With OutMail
    .To = “ron@debruin.nl”
    .CC = “”
    .BCC = “”
    .Subject = “This is the Subject line”
    .Body = strbody

    If Fnum > 0 Then
    For Fnum = LBound(MyFiles) To UBound(MyFiles)
    .Attachments.Add MyPath & MyFiles(Fnum)
    Next Fnum
    End If

    .Display ‘or use .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
    End Sub

  17. Thanks for the fast reply Ron but I was thinking about your CDO erxample. Something that would not require outlook as I don’t use it.

  18. Thanks Ron for your help. Turnes out it works flawlessly. I would have another question though. How can edit the above to send multiple workbooks from various locations? The workbooks are in the same main folder only in different sub-folders. Also, please excuse my stupidity back there. Should of known better.

  19. Actually, I would like to be able to select multiple files from different locations and then send them as atachments. This option will suit me better if it can be done.

    My problem is that I have a lot of folders with workbooks that contain the same front title but different date.

    For example: Main folder –> Projects –> 2010 –> Month (01 , 02…) –> Project_02-06-2010.xls
    Project_03-06-2010.xls
    …..

    Main folder –> Status –> 2010 –> Month (01, 02 …) –> Status_Defined_02-06-2010.xls
    ….

    This above is an exact folder structure of my main folder. Each day I need to send a predined date. Of these two files. Today for ex. I sent project_09-06-2010.xls and Status_Defined_09-06-2010.xls.
    I’m think something like excel pop-up to select the location (ex. projects or status) and then with an input box to specifiy the date required.
    This would be an alternative to the first.

    Sorry for such a long messange and Thanks Ron for helping. I know you probably receive severeal requests from strangers to help with this or that so I imagine it must be hard. Awaintg a reply.

  20. Maybe this is a option

    Enter the project name in A1

    Dim PName As String
    PName = Sheets(“sheet1?).Range(“A1?).Value

    And use this for example for today’s date
    you can also use a cell for the date and staus or so

    .AddAttachment “C:UsersRon Desktopyourfolder” & PName & “_” & Format(Date, “dd-mm-yyyy”) & “.xls”

    You can repeat this line for the other file

  21. Ron,

    So many thanks for this, I’ve relied on your pages for some time now. Keep up the good work.

    The following code works seamlessly on Windows server 2003 machine running Excel 2010. However, as soon as i move it to another machine (Windows 7 64 bit, Excel 2010 32 bit) I get the “Run-time error ‘-2147220978(8004020e)’:
    Automation error Cannot modify or delete an object that was added usig the COM+ Admin SDK” error when it tries to execute the .send.

    Both machines are in the same domain, connecting to the same Exchange Server

    Do you have any ideas as to where I’ve gone wrong?

    Sub Send_CDO()
        Dim iMsg As Object
        Dim iConf As Object
        Dim strbody As String
        Dim Flds As Variant
        Dim PathName As String
        Dim dname As String
        Dim messagebody As String
        Dim recipient As String
        Dim nostring As String
        Dim sendmailstring As String
        Dim cell As Range
        Dim strto As String

       
        Close #1
        PathName = Worksheets(“Static”).Range(“h2”).Value
        Open PathName + “Purchasing Dashboard.ini” For Input As #1
        Input #1, nostring
        Input #1, nostring
        Input #1, sendmailstring
        If Right(sendmailstring, 1) = “N” Then
            Close #1
            Exit Sub
           
        End If
        Close #1
       
        ‘get email recipients
            If Right(sendmailstring, 1) = “T” Then
                strto = “xxx.xxxx@xxxxxxxxxxx.com”
                Else
             On Error Resume Next
                For Each cell In ThisWorkbook.Sheets(“Recipients”) _
                    .Range(“A1:A100”).Cells.SpecialCells(xlCellTypeConstants)
                    If cell.Value Like “?*@?*.?*” Then
                        strto = strto & cell.Value & “;”
                    End If
                Next cell
                On Error GoTo 0
                If Len(strto) > 0 Then strto = Left(strto, Len(strto) – 1)
            End If

        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”) _
                               = “aus-exch-fe01”
                .Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 25
                .Item(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “xxxx.xxxx@xxxxxxxxxxxxxxxxxx.com”
                .Item(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “xxxxxxxxxxxxxxxxxxxxxx”
                .Update
            End With
        Sheets(“Static”).Select
        PathName = Range(“A1”).Value ‘defines attachment
       dname = Range(“D1”).Value ‘defines date for subject
       Sheets(“Dashboard”).Select
        strbody = Range(“AC15”).Value ‘defines a message for the body of the email

        With iMsg
            Set .Configuration = iConf
            .To = strto
            .CC = “”
            .BCC = “”
            .From = “”“Business Intelligence”” <xxx.xxx@xxxxxxxxxx.com”
            .Subject = “HGI Purchasing Dashboard – “ & Format(dname, “dd-mmm-yy”)
            .TextBody = strbody
            .Send
        End With

    End Sub

  22. Ron you are genius!! you worked on this stuff late 2007!! great man!!

    can anyone tell me how to replace the foll code:
    it should either prmopt for username and pwd

    .Item(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “xxxx.xxxx@xxxxxxxxxxxxxxxxxx.com”
    .Item(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “xxxxxxxxxxxxxxxxxxxxxx”


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *