15.2. Access and Outlook—Sharing Data with Others
Within our Inventory Control application, users often need to communicate information between departments about inventory that has been ordered, allocated, or consumed. If the Planning department receives a rush order, it might not have enough material in stock to completely cover the job. It might need to communicate with the Order Entry department about possible delays with the order. The easiest way to handle this communication is to write some simple VBA code to send a formatted e-mail message to the Order Entry department. The Planning department can accomplish this directly from the Material Ordering form of the database, shown in Figure 15-2.
Figure 15.2. Figure 15-2
In order to communicate back to the Order Entry department that the order is going to be delayed, all the planner needs to do is click the Alert button to display the e-mail message as shown in Figure 15-3.
The e-mail to the Order Entry department contains the order number, the original order due date, and the expected material receipt date. There is enough information in the e-mail for the Order Entry department to contact the customers and inform them of the delay. Because the e-mail message is delivered directly to the Order Entry department inbox with Outlook 2003 desktop alert, the customers are immediately informed of any delays without having to open the Access application, a significant savings in time and resources.
To write VBA code to export the information from Access to Outlook, invoke the Code Builder from the Click event of the cmdAlert command button on the Frame Order Form.
In order to manipulate the Outlook object model, choose References from the Tools menu and select the Microsoft Outlook 11 Object Model. You can now manipulate the objects, properties, and methods available within Microsoft Outlook.
Figure 15.3. Figure 15-3
First, declare the various object variables you'll need to work with the Outlook application and e-mail objects.
'First reference the Outlook Application Dim olApp As Outlook.Application 'The NameSpace object allows you to reference folders Dim olApp as Outlook.NameSpace Dim olFolder as Outlook.MAPIFolder 'Create a reference to the e-mail item you will use to send your e-mail Dim olMailItem As Outlook.MailItem
Once you've created the object variables, you can start writing code to use these objects. First, create the Outlook Application object. You can only have one instance of an Outlook Application running at once and so you don't have to worry about using the GetObject method. The CreateObject method works fine for our purposes. Now that you have the Application object, continue by referencing the NameSpace object, setting a reference to the Inbox folder, and adding a new e-mail message (IPM.Note).
Set olApp = CreateObject("Outlook.Application")
Set olNS = olApp.GetNamespace("MAPI")
Set olFolder = olNS.GetDefaultFolder(olFolderInbox)
Set olMailItem = olFolder.Items.Add("IPM.Note")If you want to display the e-mail message on the screen as you're creating it, add the following line to your code. However, unless your users need to manipulate the e-mail message, it's better to keep the message hidden.
olMailItem.Display
The Outlook Mail item has several properties you'll manipulate. Our example changes the Subject, To, Priority, and Body properties as shown in the following code.
Private Sub cmdAlert_Click()
Dim olApp As Outlook.Application
Dim olNS As Outlook.NameSpace
Dim olFolder As Outlook.MAPIFolder
Dim olMailItem As Outlook.MailItem
Dim strBodyText As String
Set olApp = CreateObject("Outlook.Application")
Set olNS = olApp.GetNamespace("MAPI")
Set olFolder = olNS.GetDefaultFolder(olFolderInbox)
Set olMailItem = olFolder.Items.Add("IPM.Note")
strBodyText = "Material for Order #" & Me.OrderNumber & _
" will be delayed until " & Me.DueDate & vbCrLf & _
"Order Due Date: " & Me.OrderDate & vbCrLf & _
"Material Due Date: " & Me.MaterialDueDate & vbCrLf & _
"Action: Inform customer" & vbCrLf & vbCrLf & "-Planning"
With olMailItem
.Subject = "Material Delay for Order #" & Me.OrderNumber
.To = "[email protected]"
.Body = strBodyText
.Send
End With
'Release all of your object variables
Set olMailItem = Nothing
Set olFolder = Nothing
Set olNS = Nothing
Set olApp = Nothing
End SubThe preceding code creates the e-mail message previously shown in Figure 15-3. While that message contains all of the basic information you need to communicate to the Order Entry department, you might want to enhance your code just a bit to add a follow-up flag and a high-priority distinction to your message. Simply add the following lines of code immediately before the .Send line.
.Importance = olImportanceHigh .FlagStatus = olFlagMarked 'Set the flag reminder date for two days in advance .FlagDueBy = Date + 2
You now have working code to create and send an alert e-mail message from the Planning department to the Order Entry department.
15.2.1. Working with Outlook's Security Features
If you've implemented code similar to the preceding example, you've probably noticed a dialog box that pops up when the .Send line is called. This dialog box appears because of new security features introduced within Outlook after the Melissa and ILoveYou viruses. These security measures include two warning dialog boxes. The first appears when you try to manipulate the addresses in your Contacts folder. The second, which is the one you'll run into with the previous code, displays a dialog box warning you that a program is trying to send an e-mail message programmatically. To send the e-mail, you'll need to wait 10 seconds before you can choose to allow the e-mail to be sent. This can be quite annoying for your users and might even make it virtually impossible for your application to work properly. There are a couple of methods you can use to work around this problem.
15.2.1.1. Using an Exchange Server to Configure Security
First, if you're in an Exchange environment (or you know your application will be used with an Exchange Server), you can configure the Administrative Options Package for Exchange Server. This package allows you to allow programmatic sending of e-mail through configuration of a public folder and custom form stored on the Exchange Server. The advantage of this system is that you don't need to touch the client machines at all. Once you install the form within the public folder on the Exchange Server, all you need to do is decide which types of programmatic access you need to allow. You can allow access to the address book, to the Send method, as well as to a variety of other types of settings (such as attachment blocking). The major disadvantage to this method is that unless you're writing code within a COM add-in for Outlook, allowing programmatic sending is an all or nothing proposition. If you ease the restriction for your application, you're also easing the restriction for viruses that use the Outlook object model to propagate. The one saving grace to this problem is that the majority of viruses prevalent these days do not use the Outlook object model, they use their own SMTP (Simple Mail Transfer Protocol) engines to send copies of themselves to others. If you do choose to use the Administrative Options package, make sure that your users have an up-to-date virus scanner both on the desktops and on the Exchange Server.
15.2.1.2. Using Redemption to Save Your Application
The second option you can utilize to prevent the security dialogs involves downloading a third party .dll called Redemption. The Redemption.dll serves as a wrapper for Extended MAPI (messaging application programming interface), another method of creating and sending e-mail messages. Extended MAPI isn't affected by the Outlook security features. The advantage to Redemption is that you can use it only for one application. So merely having the Redemption.dll present on your system poses no security risk. You can use Redemption only when you need it within your code. The major disadvantage to Redemption is that it must be registered on all machines using your application (using Regsvr32.exe). For single users, Redemption is free. If you want a redistributable license for Redemption, it will cost you $99. You can find Redemption at www.dimastr.com/Redemption.
Redemption is very easy to use. Once you've registered it on your system, set a reference to the "Safe Outlook Library". Then make just a few key changes to your code. The following code sample takes the previous listing and rewrites it to use Redemption.
Private Sub cmdAlert_Click() Dim olApp As Outlook.Application Dim olNS As Outlook.NameSpace
Dim olFolder As Outlook.MAPIFolder
Dim olMailItem As Outlook.MailItem
Dim strBodyText As String
'Add a reference to the Redemption Safe Mail Item
Dim objSafeMail as Redemption.SafeMailItem
Set olApp = CreateObject("Outlook.Application")
Set olNS = olApp.GetNamespace("MAPI")
Set olFolder = olNS.GetDefaultFolder(olFolderInbox)
Set olMailItem = olFolder.Items.Add("IPM.Note")
strBodyText = "Material for Order #" & Me.OrderNumber & _
" will be delayed until " & Me.DueDate & vbCrLf & _
"Order Due Date: " & Me.OrderDate & vbCrLf & _
"Material Due Date: " & Me.MaterialDueDate & vbCrLf & _
"Action: Inform customer" & vbCrLf & vbCrLf & "-Planning"
With olMailItem
'Set all properties of mail item here
.Subject = "Material Delay for Order #" & Me.OrderNumber
.To = "[email protected]"
.Body = strBodyText
'Remove the .Send method of the olMailItem to avoid
'security dialogs
End With
Set objSafeMail = New Redemption.SafeMailItem
'Do not need the Set statement here
objSafeMail.Item = olMailItem
objSafeMail.Send
'Release all of your object variables
Set objSafeMail = Nothing
Set olMailItem = Nothing
Set olFolder = Nothing
Set olNS = Nothing
Set olApp = Nothing
End SubWith only a handful of additional lines, you've just bypassed all of those annoying security dialogs.
15.2.2. Creating Other Types of Outlook Items from Access
Creating e-mail messages in Outlook isn't the only way you can use VBA and Outlook to enhance your application. You can create meetings, appointments, tasks, and journal items within Outlook using VBA. The Planning department can create an Outlook task directly from the Access application. This task will remind them to check with the receiving department on the appointed day to ensure the frame material has arrived. If the material hasn't arrived within several days, the Planning department knows that it needs to contact the supplier.
Once you've added another button to your Material Order form, add the following code to the Click event of the command button.
The initial portion of the code is very similar to the code used to create an e-mail message. However, instead of referencing the Inbox folder, set a reference to the Task folder, as shown in the following code.
Private Sub cmdCreateTask_Click()
Dim olApp As Outlook.Application
Dim olNS As Outlook.NameSpace
Dim olfolder As Outlook.MAPIFolder
Dim olTaskItem As Outlook.TaskItem
Dim strBodyText As String
Set olApp = CreateObject("Outlook.Application")
Set olNS = olApp.GetNamespace("MAPI")
Set olfolder = olNS.GetDefaultFolder(olFolderTasks)
Set olTaskItem = olfolder.Items.Add("IPM.Task")
With olTaskItem
.DueDate = Date + 2
.Subject = "Confirm Material Receipt for Order #: " & Me.OrderNumber
.ReminderTime = Date + 2
.ReminderSet = True
.Categories = "Material Order"
.Save
End With
Set olTaskItem = Nothing
Set olfolder = Nothing
Set olNS = Nothing
Set olApp = Nothing
End SubExecuting this code creates a task item like the one shown in Figure 15-4.