Email when a new record is created

[Famfeld]

Hi,



I know there are already a few posts with similar info, but specifically I would like to learn how to have access send emails to a few recipients when a new record is created.

Perhaps someone can get me a link to a tutorial or another post?

Thanks,

Famfeld

[ranman256]

set a variable when they begin a new record. then email when they save it.

public gbNewRecBegin as Boolean



'here they start a new record
Private Sub Form_BeforeInsert(Cancel As Integer)
gbNewRecBegin = True
End Sub



  'then when they save it.
Private Sub Form_AfterUpdate()
if gbNewRecBegin then
  DoCmd.SendObject acSendQuery, "qsNewRec", acFormatXLS, "name@aol.com", , , "Subject", "message"
  gbNewRecBegin =false
endif
End Sub

[June7]

Use form AfterUpdate event.

SendObject is simplest method to send email.

And now I see ranman already posted sample code.

[Micron]

As you say, tons of examples on how to send email using Access, so I suspect you're needing to know how to trigger it for your case. For that I'd say use form AfterInsert event.

EDIT - AfterUpdate will fire when record is edited - not what is needed here, I think?

[June7]

That is better. Don't have to test for new record and no unnecessary calls to procedure.

[CJ_London]

Another option is to use a data macro when a new record is created. The macro would need to call a vba function to send an email. Plenty of examples how to send an email

[Famfeld]

Thanks for helping.

I get an error that qsNewRec doesn't exist as an object. I get the main idea of what is being done, but I also I don't understand where I am telling Access or VBA to communicate with an email host or server?

Is there an example in a video or a tutorial that someone could point me to?

Thanks again,

Famfeld

[Famfeld]

Thanks for the help. Would it be good for me to attach my project to this thread?

[Welshgasman]

That should be the name of your query, that produces what you class as new records.?
Perhaps at the end of the day, select all the records created today (for that you would need to store the date created) and email that query to your recipients.

[June7]

Exactly what do you want to include in this email? Just a record ID or data from each field? Can either send an object (query, form, report) as a PDF attachment or don't send object and build an email body as a string that would be part of the Message argument.

This is a common topic. Check out the "Similar Threads" found below this discussion. One of them is https://www.accessforums.net/showthread.php?t=49199