Sending Email via CDO triggered by a date event in Microsoft Access

I have searched several forums over the last week and have found several references to using the CDO method. I have not been able to find one instance of it being applied to a Access database which i could rework to my particular application. I have experimented with the sendobject but it requires user intervention and i needed something i can setup to run every night using windows scheduler without any user intervention.

I have a Access database which tracks office communication, on receipt of a inquiry the letter is assigned to a particular department with an supervisor and analyst who are responsible to research the required information and provide a respond by a particular due date. if the due date is between Now () and Now + 5 then send email to Head of communication and their assistant also cc department supervisor and analyst. When email is sent update field on record with date of notification.

in my current environment there is a Microsoft Exchange server which i do not have access to, also an SMTP server on port 25 which we use to send email to users with various other applications.    

I have created a couple of databases but my skill level when it comes to adapting vb code or functions to accomplish various tasks in access is low.
Blue_techAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Barry CunneyCommented:
Ensure the the CDO dll is on the machine on which Access is being used.
Then in Access VBA add a reference to the CDO dll
Then you would do something like this in a VBA function

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") = "mail.*.si"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "*@*.si"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "*****"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendemailaddress") = """comp d.o.o."" <*@*.si>"
        .Update
    End With
   
    With iMsg
        If Not Nz(strReceiptTo, "") = "" Then
            ' Request read receipt
            .Fields("urn:schemas:mailheader:return-receipt-to") = strReceiptTo
            .Fields("urn:schemas:mailheader:disposition-notification-to") = strReceiptTo
            ' Update fields
            .Fields.Update
        End If
       
        .Configuration = iCfg
        .Subject = zadeva
        .To = za
        .cc = cc
        .Bcc = Bcc
        '.HTMLBody = besedilo
        .TextBody = besedilo
        .AddAttachment attach
        If Not Nz(attach2, "") = "" Then
            .AddAttachment attach2
        End If
        .send
    End With
   
    Set iMsg = Nothing
    Set iCfg = Nothing
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
omgangIT ManagerCommented:
@Blue_tech, have you searched this TA for the keyword CDO?  I'm sure I have posted on using CDO from Access on more than one occasion and I'm sure others have well.  Give me a bit an I'll post up an example.
OM Gang
0
omgangIT ManagerCommented:
....or better yet, see BCUNNEY's post above!
OM Gang
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.