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 273 times – 144 KB

Posted in Misc., Random Tidbits
26 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 🙂

  11. Gary Nicol says:

    This is hopefully just what I’m after and got it to work using one account.
    However, i’m trying to expand on this by using it in a multiple front end scenario with many different users.
    In the main forms, buttons direct emails to specific users depending on the result of a txtbox etc.
    How would I get the AccessHyperlink.vbs program to open the correct front end?

    Would every front end need it’s own AccessHyperlink.vbs file or am I thinking about this the wrong way?

    Help very greatly appreciated

    Gary

    • TheSmileyCoder says:

      In my case I have an installer program that ensures that all users have the frontend in the same location. So it does work with multiple (identical) frontends.
      This obviously simplifies it considerably. If you don’t have that option, you could modify it so either each user have their own vbs file, or maybe place the location path of the frontend into a registry on the users machine, and read it back in the vbs file, so you know which frontend to open.
      It might require some tinkering but its doable.

      • Gary Nicol says:

        Thanks for the reply. I must be doing something wrong though.

        In my test DB, I can get it to work sending an email to myself but if I send to another user it says cannot find script file.

        I’ve tried creating a vbs file for each user but I get the same error.

        • TheSmileyCoder says:

          In my example the VBS file is on a network share. If your user(s) doesn’t have the network share mapped to the same Letter, that might be causing you issues?

          • gary nicol says:

            think my problem is the front ends are unique to each user.

          • TheSmileyCoder says:

            Are they copies of the same frontend, but “installed” in different local paths?
            I.e. user Jane might have it on her desktop, while user John might have it in his documents folder.

            Or are they truly different?/used for different purposes?

          • Gary Nicol says:

            All the users have the network share mapped to same drive.
            The problem seems to be the system knowing which vbs file to use.
            A user will click a button to send an email and depending on the results of a textbox, it will fire the email of to another user. this could change the next time they hit the button again.

            Hope I’m making sense

          • Gary Nicol says:

            Each user has a different front end, in that they have different forms or buttons depending on what their job is.

            All of the front ends are located on a network share (mapped to same drive on user pc). This is reached via a shortcut on the users desktop.

  12. Gary Nicol says:

    Unfortunately I can’t make this work for my situation.
    It’s a shame as it works great in standard format.

    • TheSmileyCoder says:

      Sorry to hear that, you couldn’t get it to work with your setup. Hope you find something else that works for your requirements.

Leave a Reply

Your email address will not be published. Required fields are marked *

*