Email Item as Link from Access

[TheSmileyCoder]

@ NeoPa: Your excel idea is nice thinking, however I am pursuing the outlook way. I think using excel as a hyperlink is a bit confusing to the users :P

I think I have something decent cooked up so far, though its quite different from coding in Access at least in the way I am used to.

So far, I have written code that enables/disables the toolbar based on a few parameters tied to the SelectionChange event for the ActiveExplorer. Item must be in the inbox folder, and must be a mail item (not a task/appointment...s o on). Now too this, I will need to add something that checks for certain charecters/codes in the email body, which I have not yet decided on. Im still mostly in the feasibility study of this project. However its fairly easy done, standard string manipulation.

The toolbar once activated will read/parse the email, and look for whatever code I decided to use, and based on that open the database, probably by automation, or by shell command with command line parameters.


My main concern now lies in distribution. I don't so much mind going around to the 20-50 users and installing some extra code in their outlook, is the maintenance of said code that bothers me more. I will have to look into both how to distribute the code, as well as update it. That however is a probably a matter for another question.

[TheSmileyCoder]

I do see that your excel suggestion does have the benefit of not having to go around to each outlook installation and managing software there. I will give it some more thought...

[TheSmileyCoder]

I just want to tack this onto the thread before I forget it. You can get the MSAccess.exe path from the excel file if you have set a reference to the Access object model, and then use:

SysCmd(acSysCmdAccessDir)

Users must also allow macros, in order for the excel code to run. I think a potential problem with the Excel solution is that we will soon be upgrading to Office 2010, and I fear that I will have to look into alot of macro security issues at that time.

[Mariostg]

What if another approach is considered? No Outlook coding, no Excel. From the original posting:

1.  Open the access front-end on the users PC.
2.  Open relevant form
3.  Goto relevant Item

1. This can be done, no problem

Now in the Access application, suppose you have an ActionItems table containing fields such as username, actionID, isVerified. This table would be maintained by The Smiley.

When the recipient gets the email, the hyperlink is clicked, Access starts and goes to the specific form (Stage 2) containing the ActionItems records filtered for the user. The user clicks the actionID which in turns opens the form containing the items to review (Stage 3).

So one click to start access, one click to go to each item to review, maybe on click to mark the item reviewed...

[ADezii]

@TheSmileyCoder :
Two last questions:

1. Do you have access to the User's PCs on a Network?
2. Do you have Full Rights to the Folder where the User's Database resides?

[TheSmileyCoder]

@Mariostg: I allready have a installer (basicly another mdb file) that users open to install the main application. This also creates a shortcut on their desktop. (Thats the link part). The application allready has a form called "My Reviews", which shows the reviews assigned to each user, sorted by review status, with buttons to open each review (open form and goto specific item part).
Your suggestion (While appreciated, don't misunderstand me!) adds nothing new really. In short you could say the functionality missing is 1 Email=1 itemLink.

The hyperlink in the email is really just a nice to have feature.

@Adezii1) No not directly. By that I mean that I cannot sit from my own PC and remotely interact with theirs. I can go shove them off their chair and do work at their workstation. I am not IT savvy, my main expertise lies in Access and Railway Safety (odd combo).
That said, each user has their own frontend which gets updated automatically when the user starts an "old" frontend. So if I can add the code to the frontend, You might say that through MSAccess I get access to their PC, and If I can code it in access it can run on their PC with "their" rights.

2)Same as 1)

[NeoPa]

Smiley:
I just want to tack this onto the thread before I forget it. You can get the MSAccess.exe path from the excel file if you have set a reference to the Access object model, and then use:

SysCmd(acSysCmdAccessDir)

Excellent. I didn't know about this and I've done various projects (and other questions on here) where this would have proven very useful. I was assuming for a while (before my brain kicked in) that this would be handled by the code at Smiley's end that prepared the command line itself (in cell A1), but then I realised that Smiley's setup may be different from any of his users so it would, indeed, need to be incorporated into the Excel code after all. It's a very worthwhile piece of code though and I was happy to add it to the workbook already provided :-)

NB. In the code below a deliberate choice was made to use late-binding (IE. No Library Reference was made to any version of Access). This is to reduce any likelihood of problems handling potentially various versions on the target PC. It explains the need for the definition of conAccessPath, which is a duplication of acSysCmdAccessD ir which would otherwise have been available.

Option Explicit

Private Const conAccessPath As Long = 9

Private Sub Workbook_Open()
    Dim strMsg As String, strCmd As String, strAccessPath As String

    With CreateObject(Class:="Access.Application")
        strAccessPath = .SysCmd(conAccessPath)
        Call .Quit
    End With
    strCmd = Range("A1")
    strCmd = Replace(strCmd, "%AccessPath%", strAccessPath)
    strCmd = Replace(strCmd, "%ProgramFiles%", Environ("ProgramFiles"))
    strMsg = "You are about to run the command :%L%L%C"
    strMsg = Replace(strMsg, "%L", vbNewLine)
    strMsg = Replace(strMsg, "%C", strCmd)
    If MsgBox(Prompt:=strMsg, _
              Buttons:=vbOKCancel Or vbQuestion, _
              Title:="RunCommand") = vbOK Then _
        Call Shell(PathName:=strCmd, WindowStyle:=vbMaximizedFocus)
    If Application.Workbooks.Count > 1 Then
        Call Me.Close
    Else
        Call Application.Quit
    End If
End Sub

Smiley:
Users must also allow macros, in order for the excel code to run. I think a potential problem with the Excel solution is that we will soon be upgrading to Office 2010, and I fear that I will have to look into a lot of macro security issues at that time.

Indeed. You will find this precondition referred to in post #44.

My own preferred solution to users running my code, without compromising their macro security generally, is to sign all my projects. Information on how this may be done for free can be found at Code Signing.

[Mariostg]

@TheSmileyCoder

Your suggestion (While appreciated, don't misunderstand me!) adds nothing new really

Don't worry. I did not loose any sleep on this. You are spoiling your users. I hope they see and recognize the efforts you are investing :).

[NeoPa]

Lol Mario :-D

It never hurts to let the users think it's for their benefit, but we all understand it's mainly to stop all the calls back to you when they manage to muck (was that an 'm'?) up even the simplest procedures and break even the most robust systems. The less scope they have for damage the better I sleep :-D

PS. I see one of the big benefits of contributing to a forum site such as this is that we can all pick up from, and use for ourselves, some of the ideas and procedures that get developed to handle questions such as these. I certainly intend to add this one to my arsenal, and I'd never considered this type of problem before.

[TheSmileyCoder]

I finally figured this out, many years later, and blogged about. You can find the blog post here:

http://thesmileycoder.com/access-email-with-hyperlink-to-record/