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.
Who is Participating?
Barry CunneyConnect With a Mentor Commented:
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("") = 2
        .Item("") = 25
        .Item("") = "mail.*.si"
        .Item("") = 1
        .Item("") = "*@*.si"
        .Item("") = "*****"
        .Item("") = """comp d.o.o."" <*@*.si>"
    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
        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
    End With
    Set iMsg = Nothing
    Set iCfg = Nothing
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
omgangIT ManagerCommented:
....or better yet, see BCUNNEY's post above!
OM Gang
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.