Access Diva Automatically Send eMail Notifications

The difficult I do immediately, the impossible takes a little bit longer.

Automatically Send eMail Notifications...

Those To-Do Lists can be a bear to manage. So why not let Access to do it? Here's a basic outline of how to do just that where Access sends an eMail to those with to-do items still on their list.

Public Function streMailOverdueTasks() As String
 
    Dim strAttachments As String
 
    On Error GoTo Error_Proc
 
    DoCmd.Hourglass True
 
    'Create new MailItem object.
    Set objNewMail = olApp.CreateItem(0)
 
    Dim strTo As String
    Dim strSQL As String
    Dim rs As Recordset
    Dim strSubject As String
    Dim strBody As String
 
    strSQL = "SELECT qryeMailOverdueTasks.apAssociateID, qryeMailOverdueTasks.apCompanyeMailAddress " & _
                "FROM qryeMailOverdueTasks" & _
                    "GROUP BY qryeMailOverdueTasks.apAssociateID, qryeMailOverdueTasks.apCompanyeMailAddress"
 
    Set rs = CurrentDb.OpenRecordset(strSQL)
 
    With rs
        .MoveFirst
            Do While Not .EOF
                DoCmd.OpenReport "rpteMailOverdueTasks", acViewPreview, , "[apAssociateID] = " & rs!apAssociateID
                DoCmd.Minimize
                DoCmd.OutputTo acOutputReport, "rpteMailOverdueTasks", acFormatPDF, "Drive:\Folder\" & !apAssociateID & "-OverdueTasks.pdf"
                DoCmd.Close acReport, "rpteMailOverdueTasks", acSaveNo
        strAttachments = "Drive:\Folder\" & !apAssociateID & "-OverdueTasks.pdf"
 
        Set objNewMail = olApp.CreateItem(0)
        With objNewMail
            .To = rs.Fields("apCompanyeMailAddress")
            .Subject = "Your Overdue Taks!"
            .Body = "See attachment..."
 
            If strAttachments <> "" Then
                .Attachments.Add strAttachments
            End If
            'For testing
            '.Display
            .Send
        End With
        .MoveNext
        Loop
 
        If Dir("Drive:\Folder\*.pdf") <> "" Then
            Kill "Drive:\Folder\*.pdf"
        End If
 
        End With
        rs.Close
        Set rs = Nothing
 
Exit_Proc:
    DoCmd.Hourglass False
    Exit Function
Error_Proc:
    Select Case Err.Number
        Case 287:
          'On Error Resume Next
          Resume Exit_Proc 'ignore the error'
        Case Else:
          MsgBox "Error encountered streMailOverdueTasks: " & Err.Description
          Resume Exit_Proc 'display a message then exit'
    End Select
 
End Function

Create a Table tbleMailSent...

Copy\paste the below into a separate Module (not behind any Form) making sure to add your Drive and Folder information and confirm the name of fields used match what you are using.

Create a Report that is to be sent and a query that shows overdue tasks which can be a GROUP BY based off the report you will be attaching and name it qryeMailOverdueTasks. The query will need the eMail Address and ID of each Associate. (If you change the name you will need to adjust the below Module.)

Notice at the top it checks to see if it should be sending eMails. If esSend equals FALSE no eMails will be sent.

    If DLookup("esSend", "tbleMailSent", "esID = 1") = False Then Exit Sub
 
        DoCmd.Hourglass True
 
        If DCount("apAssociateID", "qryeMailOverdueTaskss") > 0 Then
            UpdateUser ("Sending eMail notifications for overdue Tasks to Associates, please wait...")
            Call streMailOverdueTasks
            UpdateUser ("All done, you may continue...")
        Else
            DoCmd.CancelEvent
        End If
 
        strSQL = "UPDATE tbleMailSent SET tbleMailSent.esDate = Date()"
                  CurrentDb.Execute strSQL, dbFailOnError
 
        DoCmd.Hourglass False
        UpdateUser ("All done, you may continue...")

Copy\paste the below to the On_Load event of the initial Form that opens when your database opens.

All done, you're ready to have Access send out eMails!

Field	Data Type	Description
esID	Autonumber - PK
esAutomaticSent	Date/Time	Date Sent (Will update everytime it sends a batch.)
eSend	Yes/No	Indicate whether to send or not. Set to TRUE for eMails to be sent.