Mail Merge to Microsoft Outlook
UI Builder for Microsoft Access can help you send professional, formatted emails from Microsoft Access in a single click. Download a free trial today.
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.