Send links via Outlook email
September 19, 2008 • JP • No Comments • Rate This Article
• Links to this article
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:
Dim olApp As Object
Dim Msg As Object
Set olApp = CreateObject("Outlook.Application")
Set Msg = olApp.CreateItem(0)
Msg.Display
Set Msg = Nothing
Set olApp = Nothing
End Sub
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:
Dim olApp As Object
Dim Msg As Object
Dim bWeStartedOutlook As Boolean
Application.ScreenUpdating = False
Application.StatusBar = "Creating email ..."
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If Err.Number <> 0 Then
Set olApp = CreateObject("Outlook.Application")
bWeStartedOutlook = True
End If
On Error GoTo 0
If olApp Is Nothing Then
Application.StatusBar = False
MsgBox "Cannot start Outlook. Make sure Outlook is installed and can be run manually from this computer.", vbInformation
GoTo ExitProc
End If
Set Msg = olApp.CreateItem(0)
Msg.Display
ExitProc:
Set Msg = Nothing
' we need to call the Quit method, to quit Outlook if we actually started it ourselves
If bWeStartedOutlook Then olApp.Quit
Set olApp = Nothing
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub
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.
Dim olApp As Object
Dim Msg As Object
Dim bWeStartedOutlook As Boolean
Application.ScreenUpdating = False
Application.StatusBar = "Creating email ..."
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If Err.Number <> 0 Then
Set olApp = CreateObject("Outlook.Application")
bWeStartedOutlook = True
End If
On Error GoTo 0
If olApp Is Nothing Then
Application.StatusBar = False
MsgBox "Cannot start Outlook. Make sure Outlook is installed and can be run manually from this computer.", vbInformation
GoTo ExitProc
End If
Set Msg = olApp.CreateItem(0)
With Msg
.Subject = "Whatever subject line I want"
.HTMLBody = "<span style=""font-family : verdana;font-size : 10pt""><p>Hello,</p>" & _
"<p>Below is a link to a file on the company network.</p>" & _
"<p><a href=" & Chr(34) & "G:\Dir\MyFolder\CompanyInfo\Jan2008\DataFile.xls" & Chr(34) & ">DataFile</a></p>" & _
"<p>Thank you,<br />Jimmy Pena</p></span>" & "<br />"
.Display
End With
ExitProc:
Set Msg = Nothing
' we need to call the Quit method, to quit Outlook if we actually started it ourselves
If bWeStartedOutlook Then olApp.Quit
Set olApp = Nothing
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub
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 and 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.
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:
"G:\Dir\MyFolder\CompanyInfo\Jan2008\DataFile.xls" & _
Chr(34) & _
">G:\Dir\MyFolder\CompanyInfo\Jan2008\DataFile.xls</a></p>" & _
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:
strFName & ".xls" & Chr(34) & ">" & strDisplay & _
" </a></p>" & _
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:
Format(Now, "mmmyyyy") & "\DataFile.xls" & Chr(34) & _
">DataFile</a></p>" & _
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
Automation, Outlook, VBA ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: A simple debug mode for your Excel workbooks
Next Post: Hold that email!
big thnx, u saved my day.
i access via c#. tried the format like outlook builds links via "HYPERLINK", but no way.
like u wrote, real html format is necessary.