Daily Access Tips

How to create Microsoft Access email is a common request. I received the following question a while back and it presented a great opportunity to show how useful Microsoft Access recordsets can be. Microsoft Access recordsets allow you to move through a set of records and use the data contained in them to perform such tasks as emailing, running reports or queries, analyzing data, etc.

The request was: “At the moment I am building a database for … I have a table holding information of the product, user, email address and balance field. I would like to create an automatic email alert to any user who’s balance is under £10 at anytime. I am guessing the solution is probably fairly simple but am really struggling. I have no idea how to do it so any help would be greatly appreciated.”

Here is the solution that I offered:

You can send the email by various methods but to use the built in Access email command that does not require Microsoft Outlook or access to an SMTP mail server just use the DoCmd.SendObject acSendNoObject (default). The acSendNoObject argument allows you to just send email message without sending an Access object such as a Report or Query object.

Just so there can be a date stamp of when the email was sent, first add a new date field called Email_Sent_Date to your table. Next create a query called ‘Email_Reminder_RecordSet’, that has the name and email address of users having a balance under 10 pounds and optionally who have not received an email in the past month or what ever time seems prudent. You will use the new field ‘Email_Sent_Date’ for this purpose and this will allow the following code to enter the date that the email was sent. The code on the on click event of your button will open a recordset on this query and loop through it’s records to send the emails. Here is the code:

‘This code is using DAO (Data Access Objects) for recordset control so be sure DAO is showing under Tools menu Under References in your code module as a ‘referenced code library or select DAO 3.6 to add it if it is not showing already.

If you copy and paste code from this article, Please replace all double quotes that look curly with standard straight looking double quotes.  These “Web Page Smart Quotes”, that have a curved look to them, will not compile in your code editor unless they are replaced with your standard keyboard double quotes.

Here is the code to paste into the OnClick event of the button you will use to trigger this emailing code:

Dim rs as dao.recordset
set rs = currentdb.openrecordset(“Email_Reminder_RecordSet”)
with rs
if .eof and .bof then ‘IT MEANS THERE ARE NO RECORDS, WE ARE AT THE BEGINNING OF FILE AND END OF FILE (No Records found for this query.)
Msgbox “ No emails will be sent because there are no records from the query ‘Email_Reminder_RecordSet’ “
else
do until .eof ‘DO UNTIL END OF RECORDSET FILE

‘THE NEXT LINE SENDS THE EMAIL MESSAGE
DoCmd.SendObject acSendNoObject, , , ![Email Address Field], , , “Low Balance Alert”, “Hello ” & ![First Name Field] & _
“, ” & Chr(10) & “You ballance is under 10 pounds. Please…”, False

.edit
![Email_Sent_Date] = now()
.update
.movenext ‘THIS MOVES TO THE NEXT RECORD IN THE RECORDSET
loop

End If
end with

‘CLEAN UP MEMORY AT END
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If

Please let me know how this works for you by leaving a reply / comment below.