Can Access send out automated emails when new records are added?

[gojets1721]

Complex one that I was hoping to get thoughts on the most efficient way to accomplish.

I want access to send out an automated email of an individual record when that record is added to the DB.

I have attached an example DB. Basically, I use a form to import an excel spreadsheet of records to a temp table. Then I use an append query to filter down to only new records which are then added to the main table.

At some point during that process, I want each of the new records to be individually emailed out.

I have used outlook automation and sendobject before; I'm just struggling to map out in my head how Acccess can individually identify each new record to email out.

Any suggestions?

 

[GPGeorge]

Complex one that I was hoping to get thoughts on the most efficient way to accomplish.

I want access to send out an automated email of an individual record when that record is added to the DB.

I have attached an example DB. Basically, I use a form to import an excel spreadsheet of records to a temp table. Then I use an append query to filter down to only new records which are then added to the main table.

At some point during that process, I want each of the new records to be individually emailed out.

I have used outlook automation and sendobject before; I'm just struggling to map out in my head how Acccess can individually identify each new record to email out.

Any suggestions?

You could create anAfterInsert Data Macro on that table to run a VBA procedure that sends the email. I think it's possible to send the email directly from the Data Macro, as well. It's not clear how you plan to accomplish this part: "each of the new records to be individually emailed out."
You'll have to figure out what means. Do you want to include all of the fields from the new record somehow, or a PDF showing the record? Whatever it means, that seems to me to be the more challenging part.

 

[theDBguy]

I'm just struggling to map out in my head how Acccess can individually identify each new record to email out.

One approach is to use a CreatedDate field, so you can tell when the record was added and thus identiy which ones are new.

 

[The_Doc_Man]

I have used outlook automation and sendobject before; I'm just struggling to map out in my head how Acccess can individually identify each new record to email out.

When you are about to make that merge happen, it appears from your description that you have a query that will add only the new records. There are only a gazillion approaches, but I'll take some pot shots at it.

First, you could put in a flag for your records to indicate that mail has been sent. This could be a Boolean Y/N type of flag or it could be a date/time field that tells you when the message was sent. I don't know how you will tell, but it is possible to write a "NOT IN" type of query that looks for records that are not already in the main table.

So you have your selection method. Then you import the data, setting the flag to FALSE or the Sent Date to 0. Then in a second pass, you can check for the flag that says "Message sent" or check for the date/time field telling when the message was sent. And when you find a flag in the wrong state or a date that is equivalent to 0 (i.e. 31-Dec-1899), you send that out and update the individual flag or date field as you go.

Second, you could take that query that is going to do the update and feed the new records to a temporary table, thus identifying and segregating the records you will add. Then come back with a recordset operation to send your messages based on the temp table.

 

[gojets1721]

You could create anAfterInsert Data Macro on that table to run a VBA procedure that sends the email. I think it's possible to send the email directly from the Data Macro, as well. It's not clear how you plan to accomplish this part: "each of the new records to be individually emailed out."
You'll have to figure out what means. Do you want to include all of the fields from the new record somehow, or a PDF showing the record? Whatever it means, that seems to me to be the more challenging part.

Sorry...should of clarified. I didn't include this in the example DB.

I have a report that basically just has all the individual record's fields on it. I would want to send that report via PDF.

 

[The_Doc_Man]

With this most recent clarification, I would suggest that you look at the report's .Filter property. You can provide a "filter" (essentially, a WHERE clause without the word WHERE in it) to select which individual record will be output. If you "diddle" with the report's .Filter property (and turn it on with .FilterOnOpen = TRUE) then you can do a SendObject and create the body of the message.

Report.Filter property (Access)

Office VBA reference topic

learn.microsoft.com

You might have to code up a loop on the recordset containing the new records so you can send them one at a time, because there is no way to use only SQL to build that list. But you can either send the messages one at a time, or if you were going to send a report with all of the new records, you would be able to build a "To" list before sending.

 

[gojets1721]

You could create anAfterInsert Data Macro on that table to run a VBA procedure that sends the email. I think it's possible to send the email directly from the Data Macro, as well. It's not clear how you plan to accomplish this part: "each of the new records to be individually emailed out."
You'll have to figure out what means. Do you want to include all of the fields from the new record somehow, or a PDF showing the record? Whatever it means, that seems to me to be the more challenging part.

Thanks! Perhaps a dumb question, but how do I make a VBA procedure via a data macro? When I try to add an after insert event on the table, I am only able to make a macro. I can't find where to use VBA. The 'convert to VBA' button is even greyed out...

 

[GPGeorge]

Thanks! Perhaps a dumb question, but how do I make a VBA procedure via a data macro? When I try to add an after insert event on the table, I am only able to make a macro. I can't find where to use VBA. The 'convert to VBA' button is even greyed out...

I was wrong. Data macros can't run VBA, although regular macros can.

The Data macro CAN, however, send email. It would run once for each record added or updated, depending on how you write it. If you want to send all emails out in a group, this isn't the way to go. If, however, the idea is to make sure someone gets an email for each new record, it would do that.

 

[gojets1721]

I was wrong. Data macros can't run VBA, although regular macros can.

