Using CDO for email

[imaquila]

I've found an example of the code but it triggers an error. "The "SendUsing" configuration value is invalid."
Since I did a copy and paste of the code changing on the the variable for the address to send to. I'm guessing that I do not have something installed which is needed to use CDO. I found and selected "Microsoft CDO for Win 2000" from my reference library (I have Win 7 but I thought it might work) but I got the same error. Maybe I need a line of code to link my project to the reference library. I'm not sure.

If I do need something else to use CDO what would it be?

My thanks to anyone who can help.

 

[xenou]

Note:
As a follow-up to my just posted example, I also found another good example here. You may want to follow it (carefully!) and see if it works out better for you:
http://www.databison.com/index.php/3-nifty-ways-to-send-email-using-excel/

 

[imaquila]

Hi Xenou,
Thank you so much for all the time you've obviously put into trying to help me to solve this mystery. I've listed the steps I followed below.

• Copied and pasted your code in to a new module of a new project

• Changed the line :

.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 ( to "= 1" since I do NOT have Outlook installed)

• Selected the Reference to the Microsoft CDO Library for Windows 2000and moved it up the list to just above OLE Automation.

• I Saved the project and used the Debugging Tool "Step Into" which went great right up to the line:

        .Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 1

At this point it jumped to error handling routine and displayed "Class not Registered".

• Out of curiosity I change the code on that line back to "= 2" but it executed in exactly the same manner.

I wonder if having some form of Outook installed is required to use CDO and then you have the choice to use Outlook or not. I don't know what else to think at this point.

I'll try the databison example next but at this point I'm not very hopeful. All the same, I'm VERY grateful for all your time and efforts! THANK YOU

 

[xenou]

I'm not sure. It seems odd that you can create a message but not a configuration. I would continue to advise you paste more of your code because it's hard to see what you are really doing (I cannot believe you are actually using my code as posted since that would no doubt fail, as posted, without certain edits to meet your situation).

It is possible that you need the MS Mapi client installed. This is usually present on MS desktops, though it could have been uninstalled or left out intentionally in your environment. Mapi is not necessarily on some of the more recent MS server installs. How do you send emails on your machine?

 

[imaquila]

I checked the settings for Thunderbird (my default email app) and the server address uses smtp. I'm definately not proficient in vba, or email but it seems like if the problem was related to setting the smtp server address THAT would be the line to jump to the error handling. Likewise, I had never use "[at]" for an "@" but I reasoned that if that were a problem the error handling would take over at the ".send" line.

Function SendEMailCDO()
Dim objMessage As CDO.Message

    On Error GoTo ErrHandler:
    
    Set objMessage = New CDO.Message
    With objMessage
        
        .Subject = "Test [" & Now & "]"
        .From = "fromemail [at] frontier.com"
        .To = "toemail [at] frontier.com"
        .TextBody = "This is a Test [" & Now & "]"
        
        .Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 1
        .Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.frontier.com"
        .Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        .Configuration.Fields.Update
                       
        .send
    
    End With
        
My_Exit:
Set objMessage = Nothing
Exit Function

ErrHandler:
MsgBox Err.Description
Resume My_Exit

End Function

 

[xenou]

If you don't have outlook it may not work. Outlook and Outlook express use MAPI as the underlying messaging interface. Thunderbird (apparently) uses only a subset of MAPI. I don't use Thunderbird so I'm not much help there. If you don't use outlook you may or may not have the MAPI client installed. I guess there's probably someway to find out - offhand I'm not sure though. You could try googling "do I have mapi installed".

As far as errors go, bad email addresses, non-existent servers, and so on would probably not produce an error at runtime. Your email would just not get out, or not get to its address. You *do* need to use valid email addresses. The ones I put in are not valid email addresses - the intention there was to keep them away from spiders that harvest emails from websites.

What kind of system are you using (version of windows)?

 

[imaquila]

Below is using the Bison Data example. I changed the usual sendusing line to a "1" and the smtp address even though the code never executes that far. I didn't bother changing the email addresses since it never actually sends anyway. The same "Class not registered" error is triggered on the line:
Set SMTP_Config = CDO_Config.Fields

Sub Send_Email_Using_CDO()
Dim CDO_Mail_Object As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim Email_Subject, Email_Send_From, Email_Send_To, Email_Cc, Email_Bcc, Email_Body As String

Email_Subject = "Trying to send email using CDO"
Email_Send_From = "databison@gmail.com"
Email_Send_To = "databison@gmail.com"
Email_Cc = "databison@gmail.com"
Email_Bcc = "databison@gmail.com"
Email_Body = "Congratulations!!!! You have successfully sent an e-mail using CDO !!!!"
Set CDO_Mail_Object = CreateObject("CDO.Message")
On Error GoTo debugs
Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1
Set SMTP_Config = CDO_Config.Fields    'CLASS NOT REGISTERED ERROR AT THIS LINE
With SMTP_Config
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 1
'please put your server name below
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.frontier.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Update
End With
With CDO_Mail_Object
Set .Configuration = CDO_Config
End With
CDO_Mail_Object.Subject = Email_Subject
CDO_Mail_Object.From = Email_Send_From
CDO_Mail_Object.To = Email_Send_To
CDO_Mail_Object.TextBody = Email_Body
CDO_Mail_Object.cc = Email_Cc 'Use if needed
CDO_Mail_Object.BCC = Email_Bcc 'Use if needed
'CDO_Mail_Object.AddAttachment FileToAttach 'Use if needed
CDO_Mail_Object.send
debugs:
If Err.Description <> "" Then MsgBox Err.Description
End Sub

 

[imaquila]

I have Win7

 

[xenou]

This might help you to figure out if you have mapi installed or not:
http://stackoverflow.com/questions/3403708/detect-if-exchange-mapi-is-installed

This link suggests you should be sure that Thunderbird is set up as your default email client:
http://www.nch.com.au/kb/10018.html

Note that your code works for me. But I used 2 for "sendusing", not 1. And I used my own from and to email addresses, of course, and local smtp server. What's frontier.com? It's very odd if you can create a cdo config object but can't access it's fields. Maybe you're just hosed here when it comes to CDO.

 

[imaquila]

I checked the link and I'm not certain but I think it's suggesting that I check my registry. (something I've done maybe twice ever and NEVER with win 7) Not even sure how to. I think you were correct in that the CDO idea is hosed (considering what I have to work with). It looks like MAPI has had a lot of problems with win 7.

I know for certain that Thunderbird is the default email app because ShellExecute DOES work and that's the app that comes up. Sadly, ShellExecute will only let me pull about 12 records from Excel and any remaining records are truncated. That was why I was exploring the idea of using CDO in the first place.

Thanks again for all your time and effort to help. If there were a way, I have no doubt that you would have found it. Thanks

 

[xenou]

You can also look for the mapi file directly. It probably should be found at C:\Windows\System32\mapi.dll But I expect you have it. Sorry, I'm not sure what to do. Looks like were beaten I guess. Another outside chance is that there's a mismatch between 32 bit and 64 bit programs (maybe your thunderbird is 64 bit and that's a problem here).


How is gmail involved here as the 'To' and is frontier.com an ISP or a company you work for? What are you actually doing with the Excel part of this - is that the content of the email? How does it go over the limit for shell?


Note: for the record, I don't have any email client at all on my machine at home (I use a web-based email client at home) - I never installed Outlook and uninstalled Outlook Express when I first set up this machine.