Send links via Outlook email

September 19, 2008 – 11:18 am by JP

---

    If you work in an office that uses public network storage (i.e. mapped network drives that everyone in your office shares access to), then you'll appreciate this next bit of code.

    The page on sample Outlook automation from Excel shows some sample VBA code in Excel for creating an Outlook mail message and displaying it for editing/sending.

    Here is some additional sample code that uses the "Create Outlook E-Mail Message (From Excel) - Late Binding" sample from that page:

    First we'll improve the code slightly, to include some error checking in case (for some reason) we cannot start Outlook. If we do have to start Outlook, we'll check at the end of the sub and quit that instance. I'll apply the campsite rule to programming: leave the end user's computer as good as or better than when you found it.

    You've seen it before on this blog, in the code from my defunct add-in that pulls tasks/appointments from Outlook into Excel -- if we start Outlook programmatically, then we need to close it, to leave the end user's computer unaltered after the code is finished, as if the code had never been run.

    Here is the updated code:

    Now we'll add the code to manipulate the properties of the Msg object, to include a link to an imaginary file on our company network. When the recipients clicks on the link, they'll open a copy of the file on their computer. This might be a link to a report you generate, an updated document, etc.

    Since I am setting the HTMLBody Property, I use HTML tags inside double quotes to set up the email to look the way I want. The most important thing to remember is the HTML tags have to be inside the double quotes, along with the other text.

    In my case, I set the email to use a 10 point Verdana font, which is my favorite (YMMV). The SPAN tag currently is the proper way to do it (someone correct me if that is wrong). The path and filename are in quotes, and the display name (what the end user actually sees when they receive the email, is between the <a> and </a> tags. It helps if you know HTML & JavaScript, as you can even include JS in your emails, to create buttons, popups, etc. And if I could find the link, I would have posted the sample code to do this.

    Anyway, here's what the output looks like:

    You'll notice that the actual filename and path are hidden by a 'friendly' name, just like you can do with any HTML link. That way you can hide the ugly, sometimes unreadable network path links with a professional looking display name.

    If you did want to include the filename as the display name, just change the link line to:

    That would make the display name and the link source equal to the same thing. Ugly if you ask me, I'd avoid this unless you like making bad impressions

    We can get as creative with this as we want. If you needed to use variables instead of hard-coding the filename/path info, it would look something like this:

    Where strDirectory is the folder name, strFName is the filename (assume Excel, but you can change it depending on the file), and strDisplay is the display name you want to use.

    In my example above, the folder name is date-based (Jan2008), so if you were sending this as a recurring email, you could use the Now or Date function to dynamically change the folder depending on when the email goes out. For example:

    This is great as part of an auto-report-generating process, where you create the folders for each month. The filename would always point to the most current folder.

Enjoy,
JP

 Print This Post  |   Email This Post  |  Permalink  |   Subscribe now!

Filed Under: Excel, Outlook, VBA, automation
Tags: Excel, hyperlink

This post has 564 views since September 19, 2008 – 11:18 am.