The Data macro CAN, however, send email. It would run once for each record added or updated, depending on how you write it. If you want to send all emails out in a group, this isn't the way to go. If, however, the idea is to make sure someone gets an email for each new record, it would do that.

Okay thanks. So I've got it working using the SendEmail macro in the after insert. The problem is that there's no option to include an attachment. Any suggestions?

 

[Pat Hartman]

Data macros are limited in their functionality for obvious reasons - they run in the BE which does not support VBA.

You can do pretty much whatever you want, if you add the notification to your form's AfterInsert event.

 

[Isaac]

Data macros are limited in their functionality for obvious reasons - they run in the BE which does not support VBA.

You can do pretty much whatever you want, if you add the notification to your form's AfterInsert event.

Maybe not perfectly stated (BE supports vba same way FE does), but I agree with the idea of it.
There isn't supposed to be any VBA in your BE, it's supposed to sit there by its lonesome on the network.

Anyway - consider using Sharepoint and Flows for this purpose. Better yet, see if you can get your back end on SQL Server and use SSIS for this.

 

[gojets1721]

Data macros are limited in their functionality for obvious reasons - they run in the BE which does not support VBA.

You can do pretty much whatever you want, if you add the notification to your form's AfterInsert event.

Okay. So let's say my form imports in six new records. How do I code it so that six individual emails are sent out with six individual attachments of those records?

 

[Pat Hartman]

Not so Issac. Although you can open the BE and add code, etc. because it is still after all an .accdb, that isn't the same as what happens when the database engine which is ACE and NOT Access is in charge. MS Access does not even need to be installed for the database engine to work with an ACE database. Think about it this way. You have a Java application that is working with an "Access" database. Well the Java app isn't working with Access.exe, it is working with ACE which is the database engine. It does database stuff. It does not have any ability to run VBA. Why do you think the Validation Rules on tables are so limited? Because VBA is not available to the database engine.

What application is invoked when an .accdb is referenced using ODBC from a web page or a Java app? It is NOT MSAccess.exe MSAccess.exe is almost never installed on servers. This is the app :
Download Microsoft Access Database Engine 2016 Redistributable from Official Microsoft Download Center

 

[Pat Hartman]

How is the form importing the records? Do they have a group identifier that can be used to select them from the table? When I import data, I use a batch concept. I generate a batch number and I link to the source file. Then I use an append query that selects the data from the linked source and appends it to the permanent table including the batchID I just generated.

Then you can run a procedure that selects each record of that group, sends an email and updates the record to mark when the email was sent and even to whom if necessary.

 

[Isaac]

@Pat Hartman I understand what you mean of course, it just isn't true that "the BE cannot support VBA".
A BE is just an Access file, which can support VBA identically to the front end.
The difference is that the BE shouldn't have the types of objects that are usually used to invoke VBA, such as Forms > Events, etc.

 

[gojets1721]

How is the form importing the records? Do they have a group identifier that can be used to select them from the table? When I import data, I use a batch concept. I generate a batch number and I link to the source file. Then I use an append query that selects the data from the linked source and appends it to the permanent table including the batchID I just generated.

Then you can run a procedure that selects each record of that group, sends an email and updates the record to mark when the email was sent and even to whom if necessary.

The form drops all the new entries into a temp table, and then an append query adds to the main table. Each record has a unique identifier so the append filters down to ones not in the main table before appending.

I can reorganize that as needed, but I'm genuinely just getting stuck on how to code sending a batch of emails out. I've used automation and sendobject before on a single report, but I haven't ever tried to send out multiple emails/records

 

[Isaac]

@gojets1721
The main limitation of this approach is that you are always going to be relying on the end-user's specific machine to make it happen.
If anything about their machine setup, Outlook permissions, etc., doesn't cooperate, your code will fail.
This is why the better method is a server-only solution involving something like SQL Server + SSIS, or Sharepoint + Flows. Just food for thought for the future

 

[Pat Hartman]

That's fine Isaac but the Data Macros can't solve the problem so the application that does the import has to send out the emails.

1. Importing to a temp table causes bloat and is unnecessary. Just link to the spreadsheet.
2. You can use the batch technique I suggested OR you can use the linked table to control sending the emails.
3. Then use an append query to copy the linked data to the permanent table.

There are lots of email samples here.

 

[gojets1721]

@gojets1721
The main limitation of this approach is that you are always going to be relying on the end-user's specific machine to make it happen.
If anything about their machine setup, Outlook permissions, etc., doesn't cooperate, your code will fail.
This is why the better method is a server-only solution involving something like SQL Server + SSIS, or Sharepoint + Flows. Just food for thought for the future

It's only my machine. I'm the only one importing in new records. My org does not have sharepoint or flows

 

[gojets1721]

That's fine Isaac but the Data Macros can't solve the problem so the application that does the import has to send out the emails.

1. Importing to a temp table causes bloat and is unnecessary. Just link to the spreadsheet.
2. You can use the batch technique I suggested OR you can use the linked table to control sending the emails.
3. Then use an append query to copy the linked data to the permanent table.

There are lots of email samples here.

Thank you. Could you provide a sample. I'm really struggling to find a walkthrough of sending batch emails. All I can find is how to use outlook automation or sendobject to email out one singular record