لطفا منتظر باشید ...
Automating Outlook from Access
Microsoft Outlook is a very powerful email client. It is also an excellent tool for both task and contact management. As an application developer, I find many opportunities to automate Outlook from the Access applications that I build. For example, suppose one of my clients sends mass email mailings out to selected groups of her customers. I use an Access front end to manipulate customers stored in a SQL Server back end. Included in the front end is a feature that enables the users to generate an email message and then enter the criteria that designates which clients receive the email message. This is one of many examples of how you can integrate the rich features of Access and Outlook.The form pictured in Figure 22.13 allows the user to select an email template used for a mass mailing. The mailing is sent to all users who meet the criteria entered in a query called qryBulkMail. A more sophisticated example would allow the users to build the query on the fly, using a custom query-by-form. The code that allows the user to select an Outlook email template appears in Listing 22.12.
Listing 22.12 Selecting the Outlook Template
Private Sub cmdBrowse_Click()
'Filter the Open dialog to Outlook template files
dlgCommon.Filter = "*.oft"
'Display the Open dialog
dlgCommon.ShowOpen
'Populate txtTemplate with the selected file
Me.txtTemplate = dlgCommon.FileName
End Sub
Figure 22.13. This form allows the user to select the email template used for a mass mailing.
The code first sets the filter of the Common Dialog control to show only files with the .oft extension. It then displays the Open dialog. After the user selects a file, the name and path of the file are placed in the txtTemplate text box. The code required to send the mailing is shown in Listing 22.13.
Listing 22.13 Sending the Outlook Message to the Recipients in the qryBulkMail Resultset
Sub CreateMail()
' Customize a message for each contact and then send or save the message
Dim intMessageCount As Integer
'Declare and instantiate a recordset object
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
'Open a recordset based on the result of qryBulkMail
rst.Open "qryBulkMail", CurrentProject.Connection
intMessageCount = 0
Set mobjOutlook = CreateObject("Outlook.Application")
' Loop through the contacts in the open folder
Do Until rst.EOF
' Check that the contact has an email address.
If rst("EmailAddress") <> " Then
'Create a mail item based on the selected template
Set mobjCurrentMessage = mobjOutlook.CreateItemFromTemplate (Me.txtTemplate)
'Add the email address as the recipient for the message
mobjCurrentMessage.Recipients.Add rst("EmailAddress")
'Send the message or save it to the Inbox
If Me.optSend = 1 Then
mobjCurrentMessage.Save
Else
mobjCurrentMessage.Send
End If
intMessageCount = intMessageCount + 1
End If
rst.MoveNext
Loop
'Write the number of messages created to the worksheet
MsgBox intMessageCount & " Messages Sent"
End Sub
First, the code creates a recordset based on qryBulkMail. It then loops through the recordset. As it visits each row in the resultset, it creates an Outlook message based on the designated template. It adds the email address of the current row as a recipient of the email message. It then either saves the message as a draft, or immediately sends it to the designated recipient.
