Automated e-mail notifications

ericx1 · #1 (**permalink**) 02-10-12, 10:32

Good Morning,

I have an event management program which is installed on, soon to be, 100 computers. There are roughly 300 users total, each having a roaming profile, which must be configured individually to each computer they decide to log into. I have been asked to create an automated e-mail notification system for this software. Everything is using access 2010. The network is locked down pretty tight, you have to have admin rights to install a network printer, if that gives an idea of security.

As far as our e-mail system goes, we are all using outlook with of course an exchange server.

The basic idea of what I am wanting to accomplish is simple. If an event happens and it meets x, y and z critera a, b and c are notified. Here is my quandry. I have code using CDO method form VB for SMTP transfer. The issue, from what I have read and attempted in order to use this you must have the SMTP services running on each individual computer. This will not happen.

Are there any other alternatives to being able to genearte auto e-mail notifications through an exchange server without having to install extra software on 100 computers or alter 300 profiles that could be on as many as 10 computers each?

The research I have done hasn't shown to many more options through VBA so figured I'd post here and see what this great team could offer up.

Thanks for your Time, Thoughts and Knowledge

pbaldy · #2 (**permalink**) 02-10-12, 14:47

I'm no expert, but I've used CDO a few times, and I don't recall ever having to install/run SMTP services. I just looked on a PC that does an automated email with CDO, and there doesn't appear to be anything related to SMTP in the processes running (we also use Outlook/Exchange). Have you tested it and had it fail? If so, what code are you using? I specify the exchange server in mine.

ericx1 · #3 (**permalink**) 02-10-12, 15:15

Well currently, I am attempting to use gmail smtp. Currently our network does not have a SMTP on it, however I believe that will be changing. So, until then I wanted it to link to gmails SMTP.

Code:

Public Sub MailNotification()
Set cdomail = CreateObject("CDO.Message")

With cdomail.Configuration.Fields
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    .Item("http://schemas.microsoft.com/cdo/configuration/_ 
       smtpserverport") = 587
    .Item("http://schemas.microsoft.com/cdo/configuration/_
       smtpserver") = "smtp.gmail.com"
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
    .Item("http://schemas.microsoft.com/cdo/configuration/_
       smtpconnectiontimeout") = 15
    .Item("http://schemas.microsoft.com/cdo/configuration/_
       sendusername") = [userName]
    .Item("http://schemas.microsoft.com/cdo/configuration/_
       sendpassword") = [password]
    .Item("http://schemas.microsoft.com/cdo/configuration/_
       sendemailaddress") = [address]
    .Update
End With

With cdomail
    .Subject = "Subject"
    .To = [To]
    .TextBody = "MessageBody"
    .Send
End With

Set cdomail = Nothing

End Sub

The error I keep getting is the transport failed to connect to the server.

pbaldy · #4 (**permalink**) 02-10-12, 15:41

Sorry, I haven't tried through Gmail.

ericx1 · #5 (**permalink**) 02-13-12, 09:14

No worries, is this code similar to the one you used?

pbaldy · #6 (**permalink**) 02-13-12, 12:18

Similar. Here's mine:

Code:

Dim iCfg As Object
Dim iMsg As Object

  On Error GoTo ErrorHandler

Set iCfg = CreateObject("CDO.Configuration")
Set iMsg = CreateObject("CDO.Message")

With iCfg.Fields
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "10.2.0.121"
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "UserName"
    .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "Password"
    .Item("http://schemas.microsoft.com/cdo/configuration/sendemailaddress") = "PaulB <Name@Domain.com>"
    .Update
End With

With iMsg
    .Configuration = iCfg
    .Subject = "Subject"
    .To = "Name@Domain.com"
    .TextBody = "Blah blah"
    '.AddAttachment "Path to attachment if needed"
    .Send
End With

Set iMsg = Nothing
Set iCfg = Nothing

Internet Services

Web Development

Digital Marketing

Consumer Tech