Automatic email notification when a field reaches a set value

Question

Hi, I created a database in MS Access 2010 for tracking some financial documentation. I would like to have an automatic email sent when a record's "Days_Outstanding" field reaches a set value. I also have a table of email addresses that I would like to send the email to depending on who initiated the documentation. Is there any way that I could do this with a macro or VBA?

Answer 1

Hi. I don't think it's possible without using at least a macro. Access doesn't have built-in reminders like Outlook.

Answer 2

Hi Kha,

>> I would like to have an automatic email sent when a record's "Days_Outstanding" field reaches a set value.

For this requirement, I suggest you use After Insert, After Update event, check the field value while After Insert, After Update event, if it meets your requirement, send email.

For sending email, you could refer the code below:

Public Function CreateEmailWithOutlook( _
    MessageTo As String, _
    Subject As String, _
    MessageBody As String)

    ' Define app variable and get Outlook using the "New" keyword
    Dim olApp As New Outlook.Application
    Dim olMailItem As Outlook.MailItem  ' An Outlook Mail item
 
    ' Create a new email object
    Set olMailItem = olApp.CreateItem(olMailItem)

    ' Add the To/Subject/Body to the message and display the message
    With olMailItem
        .To = MessageTo
        .Subject = Subject
        .Body = MessageBody
        .Display    ' To show the email message to the user
    End With

    ' Release all object variables
    Set olMailItem = Nothing
    Set olApp = Nothing

End Function

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.