Open Access and run macro from Outlook email link.

[Poduska]

My final objective is:

I am attempting to place a link into an email which will open a database form to a specific record. The email receiver would click the link, the form would open and then perform some action on the record.

I can create an email and insert a hyperlink but cannot get the hyperlink to run the macro. I am not sure the macro is the best way to go but I haven't made it far enough to test.

I can get my outlook link to open a database, I used a very simple example as a proof of concept.

I created a link in outlook of

Outlook hyperlink, no quotes, just text
C:\northwind.mdb

this opens northwinds just fine

If I hover over the hyperlink in outlook I see
file///c:/northwind.mdb

Now I attempt to add the macro using the sample from

http://support.microsoft.com/kb/209207/en-us

I edit the email link with

C:\northwind.mdb /x macrun

with out any quotes and resend.

When I open the new email

If I hover over the hyperlink in outlook I see
file///c:/northwind.mdb%20/x%20macrun

When I click on the hyperlink with the macro I receive the message

Cannot Find File
'C:\northwind.mdb \x macrun'
make sure the path or internet address is correct.

I have tried many examples but can never get a macro or code to execute when I use a link.

I have been searching but not found an answer. Has anyone tried this before?

For the short term I am stuck with 2003 as we are just starting to roll out 2007. (I know, why not 2010)

thanks for any insight.

 

[PHV]

The target of the hyperlink should be something like this:
\path\to\msaccess.exe /x macrun C:\northwind.mdb

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Poduska]

Well, it is not a direct method but it does work. I use an autoexec macro and a public module to allow me to open the database to a specific form which shows a specific record.

"C:\Program Files\Microsoft Office\Office11\Msaccess.exe" "c:\northwind.mdb" /cmd = "Employees 000005"

This passes the form I wish to open along with a value for the ID of the record I wish to show.

NEXT,
I had to create a module which is called by an "autoexec" macro.

Much thanks to

http://www.hyteware.com/msaccess_help/desktop_shortcut_to_msaccess_form.html

for the bit of code which I modified a bit to add the individual record.

Module_Our_Command_Parser

'   Sample calling string "C:\Program Files\Microsoft Office\Office11\Msaccess.exe" "c:\northwind.mdb" /cmd = "Employees 000005"
Public Function CheckCommandLine() As Boolean
 Debug.Print CheckCommandLine
 
  CheckCommandLine = True
  
  Debug.Print "Command is "; Command
  If Len(Trim(Command)) = 0 Then Exit Function ' this is a regular open
  
  Dim iPos As Integer
  Dim sMyCommand As String
  Dim intRecordPointer As Integer
  
  iPos = InStr(Command, "=")
  
  sMyCommand = Trim(Mid(Command, iPos + 1))
  sMyCommand = Mid(sMyCommand, 2)
  iPos = Len(sMyCommand)
  
  If iPos < 0 Then iPos = 1
  sMyCommand = Mid(sMyCommand, 1, iPos - 1)
  
Debug.Print "Right char = "; Right(sMyCommand, 1)

intRecordPointer = Val(Right(sMyCommand, 6))
Debug.Print "Integer = "; intRecordPointer

Select Case Left(sMyCommand, 9) 'sMyCommand

          Case "EMPLOYEES"
                DoCmd.OpenForm "employees", , , "EmployeeID = " & intRecordPointer & ""
                Debug.Print "Selected first case "; intRecordPointer
        
        Case "OPEN_FOR_ANYCOMMAND"
                DoCmd.OpenForm "Employee_Form"
        Case "MORE_OPEN_FOR_ANYCOMMAND"
                DoCmd.OpenForm "Employee_Form"
        Case Else
                DoCmd.OpenForm "switchboard"
End Select

End Function

The code has 2 other places in the case statement for additional actions. In reality this method would allow you to pass any number of parameters as start up values which you can manipulate in the code as I have done with the EmployeeID.

I'm sure others could polish this up better but it will work for my immediate needs.

The only problem I have experienced is some individuals at our location do not run 2003 and have pop-ups when the database is opened and code is then run.

 

[Poduska]

RATS.

This command works PERFECT if I use START\RUN

"C:\Program Files\Microsoft Office\Office11\Msaccess.exe" "c:\northwind.mdb" /cmd = "Employees 000005"

But I receive "Cannot open the Specified File" from a hyperlink.

Funny, why would Word\Outlook remove the beginning and ending double quote? This is what is saved as the actual hyperlink

C:\Program Files\Microsoft Office\Office11\Msaccess.exe" "c:\northwind.mdb" /cmd = "Employees 000005

[red]
How can I make the hyperlink save the beginning and ending double quotes?[/red]

Thanks

 

[Hap007]

Not sure if this works but you might try a 'Single' quote

'C:\Program Files\Microsoft Office\Office11\Msaccess.exe' 'c:\northwind.mdb' /cmd = 'Employees 000005'


Hope This Helps,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121