Macro to open Access from Appointment form?

  • Thread starter Thread starter deko
  • Start date Start date
D

deko

Can I open an Access database from the Outlook Calendar Appointment form
using a macro?

For example, when a user opens an existing Appointment Item from the Outlook
Calendar, there is a button I've placed on the toolbar of the Appointment
form that launches the below macro (mostly pseudo code).

As you will see from my pseudo code, I want to open Access and pass the
value from the Appointment Item's Location field to MyDatabase.mdb, which
will result in the database displaying the corresponding record for that
location. Can this be done with a macro?

Thanks in advance and Merry Christmas!

[macro code for button on Appointment form]
Public Sub OXP()
'Required reference: Microsoft Access 11.0 Object Library
On Error GoTo HandleErr
'Dim appt As AppointmentItem
'Set appt = AppointmentItem
'Debug.Print Location.Value
Debug.Print Item.Location '???
'Dim acapp As Access.Application
'Set acapp = GetObject(, "Access.Application")
'acapp.Visible = True
'how to open database: C:\My Documents\MyDatabase.mdb ?
'how to pass location value to Access?
Exit_Here:
On Error Resume Next
Set acapp = Nothing
Exit Sub
HandleErr:
Select Case Err.Number
Case 429
Set acapp = New Access.Application
Resume Next
Case Else
Debug.Print "Error Number " & Err.Number & ": " &
Err.Description
End Select
End Sub
 
Can I open an Access database from the Outlook Calendar Appointment form
using a macro?
Click to expand...

The below code seems to work.

But 2 issues remain:

1. What is the best way to store strMdb so it will be globally available
within Outlook?
2. How to bring the Access application window to foreground after running
the macro?

Public Sub ViewEntity()
On Error GoTo HandleErr
Dim acapp As Access.Application
Dim strMdb As String
Dim lngEid As Long
If IsNumeric(ActiveInspector.CurrentItem.BillingInformation) Then
lngEid = ActiveInspector.CurrentItem.BillingInformation
Set acapp = GetObject(, "Access.Application")
strMdb = "C:\MyDatabase.mdb"
'what is the best way to store strMdb so it will be globally
available within Outlook?
acapp.OpenCurrentDatabase strMdb
DoEvents
acapp.Run "GoToEidFromOutlook", lngEid
'how to bring Access application window to foreground?
Else
Call ChangeEntity
End If
Exit_Here:
On Error Resume Next
Set acapp = Nothing
Exit Sub
HandleErr:
Select Case Err.Number
Case 429
Set acapp = New Access.Application
Resume Next
Case 7867 'You already have the database open
Resume Next
Case Else
Debug.Print "Error Number " & Err.Number & ": " &
Err.Description
End Select
End Sub

Public Sub ChangeEntity()
Dim strAssigned As String
Dim varResponse As Variant
Dim varEid As Variant
If IsNumeric(ActiveInspector.CurrentItem.BillingInformation) Then
varEid = ActiveInspector.CurrentItem.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 has not been assigned to an OXP
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 "
& _
"Organize XP before this appointment can be viewed from Organize
XP.", _
" Enter Organize XP Entity ID", varEid)
If Len(varResponse) <> 0 Then
If IsNumeric(varResponse) Then
ActiveInspector.CurrentItem.BillingInformation = varResponse
MsgBox "Appointment assigned to Eid " & varResponse
Else
MsgBox "Invalid Entity ID"
End If
Else
ActiveInspector.CurrentItem.BillingInformation = ""
MsgBox "This appointment is not associated with an OXP entity."
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
 
Store the string as a Public variable declared in the
ThisOutlookSessionModule. That would make it available to code in any module
or class.

Use Access.Application.hWndAccessApp to get an hwnd to the Access
application. Then use the Win32 API call SetForeground window using that
hwnd. Make sure to call GetForegroundWindow first and preserve that hwnd so
you can later reset the window ZOrder when you're done.
 
Store the string as a Public variable declared in the
ThisOutlookSessionModule. That would make it available to code in any
module or class.
Click to expand...

Sounds good - I'll look into this. The goal is to avoid problems when the
path to the mdb changes.

Users launch the OL Calendar as well as export appointments to it from the
Access app, so I want to update the mdb path variable on these events. As
it is now, the path is hard-coded in the macro.

One thing I've learned about using a macro (as opposed to an Outlook Add-in)
is that it's not easy to distribute...
Use Access.Application.hWndAccessApp to get an hwnd to the Access
application. Then use the Win32 API call SetForeground window using that
hwnd. Make sure to call GetForegroundWindow first and preserve that hwnd
so you can later reset the window ZOrder when you're done.
Click to expand...

10-4. I found some code to do this. Works great.

Thanks for the reply.
 
Macros aren't at all the best way to distribute code. COM addins are much,
much better.
 
Back
Top