Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in Sending Email via CDO
I stole the following code from Ron De Bruin's website with minor modification.
The SMTP server name "smtp.sbcglobal.yahoo.com" is from my ISP. The SMTP server name is the same as I set up my outlook express outgoing mail and which works fine. When I tried to execute the following code, the got the following error message: ------------------------------------------------ Run-time error '-2147220975(80040211)' The message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response is not available. ------------------------------------------------ Any suggestion to remove the error message? Thanks a lot. Danniel CHEN Houston, TX U.S.A P.S. My outlook express (using same account and SMTP server) works fine. ========= CODE ========= CODE ========= Sub SendEmail() Dim iMsg As Object Dim iConf As Object Dim Flds As Variant Application.ScreenUpdating = False 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.sbcglobal.yahoo.com" ..Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 .Update End With With iMsg Set .Configuration = iConf .To = " .CC = "" .BCC = "" .From = """ABC ZZZ"" " .Subject = "This is a test" .TextBody = "Hi there" '.AddAttachment "C:/" & WBname ' You can add any file you want with this line .AddAttachment "C:/Test.txt" .Send End With Set iMsg = Nothing Set iConf = Nothing Application.ScreenUpdating = True End Sub ================================================== ======= |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in Sending Email via CDO
Hi Danniel
If you have a account in OE you don't need the commented lines in my examples. Try it without them -- Regards Ron de Bruin http://www.rondebruin.nl "Danniel Chen" wrote in message ... I stole the following code from Ron De Bruin's website with minor modification. The SMTP server name "smtp.sbcglobal.yahoo.com" is from my ISP. The SMTP server name is the same as I set up my outlook express outgoing mail and which works fine. When I tried to execute the following code, the got the following error message: ------------------------------------------------ Run-time error '-2147220975(80040211)' The message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response is not available. ------------------------------------------------ Any suggestion to remove the error message? Thanks a lot. Danniel CHEN Houston, TX U.S.A P.S. My outlook express (using same account and SMTP server) works fine. ========= CODE ========= CODE ========= Sub SendEmail() Dim iMsg As Object Dim iConf As Object Dim Flds As Variant Application.ScreenUpdating = False 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.sbcglobal.yahoo.com" .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 .Update End With With iMsg Set .Configuration = iConf .To = " .CC = "" .BCC = "" .From = """ABC ZZZ"" " .Subject = "This is a test" .TextBody = "Hi there" '.AddAttachment "C:/" & WBname ' You can add any file you want with this line .AddAttachment "C:/Test.txt" .Send End With Set iMsg = Nothing Set iConf = Nothing Application.ScreenUpdating = True End Sub ================================================== ======= |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in Sending Email via CDO
I got same error message after commented the lines (Flds).
Is this only for my computer? "Ron de Bruin" wrote: Hi Danniel If you have a account in OE you don't need the commented lines in my examples. Try it without them -- Regards Ron de Bruin http://www.rondebruin.nl "Danniel Chen" wrote in message ... I stole the following code from Ron De Bruin's website with minor modification. The SMTP server name "smtp.sbcglobal.yahoo.com" is from my ISP. The SMTP server name is the same as I set up my outlook express outgoing mail and which works fine. When I tried to execute the following code, the got the following error message: ------------------------------------------------ Run-time error '-2147220975(80040211)' The message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response is not available. ------------------------------------------------ Any suggestion to remove the error message? Thanks a lot. Danniel CHEN Houston, TX U.S.A P.S. My outlook express (using same account and SMTP server) works fine. ========= CODE ========= CODE ========= Sub SendEmail() Dim iMsg As Object Dim iConf As Object Dim Flds As Variant Application.ScreenUpdating = False 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.sbcglobal.yahoo.com" .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 .Update End With With iMsg Set .Configuration = iConf .To = " .CC = "" .BCC = "" .From = """ABC ZZZ"" " .Subject = "This is a test" .TextBody = "Hi there" '.AddAttachment "C:/" & WBname ' You can add any file you want with this line .AddAttachment "C:/Test.txt" .Send End With Set iMsg = Nothing Set iConf = Nothing Application.ScreenUpdating = True End Sub ================================================== ======= |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in Sending Email via CDO
Hi Danniel
Do you use Win 2000 or win XP ? Are you online when you run the sub? -- Regards Ron de Bruin http://www.rondebruin.nl "Danniel Chen" wrote in message ... I got same error message after commented the lines (Flds). Is this only for my computer? "Ron de Bruin" wrote: Hi Danniel If you have a account in OE you don't need the commented lines in my examples. Try it without them -- Regards Ron de Bruin http://www.rondebruin.nl "Danniel Chen" wrote in message ... I stole the following code from Ron De Bruin's website with minor modification. The SMTP server name "smtp.sbcglobal.yahoo.com" is from my ISP. The SMTP server name is the same as I set up my outlook express outgoing mail and which works fine. When I tried to execute the following code, the got the following error message: ------------------------------------------------ Run-time error '-2147220975(80040211)' The message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response is not available. ------------------------------------------------ Any suggestion to remove the error message? Thanks a lot. Danniel CHEN Houston, TX U.S.A P.S. My outlook express (using same account and SMTP server) works fine. ========= CODE ========= CODE ========= Sub SendEmail() Dim iMsg As Object Dim iConf As Object Dim Flds As Variant Application.ScreenUpdating = False 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.sbcglobal.yahoo.com" .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 .Update End With With iMsg Set .Configuration = iConf .To = " .CC = "" .BCC = "" .From = """ABC ZZZ"" " .Subject = "This is a test" .TextBody = "Hi there" '.AddAttachment "C:/" & WBname ' You can add any file you want with this line .AddAttachment "C:/Test.txt" .Send End With Set iMsg = Nothing Set iConf = Nothing Application.ScreenUpdating = True End Sub ================================================== ======= |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in Sending Email via CDO
Ron: I got the same error message after commented the flds lines.
Is this because of my computer? Thanks for help. "Ron de Bruin" wrote: Hi Danniel If you have a account in OE you don't need the commented lines in my examples. Try it without them -- Regards Ron de Bruin http://www.rondebruin.nl "Danniel Chen" wrote in message ... I stole the following code from Ron De Bruin's website with minor modification. The SMTP server name "smtp.sbcglobal.yahoo.com" is from my ISP. The SMTP server name is the same as I set up my outlook express outgoing mail and which works fine. When I tried to execute the following code, the got the following error message: ------------------------------------------------ Run-time error '-2147220975(80040211)' The message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response is not available. ------------------------------------------------ Any suggestion to remove the error message? Thanks a lot. Danniel CHEN Houston, TX U.S.A P.S. My outlook express (using same account and SMTP server) works fine. ========= CODE ========= CODE ========= Sub SendEmail() Dim iMsg As Object Dim iConf As Object Dim Flds As Variant Application.ScreenUpdating = False 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.sbcglobal.yahoo.com" .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 .Update End With With iMsg Set .Configuration = iConf .To = " .CC = "" .BCC = "" .From = """ABC ZZZ"" " .Subject = "This is a test" .TextBody = "Hi there" '.AddAttachment "C:/" & WBname ' You can add any file you want with this line .AddAttachment "C:/Test.txt" .Send End With Set iMsg = Nothing Set iConf = Nothing Application.ScreenUpdating = True End Sub ================================================== ======= |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in Sending Email via CDO
Ron:
I am using XP and i am online always. Thanks. "Ron de Bruin" wrote: Hi Danniel Do you use Win 2000 or win XP ? Are you online when you run the sub? -- Regards Ron de Bruin http://www.rondebruin.nl "Danniel Chen" wrote in message ... I got same error message after commented the lines (Flds). Is this only for my computer? "Ron de Bruin" wrote: Hi Danniel If you have a account in OE you don't need the commented lines in my examples. Try it without them -- Regards Ron de Bruin http://www.rondebruin.nl "Danniel Chen" wrote in message ... I stole the following code from Ron De Bruin's website with minor modification. The SMTP server name "smtp.sbcglobal.yahoo.com" is from my ISP. The SMTP server name is the same as I set up my outlook express outgoing mail and which works fine. When I tried to execute the following code, the got the following error message: ------------------------------------------------ Run-time error '-2147220975(80040211)' The message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response is not available. ------------------------------------------------ Any suggestion to remove the error message? Thanks a lot. Danniel CHEN Houston, TX U.S.A P.S. My outlook express (using same account and SMTP server) works fine. ========= CODE ========= CODE ========= Sub SendEmail() Dim iMsg As Object Dim iConf As Object Dim Flds As Variant Application.ScreenUpdating = False 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.sbcglobal.yahoo.com" .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 .Update End With With iMsg Set .Configuration = iConf .To = " .CC = "" .BCC = "" .From = """ABC ZZZ"" " .Subject = "This is a test" .TextBody = "Hi there" '.AddAttachment "C:/" & WBname ' You can add any file you want with this line .AddAttachment "C:/Test.txt" .Send End With Set iMsg = Nothing Set iConf = Nothing Application.ScreenUpdating = True End Sub ================================================== ======= |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in Sending Email via CDO
Hi Danniel
Maybe your IT department block this ?? Show me the macro you are using. Maybe I see something? -- Regards Ron de Bruin http://www.rondebruin.nl "Danniel Chen" wrote in message ... Ron: I am using XP and i am online always. Thanks. "Ron de Bruin" wrote: Hi Danniel Do you use Win 2000 or win XP ? Are you online when you run the sub? -- Regards Ron de Bruin http://www.rondebruin.nl "Danniel Chen" wrote in message ... I got same error message after commented the lines (Flds). Is this only for my computer? "Ron de Bruin" wrote: Hi Danniel If you have a account in OE you don't need the commented lines in my examples. Try it without them -- Regards Ron de Bruin http://www.rondebruin.nl "Danniel Chen" wrote in message ... I stole the following code from Ron De Bruin's website with minor modification. The SMTP server name "smtp.sbcglobal.yahoo.com" is from my ISP. The SMTP server name is the same as I set up my outlook express outgoing mail and which works fine. When I tried to execute the following code, the got the following error message: ------------------------------------------------ Run-time error '-2147220975(80040211)' The message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response is not available. ------------------------------------------------ Any suggestion to remove the error message? Thanks a lot. Danniel CHEN Houston, TX U.S.A P.S. My outlook express (using same account and SMTP server) works fine. ========= CODE ========= CODE ========= Sub SendEmail() Dim iMsg As Object Dim iConf As Object Dim Flds As Variant Application.ScreenUpdating = False 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.sbcglobal.yahoo.com" .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 .Update End With With iMsg Set .Configuration = iConf .To = " .CC = "" .BCC = "" .From = """ABC ZZZ"" " .Subject = "This is a test" .TextBody = "Hi there" '.AddAttachment "C:/" & WBname ' You can add any file you want with this line .AddAttachment "C:/Test.txt" .Send End With Set iMsg = Nothing Set iConf = Nothing Application.ScreenUpdating = True End Sub ================================================== ======= |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in Sending Email via CDO
Hi, I have tried two differents macro to send email via CDO in order not t have the Outlook confirmation button, but I have an error while runnin both here are the codes and error messages : -Sub envoimail() ' Reference to Microsoft ActiveX Data Objects 2.5 Library ' Reference to Microsoft CDO for Windows 2000 Library Dim iConf As New CDO.Configuration Dim Flds As ADODB.Fields Set Flds = iConf.Fields Flds(cdoSendUsingMethod) = cdoSendUsingPort ' 2 Flds(cdoSMTPServer) = "mailvel.velizy.fr.sterianet" Flds(cdoSMTPServerPort) = 25 Flds(cdoSMTPAccountName) = "mylogin" Flds(cdoSMTPAuthenticate) = cdoBasic ' 1 Flds(cdoSendUserName) = "mylogin" Flds(CdoSendPassword) = "mypassword" Flds(cdoSendEmailAddress) = """Michaël Sochon" " Flds(cdoSMTPUseSSL) = True Flds.Update Dim iMsg As New CDO.Message Set iMsg.Configuration = iConf With iMsg .To = " .CC = " .BCC = "" .From = """Michaël Sochon"" " .Subject = "This is a test" .TextBody = "This is the body text" .AddAttachment "D:\Mes documents\Steria\Manpower\Stat réléphonie\suivi_global.xls " End With iMsg.Send Set iMsg = Nothing Set iConf = Nothing End Sub- There the error message is : Transport failed while connecting t server. the other macro is : -Sub envoimailbis() Dim iMsg As Object Dim iConf As Object Application.ScreenUpdating = False Set iMsg = CreateObject("CDO.Message") Set iConf = CreateObject("CDO.Configuration") With iMsg Set .Configuration = iConf .To = " .CC = " .BCC = "" .From = """Michaël Sochon"" " .Subject = "This is a test" .TextBody = "This is the body text" .AddAttachment "D:\Mes documents\Steria\Manpower\Stat réléphonie\suivi_global.xls " .Send End With Set iMsg = Nothing Set iConf = Nothing Set wb = Nothing Application.ScreenUpdating = True End Sub- And the error msg is : Configuration value "sendusing" is invalid Can anyone help about this... -- mikem ----------------------------------------------------------------------- mikems's Profile: http://www.excelforum.com/member.php...fo&userid=1522 View this thread: http://www.excelforum.com/showthread.php?threadid=26850 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in Sending Email via CDO
Daniel, I copied/pasted your code and it worked for me. If anyone has the answers to my question, that still would be nice, bu at least thanx to Daniel I found a way out of this : -- mikem ----------------------------------------------------------------------- mikems's Profile: http://www.excelforum.com/member.php...fo&userid=1522 View this thread: http://www.excelforum.com/showthread.php?threadid=26850 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in Sending Email via CDO
i think you dont have the following line
at the top of your code... OPTION EXPLICIT put it there.. then try to Debug / Compile all the lines inside your WITH/ENDWITH clauses are missing . before the property. thus they set variables and NOT properties of the object With iMsg To = " CC = " must be: With iMsg .To = " .CC = " IF this is the problem and not due to copy/paste editing. then I'd advise you to check following option in the VB Editor: Tools/Options Require Variable declaration (which ensures that all new modules include the option explicit line keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool mikems wrote: Hi, I have tried two differents macro to send email via CDO in order not to have the Outlook confirmation button, but I have an error while running both here are the codes and error messages : -Sub envoimail() ' Reference to Microsoft ActiveX Data Objects 2.5 Library ' Reference to Microsoft CDO for Windows 2000 Library Dim iConf As New CDO.Configuration Dim Flds As ADODB.Fields Set Flds = iConf.Fields Flds(cdoSendUsingMethod) = cdoSendUsingPort ' 2 Dim iMsg As New CDO.Message Set iMsg.Configuration = iConf With iMsg To = " CC = " Can anyone help about this...? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting #name? error in cells after sending spreadsheet via email | Excel Worksheet Functions | |||
Sending a Spreadsheet as an Email Attachment vs. Imbedded in Email | Excel Discussion (Misc queries) | |||
error sending email (as attachment) from within Excel 2000 | Excel Discussion (Misc queries) | |||
Email sending | Excel Discussion (Misc queries) | |||
Sending Email using CDO ERROR | Excel Programming |