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.
Tips (Main)
Creating a Multi-Value field using Alphabet
Copy Fields Down from above Record
Loop thru records and OutPutTo seperate .RTF or .PDF
Modified Spell Check
Code Snippets
Lock\Unlock Bound Controls
Loop while renumbering two columns
Create a Table with Dynamic Field Names
Snippets for Exporting to Excel
Log Field Changes
Log Record Deletions
Check for Duplicate Values
Selecting an Excel Worksheet from Access
Send eMail to Multiple Recipients
Cancel Save in a Bound Form
Looping Records to Send eMail
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
            Do While Not .EOF
                DoCmd.OpenReport "rpteMailOverdueTasks", acViewPreview, , "[apAssociateID] = " & rs!apAssociateID
                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
        End With
        If Dir("Drive:\Folder\*.pdf") <> "" Then
            Kill "Drive:\Folder\*.pdf"
        End If
        End With
        Set rs = Nothing
    DoCmd.Hourglass False
    Exit Function
    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...
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
Copy\paste the below into a seprate Module (not behing 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...")
        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!
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.