Sending email using exchange server and distribution lists

[Inline]

Hello all,

I have been working on a database that tracks all new hires in our company and sends out emails every Friday morning. To ensure this email goes out consistently, we want to remove outlook from the equation and leave it up to the server running our MS exchange. I would like to have access reach out to our exchange server automatically (without user input) send an email to selected users. I have seen examples and have been searching for a few days now and have yet to find an example that really suits my needs. Any assistance would be MUCH appreciated!

[ItsMe]

I don't have any experience managing Exchange server. What is it you expect Exchange Server to do for you? Do you need a log of ins and outs?

[rpeare]

I don't think you want to have access involved the email equation.

You want an automated email to go out to any new employees for the last 7 days. If your data is stored in access you probably want a task running on your server that will process the 'new' employees from your table and generate individual emails for each person. One of the issues with having access do this is, let's say you want access to determine if it's 5:00PM on friday, you have to set up a routine that will run constantly to check the time/weekday against your target so let's say you check every 20 seconds, while the database is open it's going to check the date/time every 20 seconds to see if it's friday yet. This is just a really inefficient use of computer resources.

Here's a blog post showing a few different methods:

http://www.fmsinc.com/MicrosoftAcces...idDoEvents.asp

I've never tried it so I don't know if it's possible to trigger a task on a MS exchange server from MS Access with VBA code. All the code I've ever seen for emailing in MS access has dealt with outlook.

Have you considered an easy to use option like Powershell to cycle through your new records and send out emails?

[Inline]

I got it working! For anyone who may have the same question in the future, this worked, but the emails were getting sent to the recipient's junk email folder, so I didn't think it was working.

Public Sub VerySimpleSendMailWithCDOSample()

Dim iCfg As Object
Dim iMsg As Object

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") ="smtp.server.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")= 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") ="MyUserName"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") ="MyPassordNotAlwaysNeeded"
.Item("http://schemas.microsoft.com/cdo/configuration/sendemailaddress")= "email@address.com.au"
.Update
End With
.Update
End With

With iMsg
.Configuration = iCfg
.Subject = "Subject"
.To = "recipient.email@address.com.au"
.TextBody = "MessageBody"
.AddAttachment "FullPathToAttachment"
.Send
End With

Set iMsg = Nothing
Set iCfg = Nothing

End Sub

-Credit to StarTrekker @ dbforums

And to clarify my concern was sending an email with a report without using outlook and reaching out to the exchange server directly. This way we can have the task scheduler run on the server hosting the exchange (running 24/7), open the db to an MDE that runs code to output the report to a .pdf and the code then emails the report before closing the database.

[pbaldy]

I've used CDO several times, and it worked well. One thought I'd offer if relevant is that since the email is bypassing Outlook, it doesn't show up in your Sent Items (at least it didn't for me). I blind copied myself and moved it to a sub-folder by rule so that I had a record of the email going out.

[rpeare]

hah, I thought the issue was automating the timing, not actually creating the email.

[Inline]

I've used CDO several times, and it worked well. One thought I'd offer if relevant is that since the email is bypassing Outlook, it doesn't show up in your Sent Items (at least it didn't for me). I blind copied myself and moved it to a sub-folder by rule so that I had a record of the email going out.

Thanks for the tip!

hah, I thought the issue was automating the timing, not actually creating the email.

Yeah, we have the timing being managed by windows task scheduler, thanks though!