MS Access - Automatic Outlook email notification

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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

Answer 5

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