none
Generate Automatic Emails on Microsoft Access RRS feed

  • Question

  • Hello,

    I am fairly new to Microsoft Access and am trying to generate automated emails for my supervisor 90 days prior to the due date. I have a master table from which I created a query that lists all of the information I need. The fields that are included in the query are the employee name, email and due date. I also added  a 'NumDay" field within the query which calculates the difference between the due date and todays date. I want to know how to create a module which can generate automated emails of the employees once the "NumDay" field reaches 90. I have no experience with Microsoft Visual Basic coding and would need assistance to complete this task.

    Thanks,
    Nigel Herbert
    Wednesday, August 2, 2017 1:37 PM

Answers

  • Hello,

    I am fairly new to Microsoft Access and am trying to generate automated emails for my supervisor 90 days prior to the due date. I have a master table from which I created a query that lists all of the information I need. The fields that are included in the query are the employee name, email and due date. I also added  a 'NumDay" field within the query which calculates the difference between the due date and todays date. I want to know how to create a module which can generate automated emails of the employees once the "NumDay" field reaches 90. I have no experience with Microsoft Visual Basic coding and would need assistance to complete this task.

    Thanks,
    Nigel Herbert

    Nigel, 

    There are more kinds of Basic, this forum is for the version in Visual Studio .Net, which can use Access DataBase but is not the one inside Access. In Access is VBA which has an own forum.

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/home?forum=isvvba


    Success
    Cor

    • Marked as answer by Nigel Herbert Thursday, August 3, 2017 3:13 PM
    Wednesday, August 2, 2017 2:00 PM

All replies

  • Hello,

    I am fairly new to Microsoft Access and am trying to generate automated emails for my supervisor 90 days prior to the due date. I have a master table from which I created a query that lists all of the information I need. The fields that are included in the query are the employee name, email and due date. I also added  a 'NumDay" field within the query which calculates the difference between the due date and todays date. I want to know how to create a module which can generate automated emails of the employees once the "NumDay" field reaches 90. I have no experience with Microsoft Visual Basic coding and would need assistance to complete this task.

    Thanks,
    Nigel Herbert

    Nigel, 

    There are more kinds of Basic, this forum is for the version in Visual Studio .Net, which can use Access DataBase but is not the one inside Access. In Access is VBA which has an own forum.

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/home?forum=isvvba


    Success
    Cor

    • Marked as answer by Nigel Herbert Thursday, August 3, 2017 3:13 PM
    Wednesday, August 2, 2017 2:00 PM
  • I had to do something similar a few months back.  Here is the solution I came up with. 

    Option Compare Database
    
    Private Sub Command1_Click()
    
    AllEmails = ""
    For Each ctrl In Me.qry_Ryan_Emails.Controls
    
        If TypeName(ctrl) = "CheckBox" Then
            If ctrl.Enabled = True Then
                AllEmails = AllEmails & " " & Me!qry_Ryan_Emails.Form.Work_Email
            End If
        End If
    
    Next ctrl
    
    Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        eSubject = Me!Subject
        eBody = Me!Message
    
        On Error Resume Next
    
        With OutMail
            .To = AllEmails
            .CC = ""
            .BCC = ""
            .Subject = eSubject
            .BodyFormat = olFormatHTML
            .Display
            .HTMLBody = eBody & vbCrLf & .HTMLBody
            .Send
        End With
    
        On Error GoTo 0
        Set OutMail = Nothing
        Set OutApp = Nothing
    
    End Sub

    This line concatenates all emails into one string.

    AllEmails = AllEmails & " " & Me!qry_Ryan_Emails.Form.Work_Email

    Then I just send 1 email to the group.

    This link is very useful for referencing controls on forms and subforms.

    http://access.mvps.org/access/forms/frm0031.htm


    It sends emails from a multi-select ListBox.  Post back if you have additional questions.


    MY BOOK

    Wednesday, August 9, 2017 10:59 PM
  • I just re-read this post.  Check out the link below.

    https://www.techwalla.com/articles/how-to-auto-email-access-reports

    Also, to expand on my original idea, you can try the following.

    First of all you create a query for therecords and in the "alert" field you put in the Criteria row

    DueDate() or Date()

    for the same day, or

    Date() + 90 

    for 90 days ahead.
    You then create an Autoexec macro (that runs when the database is opened) to open a "hidden" form based on the query
    On that form you have some simple VBA in the "On Load" or "On Current" event procedure like

    If Not Isnull(me.recordcount) or me.recordcount > 0 then
    me.Visible = True
    msgbox "the Records shown require action today"
    end if 


    MY BOOK




    • Edited by ryguy72 Thursday, August 10, 2017 1:48 AM
    Thursday, August 10, 2017 1:43 AM