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.
got the same error……
‘the sendusing configuration value is invalid’, though i have outlook configured and runningin my pc..
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”
Using Outlook, not express
Using Outlook, not express…
Hi Doco
>Using Outlook, not express…
Then use the commented code and fill in your SMTP server
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
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
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
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 :)
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.
If it is at work ask your IT department if they block it
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
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
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
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
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?
Hi Sekhar
Sorry for the late reply
Yes this is possible, which Excel version do you use
Hi Seref
Follow the steps on the site (use the commented lines)
And be sure that your smtp server is correct
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
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
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
Hi Ron. Any ideea how send mail containing all files in a specific folder (ex: C:wb)?
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
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.
You can use the code from the Outlook example that add the files in your CDO macro
Only CDO use
.AddAttachment
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.
Alex, do you want to mail every file in every sub folder ?
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.
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
Hey,
.Item(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = False ‘keep this true to send mails from gmail
This works fine.
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?
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
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”