Results 1 to 12 of 12
  1. #1
    Zimmerray is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    10

    Automatically send Email when a record is added or modified.


    First, I have very limited experience with Access and zero knowledge about VBA programming in Access. My request is for the code to automatically send an email to a certain address anytime a record has been modified, and anytime a new record is added. My assumption is to click "Create" then "Module" and add the code there.

    I realize this question has been asked before, however I have not been able to find the code and explicit instructions to make this work.

    I would be very grateful for assistance.

  2. #2
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,742
    Can I enquire what sort of data is being recorded? What is the purpose?
    If this involves any sort of significant number of records my advice would be not to do it.
    You are generating the sort of "noise" that users (email recipients in your case) will simply ignore after some time.

    It's a bit like that extra warning box that says "Are You Sure?" every time you press a certain button. After a very short period of use it just gets pressed.

    You might be better to have a summary report of new or changed records?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Zimmerray is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    10
    The number of records will be very few and an email would be a prompt that a record has been created and is ready for "required information" to be added. It definitely would not be "noise" as the the information to be added is what database is all about to begin with. I can forego the notification every time a record is modified, however, notification every time a new record is added would be a big help. Thanks for your quick reply.

  4. #4
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,742
    Okay that makes more sense. I have (at the request of managers and others normally higher up the food chain) added lots of "Double check, and Email and wave a red flag" type notifications against my better judgement in systems and they have almost without exception been switched of within a few months.

    The only exception I can remember was an email about approving or denying holiday requests, it used to fire out every day at 9.30am to the manager that had ignored it until it then went to their manager telling them they had not done their bit.

    What do you need in terms of email content? And does everyone have outlook installed ?

    EDIT: Or would a dashboard in the database opening form stating that there were 10 records needing more info and click here to update them be a better option?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,778
    For new record use form AfterInsert event to send email. Seems logical that you should be able to get at record data to include in your email but I've never tried this. I suppose you realize that 5 new records will be 5 new emails, but that seems to be OK.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  6. #6
    Zimmerray is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    10
    Minty,
    You definitely have my attention with the "Dashboard" on my opening menu. That could be a viable solution to your reluctance to the email option. I am willing to compromise and see how that would work. I apologize for my tunnel vision. Nice web site you have by the way.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,778
    I've used a prompt on opening as well, where someone created a record but missed important data. I can tell you that they get ignored just as much as emails do. You can't fix attitude with a database! I think the best you can do is flag problems and report them as appropriate.
    Last edited by Micron; 04-20-2021 at 10:57 AM. Reason: added comment
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  8. #8
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,742
    The good thing with the dashboard approach is that if you have a "to-do" list although it can be ignored, it won't go away until the criteria to not display it are fulfilled.
    This at least means that everyone can see that someone hasn't done something, and sooner or later someone will either do what they should have or more likely a manager will "prod" them.

    Highlighting a lists overdue things in a suitable annoying colour can also be a great "visual aid"
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    Zimmerray is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    10
    Minty,
    I have looked through the list at your web site and have been unable to find how to add the dashboard. As previously mentioned - Newbie here. So I will need your assistance to accomplish this task.

  10. #10
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,742
    I think you are mistaking the link in my signature as my web site - it's not I'm afraid, just a source of decent information!

    A dashboard is simply a normal access form with data displayed on it, normally from a variety of sources, but it can just be one.
    So lets say you had saved a query that selected all the records you needed data adding to - we'll call it qryDataRequired

    On your main menu form / dashboard you can do a number of things - the simplest one is a unbound text box with it's control sources set to

    = "Records that need Attention: " & DCount("*","qryDataRequired")

    That's it, basic but functional.

    For something more "sophisticated" you could display a continuous sub form with some key points from that query, Client_Name, DateProcessingRequired etc, etc.
    Then maybe get clever and open the form that that data need entering in, on the specific record they click on.

    All of these things are not complicated if you attack them in baby steps.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  11. #11
    Zimmerray is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    10
    Click image for larger version. 

Name:	Attention.jpg 
Views:	9 
Size:	27.1 KB 
ID:	45047 Now it shows all 684 records need attention. Next hurdle is to get it to count only new records and then drop off the ones that have been correctly modified. I'm having 2nd thoughts about this dashboard. I think it may be better to create a button to email a report manually. Thanks for your help. I have given you the thumbs up all the same.

  12. #12
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,742
    If there is a date stamp you could easily show how many new records were added today or yesterday?

    There is some email code here https://www.accessforums.net/showthr...838#post474838
    Which is probably over complicated but you should be able to dig the basics out from it.

    If you get stuck please post back with any questions.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 09-08-2019, 11:45 AM
  2. Email the new record added to access table
    By hazeleyre23 in forum Access
    Replies: 2
    Last Post: 04-07-2016, 06:38 AM
  3. Replies: 2
    Last Post: 01-16-2015, 04:22 PM
  4. Replies: 1
    Last Post: 08-14-2014, 03:37 AM
  5. Replies: 4
    Last Post: 02-05-2014, 04:05 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums