Access Email with Hyperlink to record

A couple of days ago I posted about ways to get an email hyperlink to activate your access database and navigate to a specific record.

It was a bit cumbersome because it had to use both batch files and vbs files as intermediate steps, and to make matters worse, it required a single batch file per record you wanted to link to. It wasn’t pretty but it was the best I had managed to make in order to actually getting record hyperlinks working.

A fellow MVP, Ben Clothier, was reading my article and trying to think of ways to improve upon it. He came across this article and mailed it to me :

Registering an Application to a URI Scheme

 

Basically, if you have ever wondered how Windows knows to activate Chrome, Internet Explorer or something else, here is the answer. It is specified in the registry. If we take a look in the registry we see:

httpRegistryEntry

 

Now whenever a http link is clicked, the information here tells windows to fire up “C:\Program Files (x86)\Google\Chrome\Application\chrome.exe” and pass it the %1 parameter (Which would be the web page address) . From there its up to chrome to load the webpage and display it.

 

Now the great thing, is that according to the link from Ben Clothier, we should be able to do the same thing. It took a bit of playing around to get the syntax right, because an access app isn’t an executable file. Now we could maybe use msAccess.exe and pass it both the path of our application as well as the parameter (The recordID) that we want to look at, but there are a few issues with that as well. First we might not have the full path of the access installation, and also for the /cmd switch on access to work the application has to not be running already. So instead of trying to use the url to activate access directly I opted for using wscript to activate a vbscript file. Its actually nearly the same vbscript file I used for my original post about hyperlinking to records. More about that later.

So I went into my registry and added the following keys to the HKCU (HKEY_CURRENT_USER).

After a wee bit (well probably an hour) of playing around I had weeded out the issues. A typo I had made in the registry made me go nearly mad before I got it working. That and parts of the URL I used within the email contained special characters (< and >), and those were getting mangled by the process (Being replaced with “%3e”.  I have now for simplicity left them out, otherwise it can be dealt with by some search and replace.

Now when an email is sent, the hyperlink looks like this:

<a href:”ComToolLinkHandler:718″>Record 718</a>

which of course when formatted by the email handler just shows as “Record 718” as a hyperlink (Usually blue and underlined).

The vbs file is the same as we used in the first hyperlink example I posted. The vbs file seems to give the most flexibility. Once I realized we could do this application URL registration I also went and tried to register that the link should be handled directly by the access application, through the use of the /cmd switch. This does work, but the issue is that we end up with multiple open access applications, which isn’t really ideal. If the types of link you send you is of the kind where you login, do something and close again, then you could use the direct access approach. But I really favor the vbs solution, and the flexibility it gives to handle whether the database is already open.

I have combined all 3 solutions (URL protocol Direct Access, URL protocol to vbscript file, and batch file creation)  into a single zip file. It includes both the database, and the vbs file.

In the zip file look for “Hyperlink Example by TheSmileyCoder.accdb”. In a “real” production scenario, you would probably want to move the vbs file to a network location and update paths accordingly. I’ve done some extra “stuff” to make the hyperlink example independent of what folder you put the demo in. Again in production, you might want to do that differently.

 

NOTE: You must rename the file AccessHyperlink.RenameToDotVbs to AccessHyperlink.vbs for the demo to work. Chrome kept blocking the download, due to the vbs file in it.

Download “Hyperlink Demo File” Hyperlink-Example.zip – Downloaded 213 times – 144 KB

Posted in Misc., Random Tidbits
15 comments on “Access Email with Hyperlink to record
  1. alancossey says:

    Anders,
    This is excellent!
    Have you tried creating the .vbs file on the fly? That way there would be no need to install any files at all.

    Alan Cossey

    • TheSmileyCoder says:

      Hi Alan
      I haven’t specifically tried for the vbs file, but yes it is possible. Its just a text file after all. The demo shows how the .bat file is created, and the same techniques could be used for the vbs file. That said, I wanted to keep the demo file focused on the concept it was trying to demo. Initially I had included some fancy html table formatting to be used in the email, but I removed that as I found it might distract people from the “real” point I was trying to make.
      Thank you for your input.

  2. alancossey says:

    Anders,
    Agreed. Good point.

    I also wondered about it having just the primary key showing in the message, e.g.

    Please could you review the record:0001

    The 0001 could be expanded upon to be “0001:Need to buy new widget” or whatever and parse out the 0001 in the .vbs file.

    Lots of possibilities. It is great. A customer wanted this functionality a few years ago. I lost him, but have recently got him back so this may well be useful for me in the coming months.

    Alan

    • TheSmileyCoder says:

      Well just like with regular links, this link consists of 2 parts. The actual link, and then display part of the link. So you could display anything, and not even need to parse it out.

  3. Giorgio says:

    So, if I take your hyperlink as an example
    Record 718
    I guess you’d have to do it like so http://1drv.ms/1SyXihK in Outlook.

    • TheSmileyCoder says:

      Yes, if you are using tag in xml style like I am. But the demo file just uses a simpler form of formatting the link.

  4. grovelli says:

    How do we get the ComToolLinkHandler shown in the registry picture?

    • TheSmileyCoder says:

      ComToolLinkHandler is the name of the reqistry key to handle the click event. It is not an object in itself. Its been a while since I wrote this,but I believe its covered by the download as well.

  5. grovelli says:

    I don’t see any such key in my pc registry nor do I see it covered in HyperLink Example By TheSmileyCoder.accdb

  6. TheSmileyCoder says:

    That is just what I choose to call it. The application for which this was intended is called “ComTool” (Short for commenting Tool), so I figured it was fitting to call it ComToolLinkHandler. You would need to setup your own registry entries to match your requirements.

  7. grovelli says:

    But if I want to use your HyperLink Example By TheSmileyCoder.accdb as an example and add the following to my pc registry:

    [HKEY_CURRENT_USER\Software\Classes\ComToolLinkHandler] @=”URL: ComTool Link Protocol”
    “URL Protocol”=””

    [HKEY_CURRENT_USER\Software\Classes\ComToolLinkHandler\Shell] @=””

    [HKEY_CURRENT_USER\Software\Classes\ComToolLinkHandler\Shell\Open] @=””

    [HKEY_CURRENT_USER\Software\Classes\ComToolLinkHandler\Shell\Open\Command]
    @=”wscript.exe G:\DATA\\METRO-TS\ComTool\LinkManager\Vbs\LinkManager.vbs “%1″”

    Will it work since I don’t see ComToolLinkHandler mentioned anywhere in the VBA code associated with HyperLink Example By TheSmileyCoder.accdb ?

  8. grovelli says:

    I think I’ve got it: what escaped me was calling a registry key from a href as in Record 718

    and I guess you can use either colon or equals after href.

  9. grovelli says:

    So now I click on the button labelled “Email this record (URL Through VBS)” on frmRecord and I get the proper message in an Outlook email:
    Dear Boss

    Please could you review the record:0001
    Best regards

    g.rovelli

    but when I click on the hyperlink it asks me to choose among a lists of applications(Paint, Word, Adobe etc) to open the file and Access isn’t in the list.
    Is there a way to add it to the list?

  10. grovelli says:

    Never mind. I’ve tried it on my laptop and clicking the record link in the email opens Access to the right record 🙂

Ask a question, leave some feedback, post a reply!