Using VBA to insert hyperlink to local file in outlook email

Posted on 2005-05-05
Medium Priority
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 = "somebody@myfirm.com"
    .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

David Lee earned 2000 total points
ID: 13937792
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.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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 process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses

615 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