Using VBA to insert hyperlink to local file in outlook email

Posted on 2005-05-05
Last Modified: 2008-01-16
I've written a VBA macro for MS Word to create an email in Outlook. It's supposed to create a hyperlink in the body of the email, but does not. The relevant portion of the code is at the end of this email. The macro grabs the full path & file name of the current file and stores it as a variable (currname) with all the spaces replaced with "%20". If I paste that variable in an email AND THEN SPACE WITH THE SPACEBAR, it creates a hyperlink which you can click on. The macro inserts the text, but it doesn't trigger outlook to turn it into a hyperlink.

Q. -- what can I do to make sure that a hyperlink is created?

I'm trying to automate a work-flow system for a small workgroup where critical documents are proofread by a co-worker before release. The intent of the macro is to automate sending an email which contains a hyperlink that the coworker can click on to open the document. We don't want to email the actual document around.

Dim currname As String
currname = Replace(ActiveDocument.FullName, " ", "%20")
Dim oOutlookApp As Outlook.Application
Dim oItem As Outlook.MailItem
Set mydata = New DataObject
Set oOutlookApp = GetObject(, "Outlook.Application")
Set oItem = oOutlookApp.CreateItem(olMailItem)
With oItem
    .BodyFormat = olFormatHTML 'Set the recipient for the new email
    .To = ""
    .Subject = "PROOFREAD"
    .Body = "Please click this link " & currname & " to open this document. Please proofread it and correct any errors, then print and bring to me for signature."
End With
Question by:GrayStrickland
    1 Comment
    LVL 76

    Accepted Solution

    Hi, GrayStrickland.

    At first glance I see two issues.

    1.  Although you've set .BodyFormat to olFormatHTML, you put the text in .Body which is the textual body.  It should go in .HTMLBody instead.

    2.  You inserted the document's name, but not as a hyperlink.  That means it''s being seen as text.  The format for a link is:

       <a href="TheLink">some text</a>

    So, you might use something like this:

        .HTMLBody = "Please click this <a href='file://" & currname & "'>link</a> to open this document. Please proofread it and correct any errors, then print and bring to me for signature."

    Remember that sending a file link is going to require that the link be to an area that the mail recipient has access to.  Otherwise they'll click on the link and get an error.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Introduction This article makes the case for using two modules in your VBA/VB6 applications to provide both case-sensitive and case-insensitive text comparison operations.  Recently, I solved an EE question using the LIKE function.  In order for th…
    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now