Share via

MS Access - Automatic Outlook email notification

Anonymous
2019-09-12T11:16:40+00:00

Hi, Hope someone can help with my situation. 

My access database is used for an inspection form and reporting system. During the inspection form there is a combo box which contains Pass or fail.  The combo box is called "cboResult". After this selection is made the record is saved and the manager must check over the report before sending it out via a report. I would like to implement an automatic email so that once the record is saved or the cboResult is selected an email is sent to the manager to basically say record Number xxxx is complete. 

Is this possible?

Many thanks.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

ScottGem 68,810 Reputation points Volunteer Moderator
2019-09-12T14:57:46+00:00

Yes its possible. Please check the SendObject method. This method can be used to generate an e-mail with the report as an attachment. This can triggered in either the After Update event of the form or the After Update event of cboResult.

If you need more help with this, please feel free to ask.

Was this answer helpful?

7 people found this answer helpful.
0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ScottGem 68,810 Reputation points Volunteer Moderator
    2019-09-21T00:01:20+00:00

    The Function starts this way:

    Public Function SendEmailWithOutlook( _

        MessageTo As String, _

        Subject As String, _

        MessageBody As String)

    This means that you have to tell the function who you ar sending the e-mail to (MessageTo), What Subject the e-mail will have (Subject) and what will go in the body of the message (MessgeBody). If you don't supply all three values the function won't work.

    So to use the function you would have code somewhere like:

    x=SendEmailWithOutlook("johndoe@gmailcom", 'This is the subject","This goes in the message body")

    You can either do it like that or you generate the values in a variable. For example:

    MessageBody = "This information is contained in record " & Me.ID & " of the database."

    Then you would use:

    x=SendEmailWithOutlook("johndoe@gmailcom", 'This is the subject",MessageBody)

    As to the error I would need to see exactly how you are calling the function.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. ScottGem 68,810 Reputation points Volunteer Moderator
    2019-09-19T12:31:32+00:00

    No, its a VBA command.

    https://docs.microsoft.com/en-us/office/vba/api/Access.DoCmd.SendObject

    There is a macro equivalent  EMailDatabaseObject

    https://support.office.com/en-us/article/EMailDatabaseObject-Macro-Action-DE087DF0-6D0C-4DFC-B1B6-BA1172D28AC5

    But you should use VBA as much as possible over macros.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2019-09-20T08:22:32+00:00

    HiScott, So this is the code i will be using; 

    Public Function SendEmailWithOutlook( _

        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

            .Send       ' Send the message immediately

        End With

        ' Release all object variables

        Set olMailItem = Nothing

        Set olApp = Nothing

    End Function

     Question 1- Is there a way for it to include the ID number of the record onto the email. 

    Question 2  - is there a way to implement this on the "pass" choice of cboResult

    Was this answer helpful?

    0 comments
  4. Anonymous
    2019-09-19T09:31:52+00:00

    Hi Scott, Thanks for your help. Is this "SendObject" method a macro command? As i cannot find it.

    Was this answer helpful?

    0 comments