none
Automatic email notification when a field reaches a set value RRS feed

  • 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?
    Wednesday, April 13, 2016 7:50 PM

Answers

  • 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.


    Thursday, April 14, 2016 2:17 AM

All replies

  • Hi. I don't think it's possible without using at least a macro. Access doesn't have built-in reminders like Outlook.
    Thursday, April 14, 2016 12:43 AM
  • 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.


    Thursday, April 14, 2016 2:17 AM