Send links via Outlook email

September 19, 2008JPNo CommentsRate This ArticlenewLinks 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:

Sub CreateMailInExcelLateBound1()
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:

Sub CreateMailInExcelLateBound2()

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.

Sub CreateMailInExcelLateBound2()

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:

"<p><a href=" & Chr(34) & _
"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:

"<p><a href=" & Chr(34) & strDirectory & "\" & _
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:

"<p><a href=" & Chr(34) & "G:\Dir\MyFolder\CompanyInfo\" & _
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

About JP
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 »

↑ Scroll to top
Previous Post:

Next Post:

1 Response(s) to Send links via Outlook email ↓

  1. Micke says:

    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.

Speak Your Mind

Tell us what you're thinking...

Certain comments (including first-time comments) are subject to moderation and will not appear immediately. Please view the Comment Policy for more information. To post VBA code in your comment, use tags like this: [cc lang='vb']Code goes here[/cc].




Site last updated August 24, 2010 @ 5:56 pm