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.

Startj I & oil ©IP 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.


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 & _


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 Sub

"Action: Inform customer" & vbCrLf & vbCrLf & With olMailItem

.Subject = "Material Delay for Order #" &

The 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.

0 0

Post a comment