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.
VBA
Tips (Main)
Home
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
ValidateData()
ClearClipboard()
Selecting an Excel Worksheet from Access
Send eMail to Multiple Recipients
Cancel Save in a Bound Form
fFindBookmark()
Looping Records to Send eMail
fxlFindReplace()
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...
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.
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...")
        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!
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.