There are various techniques and approaches to sending e-mails via a Microsoft Access Database and here you will find one working example of how to achieve this, via a Microsoft Access Form.
The Microsoft Access database download sample, which can be accessed from either the Microsoft Access Forms page or the Microsoft Access Downloads page, uses the SendObject Method which contains the following syntax:
DoCmd.SendObject [objecttype][, objectname][, outputformat][, to][, cc][, bcc][, subject][, messagetext][, editmessage][, templatefile]
The SendMail procedure which is called from the main database switchboard, via the Send Mail command button or which is also called if there is mail to be sent when the Microsoft Access database is being exited, can be seen below:
The SendMail procedure is called from the Send Mail command or upon Exit of the database.
'----------------------------CODE START---------------------------- Public Sub SendMail() 'Provides the Send Mail automation Dim dbs As DAO.Database Dim rst As DAO.Recordset Dim strSubject As String Dim strEmailAddress As String Dim strEMailMsg As String Dim ingCounter As Integer Dim intCount As Integer strSubject = "Latest Job Outcomes" strEmailAddress = "[Mail Addresses Go Here]" 'strEmailAddress,replace [Mail Addresses Go Here] above with valid 'e-mail addresses Set dbs = CurrentDb Set rst = dbs.OpenRecordset("qrySendMail") 'Count of unsent e-mails intCount = DCount("[lngJobOutcome]", "[tblJobOutcomes]" _ , "[ysnSentByMailToStaff]=0") 'If count of unsent e-mails is zero then the procedure will not run 'If count of unsent e-mails is greater than zero, msgbox will prompt 'to send mail. If intCount = 0 Then MsgBox ("You have " & intCount & " new job outcome e-mails to send.") _ , vbInformation, "System Information" Exit Sub Else rst.MoveFirst Do Until rst.EOF strEMailMsg = rst![strStudentFirstName] & " " & rst![strStudentLastName] _ & " - " & rst![strStudentNumber] & " - " & " on the " & rst![strCourse] _ & " course" & " has informed us of a new job." & Chr(10) & Chr(10) _ & "Below are the details that have been submitted by the student:" _ & Chr(10) & Chr(10) & rst![memNewJobDescription] & Chr(10) & Chr(10) _ & "Graham" 'EMAIL USER DETAILS & ATT REPORT DoCmd.SendObject , , acFormatRTF, strEmailAddress, _ , , strSubject, strEMailMsg, False, False rst.MoveNext Loop rst.Close Set rst = Nothing dbs.Close Set dbs = Nothing 'Run update to update the sent mail check box DoCmd.SetWarnings False DoCmd.RunSQL "UPDATE tblJobOutcomes " & _ "SET tblJobOutcomes.ysnSentByMailToStaff = -1 " & _ "WHERE (((tblJobOutcomes.ysnSentByMailToStaff)=0))" DoCmd.SetWarnings True MsgBox "All new Job Outcomes have been sent", vbInformation, "Thank You" End If End Sub '-----------------------------CODE END-----------------------------
Due to the nature of the Microsoft Access application contained in the example, the above procedure also runs the SQL Update statement that updates the JobOutcome table ysnSentByMailToStaff field to ensure that the e-mails get sent only the once.
The procedure also writes out the subject and e-mail body with information from the associated tables.
In order to use the attached Microsoft Access Database example please ensure that you read the ReadMe.txt file included in the sendmail.zip file which details how to test the example.