Can Access send out automated emails when new records are added? (1 Viewer)

[Pat Hartman]

Access Diva Looping Records to Send eMail

Access-Diva provides custom databases using Microsoft Access utilizing SQL Server when called for.

www.access-diva.com

Gina provides two options. One that uses Excel and another that uses PDF's. You didn't say what you wanted to send. Both methods are essentially the same so if you just have a text string you want to include in the body, either will work.

If you have more than a couple of emails to send, I would not use the PDF method as written. There is a lot of extra overhead involved with opening the report first in order to get it filtered before converting to a PDF and then sending the PDF. The method I use does NOT require opening the report first and so is far more efficient for large mailings.

I use a hidden field on a form. As I iterate through the loop, I populate the hidden form field with the filter value. Then the Report is bound to a query that has a where clause that looks at the hidden form field:

Where SomeID = Forms!YourForm!txtHiddenSomeID

The problem is caused because the OutputTo does not have the same ability of the OpenReport method to provide where arguments on the fly. That is why the hokey and inefficient method is used in a lot of samples when sending PDF's.

 

[GPGeorge]

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?

Theoretically, the SendEmail action in the Data Macro would send one email each time a new record is added to the table.

You want to include the attachments in those emails, though. That's probably beyond the reach of the Data Macro, as Pat et all have pointed out.

The process of sending emails to a group involves a recordset containing the information needed (email addresses, names of recipients, etc.). You can use a Do While Loop or a For Loop to iterate over that recordset, sending the individual emails inside the loop.

There are multiple examples, I'm sure of using this approach. Don't limit the search, though, to only sending emails.

 

[gojets1721]

Access Diva Looping Records to Send eMail

Access-Diva provides custom databases using Microsoft Access utilizing SQL Server when called for.

www.access-diva.com

Gina provides two options. One that uses Excel and another that uses PDF's. You didn't say what you wanted to send. Both methods are essentially the same so if you just have a text string you want to include in the body, either will work.

If you have more than a couple of emails to send, I would not use the PDF method as written. There is a lot of extra overhead involved with opening the report first in order to get it filtered before converting to a PDF and then sending the PDF. The method I use does NOT require opening the report first and so is far more efficient for large mailings.

I use a hidden field on a form. As I iterate through the loop, I populate the hidden form field with the filter value. Then the Report is bound to a query that has a where clause that looks at the hidden form field:

Where SomeID = Forms!YourForm!txtHiddenSomeID

The problem is caused because the OutputTo does not have the same ability of the OpenReport method to provide where arguments on the fly. That is why the hokey and inefficient method is used in a lot of samples when sending PDF's.

Thanks! One (possibly stupid) question...where do I call the function in Gina's PDF example once it's all set up?

 

[gojets1721]

I use a hidden field on a form. As I iterate through the loop, I populate the hidden form field with the filter value. Then the Report is bound to a query that has a where clause that looks at the hidden form field:

Where SomeID = Forms!YourForm!txtHiddenSomeID

The problem is caused because the OutputTo does not have the same ability of the OpenReport method to provide where arguments on the fly. That is why the hokey and inefficient method is used in a lot of samples when sending PDF's.

Also, could you provide an example DB of this method by any chance?

 

[Pat Hartman]

You already have the code for looping through the recordset. Look at Gina's code. This is the part I'm talking about:

                    DoCmd.OpenReport "rpteMailOverdueNotifee", acViewPreview, , "[NotifeeID] = " & rs!NotifeeID
                    DoCmd.Minimize
                    DoCmd.OutputTo acOutputReport, "rpteMailOverdueNotifee", acFormatPDF, "H:\Databases\Reports\" & !NotifeeID & "-Overdue.pdf"
                    DoCmd.Close acReport, "rpteMailOverdueNotifee", acSaveNo

You would change it to:

                    Me.HiddenNotifeeID = " & rs!NotifeeID
                    DoCmd.OutputTo acOutputReport, "rpteMailOverdueNotifee", acFormatPDF, "H:\Databases\Reports\" & !NotifeeID & "-Overdue.pdf"

To make this work, you need to add a Where clause to the query that the report is bound to.

Where NotifeeID = Forms!yourformname!HiddenNotifeeID

OBVIOUSLY, you will need to change all field names and other hard-coded references to your OWN names.

 

[spaLOGICng]

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.

Tables Macros cannot call VBA Functions or Procedures.

 

[GPGeorge]

Tables Macros cannot call VBA Functions or Procedures.

I already apologized for that error. But thanks for being scrupulous.

 

[Pat Hartman]

Thanks! One (possibly stupid) question...where do I call the function in Gina's PDF example once it's all set up?

Sorry, I missed this. Just replace your single export code with the loop code which should be in the click event of a button.

 

[spaLOGICng]

I already apologized for that error. But thanks for being scrupulous.

No worries. I had been reading a lot of the comments. I don't know why I did not see your second comment where you corrected yourself.