databasedev.co.uk - database solutions and downloads for microsoft access

Sending E-Mails from a Microsoft Access Database:

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:

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.