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

Sending E-Mails from a Microsoft Access Form:

Often, processes that are performed in Microsoft Access require that others be notified. The automation of E-Mails from a Microsoft Access Database Form is a prime example of this. This is particularly true when assigning individuals to specific duties/tasks, such as managing the relationship with a new customer or processing a helpdesk ticket. Because notification in most organizations means sending an email, the low-tech approach of creating and sending an email outside of your Microsoft Access database, especially if you are dealing with a high volume of notifications, can be incredibly inefficient and means the duplication of workload. Luckily these instances provide a classic opportunity to use technology to improve your productivity and in general make your job less aggravating.

This article will demonstrate how to implement vb-code that will automate the sending of emails (via Outlook) from a Microsoft Access database. Because the authors experience with emailing notifications has involved a helpdesk, the objects in this database will have the most relevance for a helpdesk using Microsoft Access. However, once you understand the code and how to implement it, this capability can be adjusted to suit the needs of any Microsoft Access database system.

For the Helpdesk scenario, we will use the following database design:

The relationship design of the Helpdesk database

This shows the main table of tblHelpDeskTickets, where information relating to problems/issues are stored. The table, tblHelpdeskEmployees is the list of Employees who work on the Helpdesk logging the problems. The table, tblUsers, stores the details of the Helpdesk support personal, where the e-mails will be sent to (the assignee's in this case).

If we take a look at the Microsoft Access form that we will be using to send the e-mail from, this will demonstrate the user interface required in this type of situation:

The Microsoft Access form that will be used to automate the creation of E-mails to be sent from the database

As you can see, this is a very straightforward form design. It captures the information that is relevant, and that’s all. In practice, this would probably be a serviceable initial design, but you would quickly see the need for more fields. The form collects all of the required information to be used in the automated e-mail, now we need to add the required vb-code to the command button, that will generate the e-mail in Microsoft Outlook to be sent.

For the OnClick event of the command button (cmdMailTicket) that will create the e-mail we add the following code:

Private Sub cmdMailTicket_Click()
    On Error GoTo Err_cmdMailTicket_Click

    Dim stWhere As String       '-- Criteria for DLookup
    Dim varTo As Variant        '-- Address for SendObject
    Dim stText As String        '-- E-mail text
    Dim RecDate As Variant      '-- Rec date for e-mail text
    Dim stSubject As String     '-- Subject line of e-mail
    Dim stTicketID As String    '-- The ticket ID from form
    Dim stWho As String         '-- Reference to tblUsers
    Dim stHelpDesk As String    '-- Person who assigned ticket
    Dim strSQL As String        '-- Create SQL update statement
    Dim errLoop As Error

    '-- Combo of names to assign ticket to
    stWho = Me.cboAssignee
    stWhere = "tblUsers.strUserID = " & "'" & stWho & "'"
    '-- Looks up email address from TblUsers
    varTo = DLookup("[strEMail]", "tblUsers", stWhere)

    stSubject = ":: New Help Desk Ticket ::"

    stTicketID = Format(Me.txtTicketID, "00000")
    RecDate = Me.txtDateReceived
    '-- Helpdesk employee who assigns ticket
    strHelpDesk = Me.cboReceivedBy.Column(1)


    stText = "You have been assigned a new ticket." & Chr$(13) & _
             Chr$(13) & "Ticket number: " & stTicketID & Chr$(13) & _
             "This ticket has been assigned to you by: " & strHelpDesk & _
             Chr$(13) & "Received Date: " & RecDate & Chr$(13) & _
             Chr$(13) & "This is an automated message." & _
           " Please do not respond to this e-mail."

    'Write the e-mail content for sending to assignee
    DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1

    'Set the update statement to disable command button
    'once e-mail is sent
    strSQL = "UPDATE tblHelpDeskTickets " & _
             "SET tblHelpDeskTickets.ysnTicketAssigned = -1 " & _
             "Where tblHelpDeskTickets.lngTicketID = " & Me.txtTicketID & ";"


    On Error GoTo Err_Execute
    CurrentDb.Execute strSQL, dbFailOnError
    On Error GoTo 0

    'Requery checkbox to show checked
    'after update statement has ran
    'and disable send mail command button
    Me.chkTicketAssigned.Requery
    Me.chkTicketAssigned.SetFocus
    Me.cmdMailTicket.Enabled = False

    Exit Sub

Err_Execute:

    ' Notify user of any errors that result from
    ' executing the query.
    If DBEngine.Errors.Count > 0 Then
        For Each errLoop In DBEngine.Errors
            MsgBox "Error number: " & errLoop.Number & vbCr & _
                   errLoop.Description
        Next errLoop
    End If

    Resume Next


Exit_cmdMailTicket_Click:
    Exit Sub

Err_cmdMailTicket_Click:
    MsgBox Err.Description
    Resume Exit_cmdMailTicket_Click

End Sub

This code creates the following example e-mail when generated from the Microsoft Access form that contains the information:

The e-mail generated by Microsoft Access through Microsoft Outlook, using information from the form

Once the e-mail has been sent through Outlook, using the DoCmd.SendObject command the form is then updated, using an Update statement in the code.

The Update statement is ran using CurrentDb.Execute strSQL, dbFailOnError which executes the following SQL:

strSQL = "UPDATE tblHelpDeskTickets " & _
             "SET tblHelpDeskTickets.ysnTicketAssigned = -1 " & _
             "Where tblHelpDeskTickets.lngTicketID = " & Me.txtTicketID & ";"

Once this has ran, the form is updated, the the command button is disabled to prevent the sending of duplicate e-mails. This action is carried out using:

'Requery checkbox to show checked
'after update statement has ran
'and disable send mail command button
    Me.chkTicketAssigned.Requery
    Me.chkTicketAssigned.SetFocus
    Me.cmdMailTicket.Enabled = False

This disables the command button as shown:

The Send Ticket command button is disabled once the e-mail has been sent

An example of this database can be found in the Microsoft Access downloads section or the Microsoft Access Forms section. This example is created in Microsoft Access 2000. You will need to alter e-mail addresses to test this fully.