You can either use VBScript code within the form to handle the Item_Open
event or you can use a COM addin and handle the NewInspector event for
the Inspectors collection, then check the newly opened Inspector to see
if the item in it is something you want to handle. COM addin code can be
written in any high-level language, it's not restricted to VBScript and
the Notepad-like forms editor.
Hi Ken, and thanks for another well-written and insightful reply. I've
been trying to avoid COM add-ins, nor is VBScript my favorite way to go.
The functionality I'm after is the ability to click a button from the
Message or Appointment form's menu bar and go to the Access database
record associated with that Mail or Appointment item. You can see what
I've come up with below.
What I've done is put all the code behind a macro button on the menu bar.
Everything happens when this is clicked (there's no real need to store the
Entity_ID in MailItems, as is done with AppointmentItems). The tricky
part is when Access in not already open. Clearly, the Access app in
question must be opened before I can do anything with it. But there is
code in the Open event of the start up form in the app. This code
positions the application window on the screen with an API call and brings
it to the top of the Z-order.
Ideally, I'd want Access to open in the background and become visible only
if a match is found. But because of the code in the startup form, the app
window has to become visible, then check for a match, and then minimize if
no match (this only happens if Access is not already open). Otherwise
things are working pretty well.
[macros in Outlook]
Option Explicit
Private Const DBPATH As String = "C:\MyDatabase.mdb"
Private Const QT As String = """"
Public Sub ViewEntity()
'Required reference: Microsoft Access 11.0 Object Library
On Error GoTo HandleErr
Dim acapp As Access.Application
Dim objItem As Object
Dim varEid As Variant
Dim blnNewInstance As Boolean
Set objItem = ActiveInspector.CurrentItem
If IsNumeric(objItem.BillingInformation) Then
varEid = objItem.BillingInformation
ElseIf TypeOf objItem Is MailItem Then
varEid = objItem.SenderEmailAddress
'returns zero length string if empty
End If
If Len(varEid) = 0 Then
Call ChangeEntity
Else
Set acapp = GetObject(, "Access.Application")
acapp.OpenCurrentDatabase DBPATH
If Not IsNumeric(varEid) Then
varEid = acapp.DLookup("[Entity_ID]", "tblEmail", _
"[EmailAddress] Like " & QT & varEid & "*" & QT)
'returns Null if no match
End If
If IsNull(varEid) Then
If blnNewInstance Then acapp.DoCmd.RunCommand acCmdAppMinimize
DoEvents
'new instance needs to be minimized because code that runs
'when start up form opens positions application window
Else
acapp.DoCmd.RunCommand acCmdAppRestore
ActiveWindow.WindowState = olMinimized
ActiveExplorer.WindowState = olMinimized
DoEvents
acapp.Run "GoToEidFromOutlook", CLng(varEid)
End If
End If
Exit_Here:
On Error Resume Next
Set objItem = Nothing
Set acapp = Nothing
Exit Sub
HandleErr:
Select Case Err.Number
Case 429 'ActiveX component can't create object
Set acapp = New Access.Application
blnNewInstance = True
Resume Next
Case 7867 'You already have the database open
blnNewInstance = False
Resume Next
Case 7866 'Microsoft Office Access can't open the database because
it is missing...
MsgBox "Error opening '" & DBPATH & "'" & vbCrLf &
Err.Description
Case Else
Debug.Print "Error Number " & Err.Number & ": " &
Err.Description
End Select
End Sub
Public Sub ChangeEntity()
On Error GoTo HandleErr
Dim objItem As Object
Dim strAssigned As String
Dim varResponse As Variant
Dim varEid As Variant
Set objItem = ActiveInspector.CurrentItem
'only change Entity ID of AppointmentItems
If TypeOf objItem Is AppointmentItem Then
If IsNumeric(objItem.BillingInformation) Then
varEid = objItem.BillingInformation
strAssigned = "This appointment is currently assigned to Entity
ID " & _
varEid & vbCrLf & vbCrLf & "Enter a different Entity ID to
" & _
"reassign this appointment, or click 'Cancel' to unassign."
Else
strAssigned = "This appointment is not assigned to an Entity."
& _
vbCrLf & vbCrLf & " Enter an Entity ID to assign this
appointment."
End If
varResponse = InputBox(strAssigned & vbCrLf & vbCrLf & "After
entering an " & _
"Entity ID, the Outlook Calendar must be closed and reopened
from " & _
"Access before this appointment will be updated in Access.", _
" Enter Entity ID", varEid) 'InputBox default value is varEid
If Len(varResponse) = 0 Then 'cancel button returns nullstring
ActiveInspector.CurrentItem.BillingInformation = vbNullString
If Len(varEid) <> 0 Then 'message only if appt was previously
assigned
MsgBox "This appointment is not associated with an Entity.
", _
vbInformation
End If
Else
If IsNumeric(varResponse) Then
objItem.BillingInformation = Trim(varResponse)
Else
MsgBox "Invalid Entity ID. ", vbExclamation
End If
End If
End If
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
Debug.Print "Error Number " & Err.Number & ": " &
Err.Description
End Select
End Sub
