Sending E-Mail via CDO.Message

[Porisius]

All,

I have a database that its entire purpose is to grab a file that has test data for a part, parse its test data, calculate lot numbers from the parts used to make the part that was logged in the file, put it in the table, rinse, and repeat. Simple, done, no problem there...

Now, with as many times as it has to run and how long it takes to perform its tasks, (96 Fields per part made, a SQL Script that takes a whole page, literally, welcome to Hell), it bogs down the original database (Rate of about 17 parts per minute) that it cannot allow the user who inputs what parts went where... Simple, done, no problem there... I made a second one with a timer that keeps it going.

IT and the Quality Dept does not want that database (Process Database) to be touched except by certain people. IT Dept, Quality Dept, and of course Me... So, we setup a Windows 7 Enterprise on a Virtual Machine to run this kind of task, since setting it up on an existing server was either too overloading, or they didn't want a user to be logged in all the time. Also, having the database as a service is not recommended by Microsoft. (Reference: support.microsoft.com/default.aspx?scid=kb;en-us;175948)

Now, I brought of the case of what happens if this thing errors out (Lot of potential human errors, but I have no choice), have to restart it, stop it, etc... (Had a long meeting on this) We came up with email system. Company doesn't want to spend the money for a email account in Outlook for an account that may or may not send an email. I am praying for the latter, but hey, this is a just in case scenario.

I did some googling and figured a CDO would work fine.

We have one VBS Script that runs this code with Admin Rights, and it works fine. It doesn't work for me in VBA on my Win7 Box even with adding the Dim. I don't even get an error, message, or the email, and I copied the address and pasted it from a manually sent test email that did work.

        Dim objMyMail as Object ' Not in VBS Script
        Set objMyMail = CreateObject("CDO.Message")
 
        objMyMail.From = "ErrorDB@Work.com"
        objMyMail.To = "MonitorDB@Work.com"
        objMyMail.Subject = "Test Message"
        objMyMail.TextBody = "Test Complete!"
        objMyMail.Send
 
Set objMyMail = Nothing

Then I tried this one... after some googling

    Dim objMsg as Object
    Dim objConfig as Object
    Dim Flds as Variant
 
    Set objMsg = CreateObject("CDO.Message")
    Set objConfig = CreateObject("CDO.Configuration")
    objConfig.Load -1    ' CDO Source Defaults
        Set Flds = objConfig.Fields
        With Flds
            .Item("schemas.microsoft.com/cdo/configuration/sendusing") = 2
            .Item("schemas.microsoft.com/cdo/configuration/smtpserver") = "SMTPServer.Work.com"
            .Item("schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
            .Update
        End With
 
    strbody = "Test"
 
    With objMsg
        Set .Configuration = [COLOR=black]objConfig[/COLOR]
        .To = "MonitorDB@Work.com"
        .CC = ""
        .BCC = ""
        .From = """Database Error"" <[COLOR=black]ErrorDB@Work.com[/COLOR]>"
        .Subject = "Test"
        .TextBody = strbody
        .Send
    End With

Either I get a cannot connect (Server with SMTP), or I get a not authorized error (ExchServer). This has been the most feedback from this project. Our IT here does not have the rights to modify our own exchange server here, because the home site in Europe doesn't want to give control.

My head hurts, and yours probably does too... I know it has to be something dumb and/or simple... Windows 7 no longer has a SMTP Server Service. So, if the Brain Trust here has any ideas, myself and the IT Dept would be most appericated... Btw, all the addresses have proper http, just can't post it with them in there until I get more posts... So, that's not my problem, I think...

Regards,
Chris

 

[AccessMSSQL]

Is the admin user running it from a different machine? You might have different versions of the cdosys.dll?

 

[AccessMSSQL]

couple of threads for you perhaps these will shed some light:

http://social.msdn.microsoft.com/Fo...s/thread/febda45e-a743-451d-9bc3-3650fcffb676

http://www.vbforums.com/showthread.php?644394-RESOLVED-Help-with-Email-CDO-method-on-Windows-7

 

[Porisius]

AccessMSSQL,

I ran this on the virtual machine as a local administrator of that box. I have local admin rights on it and my box ONLY. Even IT here is strict, but with cause, and more often than not, IT is giving me rights when I tell them that I don't need it, but would be nice.

It happens to be a case of PFM. Yesterday, I guess it didn't want to work, and today I logged in, looked the two references you provided, for which I am grateful. I decided for S&G to rerun the code I had, and it worked for me. I didn't change a thing...

One thing I am noticing with this is, no two people have the same results with the same code. For some it works, others it doesn't. Why? I don't know, and I lost enough brain cells from the management here and this problem that I have chosen not to pursue it.

Before closing time yesterday, if anyone is interested. There is an EXE called SendEmail from caspian.dotconf.net written by Brandon Zehm. This is something one guy in our IT Dept found for me, (probably did that so I didn't go bald, I wasted so many hours on this.) so if anyone needs it. There it is, I will be giving the guy $5 on his site, and buying the IT guy another beer.

So, other than expanding my knowledge, AccessMSSQL, I am afraid that it did little for me. Still, I am grateful, and highly appericated. I guess it just needed time, and I needed to fly some model airplanes and then drink a beer (I don't drink and drive, nor do I drink and fly.)

Thanks again, and I hope this clears someone else's headache.

Chris

 

[AccessMSSQL]

Sorry it didn't work for you...sounds like a headache!

 

[Porisius]

I couldn't tell you if it did or didn't, honestly... Like I said, I came in this morning, read what you said, looked it up, ran my code one more time, and it worked... Why? Hell if I know, but I ain't complaining.

Still, thanks anyway... I got the medication for this headache sitting at home, the nectar of the Gods, Beer...