Results 1 to 7 of 7
  1. #1
    Join Date
    May 2006
    Posts
    386

    Unanswered: Can anyone help on how to create an automated email reminder from MS Access database

    Dear All,

    My manager has been asking me for this for months but I never managed to get this working.

    I was wondering whether someone would be kind enough to guide me through on how to set up or write a code in MS Access database to send automated email reminders through ms outlook.

    Basically, I want to the database to send 3 different reminders at different intervals. For example if a student has not returned the books back to the library 30 days after it was taken, the system should bring up a popup screen informing the user that "would you like to allow this reminder to be sent to x or y" Also the user should be able see what is being sent before clicking Yes or NO. if clicked yes, then the body of email should include the following
    information so the student will know what we are chasing them for:

    Date book was taken NameOfBook FeePaid FinedAmount

    Any advice on this would be highly appreciated.
    Emi-UK
    Love begets Love, Help Begets Help

  2. #2
    Join Date
    Mar 2009
    Posts
    15

    Can you tell me how to even make a 'send email macro'?

    First of all, if you even get an answer to your question, I hope you can throw that over to me as well. That is also something I am trying to figure out how to do.

    But another thing that maybe you can help me with - I'm trying to create an action button, a macro, I guess, in which you press it, and an email of a form or report is sent to the email address specified. I noticed your post and how I would like to know that as well, so jumped on with also the hopes that you may happen to know how to create that send email macro.

    Any advice would be greatly appreciated.

  3. #3
    Join Date
    Oct 2009
    Posts
    340
    the key is the SendObject method - look that up in the VBA help area and get to know it....

    Access is not a Contact Manager - so you need to avoid using the wrong tool. Also one must temper the concept of "automatically send emails". Access is event driven - not a running service. You must start it, and then trigger it.

    But there are anti-spam barriers between products and you have to manage it. If there is a high quantity - you'll need to write a loop so it refires thru the whole list. Also - the SendObject is ok for brief messages but lacks formatting and layout elegance.

    So usually, to be practical, a hybrid solution is often used. Use Access to generate who is to be contacted (a query of their email addresses) - and then copy/paste that list over into the email system bcc - and send a canned message. Semi manual but often more practical.

    Also if you use Outlook you can use it's feature for email merge - and an Access query is the data source. Ditto for Word merge - and the Word doc is an attachment of your email.

    But when ever I hear a client's requirement and it becomes clear to me that they need a Contact Management tool - I don't see any reason to reinvent that wheel as GoldMine and other packaged products are out there that do that....

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Regarding the automated emails routine, you'll probably want to create a function to run on your startup form to check and see if there are any records to send and then open a form showing those reminders where the user can then select which ones to send (if I read you correctly).

    For example, in the OnOpen event of your startup form (which only loads at once and only when the mdb first opens or whenever you call the function will be key), you might call a function like this:

    If retHasEmailsToSend() = true then
    docmd.openform "SendRemindersFormName" <- see codebank for examples on this.
    end if

    and then your function (in a module) might look like this (for ADO coding)....

    Function retHasEmailsToSend() as boolean
    retHasEmailsToSend = false
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from myTableName where DateFieldNameX <= #" & Date() & "#"
    rs.open strsql,currentproject.connection,adopenkeyset,adlo ckreadonly
    if rs.eof and rs.bof then
    retHasEmailsToSend = false
    else
    retHasEmailsToSend = truee
    end if
    rs.close
    set rs = nothing

    (Note: or above code taps into the Outlook tables to get reminders - you'll need to research how to do this if this is the way you want to do it.)

    How you setup the reminder/email type table will be key.
    Last edited by pkstormy; 02-27-10 at 01:21.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    May 2006
    Posts
    386
    Dear pkstormy,
    Thank you for your response. I apologise for the delayed response. I was on sick leave and came back to work and my boss has thrown the same question at me.

    To shed more light on this, I already a ReminderForm which opens at the StartUp and it gives me a list of all clients and a list of their overdue invoices, amounts etc. However, I would like the database to flag the user that clients a to z have these many overdue invoices, would you like to send them an email reminder? if we click on yes, then it should send the reminder accordingly.

    What is more important is that lets assume a reminder was emailed to Exmaple Solicitors on 07/02/11 giving them 5 days to pay, if payment is not received within 5 days, then the system should send the second reminder letter in which they will be given 3 more days, and if they dont pay 3 days later, then a 3rd and final reminder should be sent.

    Any help on this would be extremely appreciated.
    Emi-UK
    Love begets Love, Help Begets Help

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    persoanlly I'd do this sort of thing as part of an automated process kicked off by the windows scheduler

    inside the scheduler you open a compy of Access with a specified database and specify what macro or procedure to run.

    the macro can kick off a VBA procedure.. I forget the details but it should; be out there on t'net

    that procedure extracts the relevant customer invoices, does what ever processing and then terminates.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2006
    Posts
    386
    Hi Healdem,
    Thank you for your resopnse. Would you kind enough to share the code for this procedure. I think the most important part in doing this would be to ensure the reminder pickup list of all invoices from the ReminderForm and then sends reminder based on previous reminders i.e. reminder 1, reminder 2 and reminder 3.
    Emi-UK
    Love begets Love, Help Begets Help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •