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:

VBA:
  1. Sub CreateMailInExcelLateBound1()
  2. Dim olApp As Object
  3. Dim Msg As Object
  4.  
  5. Set olApp = CreateObject("Outlook.Application")
  6.  
  7. Set Msg = olApp.CreateItem(0)
  8.  
  9. Msg.Display
  10.  
  11. Set Msg = Nothing
  12. Set olApp = Nothing
  13. 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:

VBA:
  1. Sub CreateMailInExcelLateBound2()
  2.  
  3. Dim olApp As Object
  4. Dim Msg As Object
  5. Dim bWeStartedOutlook As Boolean
  6.  
  7. Application.ScreenUpdating = False
  8. Application.StatusBar = "Creating email ..."
  9.  
  10. On Error Resume Next
  11.     Set olApp = GetObject(, "Outlook.Application")
  12.     If Err.Number <> 0 Then
  13.         Set olApp = CreateObject("Outlook.Application")
  14.         bWeStartedOutlook = True
  15.     End If
  16. On Error GoTo 0
  17.  
  18. If olApp Is Nothing Then
  19.     Application.StatusBar = False
  20.     MsgBox "Cannot start Outlook. Make sure Outlook is installed and can be run manually from this computer.", vbInformation
  21.     GoTo ExitProc
  22. End If
  23.  
  24. Set Msg = olApp.CreateItem(0)
  25.  
  26. Msg.Display
  27.  
  28. ExitProc:
  29. Set Msg = Nothing
  30. ' we need to call the Quit method, to quit Outlook if we actually started it ourselves
  31. If bWeStartedOutlook Then olApp.Quit
  32. Set olApp = Nothing
  33. Application.StatusBar = False
  34. Application.ScreenUpdating = True
  35. 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.

VBA:
  1. Sub CreateMailInExcelLateBound2()
  2.  
  3. Dim olApp As Object
  4. Dim Msg As Object
  5. Dim bWeStartedOutlook As Boolean
  6.  
  7. Application.ScreenUpdating = False
  8. Application.StatusBar = "Creating email ..."
  9.  
  10. On Error Resume Next
  11.     Set olApp = GetObject(, "Outlook.Application")
  12.     If Err.Number <> 0 Then
  13.         Set olApp = CreateObject("Outlook.Application")
  14.         bWeStartedOutlook = True
  15.     End If
  16. On Error GoTo 0
  17.  
  18. If olApp Is Nothing Then
  19.     Application.StatusBar = False
  20.     MsgBox "Cannot start Outlook. Make sure Outlook is installed and can be run manually from this computer.", vbInformation
  21.     GoTo ExitProc
  22. End If
  23.  
  24. Set Msg = olApp.CreateItem(0)
  25.  
  26. With Msg
  27.   .Subject = "Whatever subject line I want"
  28.   .HTMLBody = "<span style=""font-family : verdana;font-size : 10pt""><p>Hello,</p>" & _
  29.         "<p>Below is a link to a file on the company network.</p>" & _
  30.         "<p><a href=" & Chr(34) & "G:\Dir\MyFolder\CompanyInfo\Jan2008\DataFile.xls" & Chr(34) & ">DataFile</a></p>" & _
  31.         "<p>Thank you,<br />Jimmy Pena</p></span>" & "<br />"
  32.   .Display
  33. End With
  34.  
  35. ExitProc:
  36. Set Msg = Nothing
  37. ' we need to call the Quit method, to quit Outlook if we actually started it ourselves
  38. If bWeStartedOutlook Then olApp.Quit
  39. Set olApp = Nothing
  40. Application.StatusBar = False
  41. Application.ScreenUpdating = True
  42. 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 <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:

emaillink-300x193

    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:

VBA:
  1. "<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:

VBA:
  1. "<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:

VBA:
  1. "<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


Share and Enjoy:
  • StumbleUpon
  • Technorati
  • Digg
  • Google
  • del.icio.us
  • MisterWong

Print This Post Print This Post  |  Email This Post Email This Post  |  Permalink  |  Subscribe to this feed Subscribe now!

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

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

Post a Comment

To post VBA code in your comment, use [VBA] tags, like this: [VBA]Code goes here[/VBA].





Subscribe without commenting

Keep Reading:

Browse Posts:


« A simple debug mode for your Excel workbooks || Hold that email! »