How to Automatically Email Employees Missing Daily Attendance in Access
Question details
The user wants to configure a database to identify employees who missed their daily check-in and automatically send them a reminder email at a designated time.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Managing a team's daily attendance tracking using a shared database and automating email reminders to ensure compliance without manual administrative work.
- Observed behavior
- The system needs to query the database for missing current-day records and execute an automated email loop at a scheduled time.
Ensure your Microsoft Access database is split correctly with backend tables accessible, and that you have an email client like Outlook configured on the machine running the automation.
Automate Missing Attendance Emails Using an Outer Join Query and VBA
Create a query to identify missing attendance records for the current day, and use a VBA Recordset loop triggered by a form timer event to send the emails.
This method relies on identifying employees who exist in the master employee table but lack a corresponding entry in the attendance table for today's date. By combining an Outer Join query with Access VBA, you can loop through these specific employees and automatically trigger an email via Outlook or the native SendObject command.
In Access, go to the Create tab and open Query Design. Add your 'Employees' and 'Attendance' tables. Double-click the relationship line to change it to an Outer Join (Include ALL records from 'Employees' and only those records from 'Attendance' where the joined fields are equal). Add the attendance date field and set its Criteria to `Is Null` for the current date.
Open the VBA Editor (Alt + F11) and create a new module. Write a subroutine using `DAO.Recordset` to open the query you just created. Use a `While Not .EOF` loop to iterate through each employee missing a record, formatting the email address and message body.
Inside your VBA loop, use `DoCmd.SendObject` or create an Outlook Application object (`CreateObject("Outlook.Application")`) to send a reminder email to the employee's email address retrieved from the current Recordset row. Move to the next record using `.MoveNext`.
Open your main navigation or dashboard form in Design View. In the Property Sheet under the Event tab, set the `Timer Interval` to 60000 (1 minute). In the `On Timer` event, write code that checks the current time. If it equals your scheduled time (e.g., 8:00 AM) and `DCount` verifies missing records exist, call your email subroutine.

Looking for a Lightweight and Free Office Suite? Try WPS Office
While Microsoft Access is powerful for complex database automation, WPS Office provides an exceptional, free alternative for handling documents, spreadsheets, and presentations. Easily manage attendance trackers using WPS Spreadsheet with an intuitive interface, seamless migration, and high format compatibility.

Frequently Asked Questions
How can I prevent the Access timer event from firing on weekends?
In your VBA Timer event code, use the `Weekday(Date())` function to determine the current day. Wrap your email execution code in an `If` statement that only runs if the weekday value is between 2 (Monday) and 6 (Friday).
Why is my Outer Join query returning all employees instead of just the missing ones?
Ensure you have added the current date criteria directly to the Attendance table side of the join, and set the condition to `Is Null` for the attendance record ID. If you filter on the date field instead of testing for Null, the Outer Join may not behave as expected.
Does Microsoft Access need to be open for the timer event to work?
Yes, the Access database and the specific form containing the Timer Event must be open and running on a machine for the automated VBA code to execute at the scheduled time.
Can I use this method to send SMS notifications instead of emails?
Yes, if your mobile carrier supports Email-to-SMS gateways, you can map the employee's phone number to their carrier's SMS email domain (e.g., number@vtext.com) and send the reminder through the exact same VBA email loop.




