Send an Email with a Link to a Workbook on a Share Drive

Ever needed to send an email with a link to an Excel Workbook on a share drive? Well if you have, you’re in the same boat as Sophia who writes in to ask:

“..how do I email from Excel with a hyperlink to a workbook on a shared directory?”

.

There is a way:

.

This macro starts an email with a few words of text and a hyperlink to a workbook in a specified location.

.

To implement this macro, simply copy and paste it into a standard module. Of course, you’ll have to edit the email addresses, subject line, body text and hyperlinked location.

.

  1. Sub Email_With_Link()
  2.  
  3. 'Step 1:  Declare your variables
  4.    Dim OLApp As Object
  5.     Dim OLMail As Object
  6.    
  7. 'Step 2:  Open Outlook start a new mail item
  8.    Set OLApp = CreateObject("Outlook.Application")
  9.     Set OLMail = OLApp.CreateItem(0)
  10.     OLApp.Session.Logon
  11.    
  12. 'Step 3:  Build your mail item and send
  13.    With OLMail
  14.     .To = "admin@datapigtechnologies.com; mike@datapigtechnologies.com"
  15.     .CC = "mha105@yahoo.com"
  16.     .BCC = ""
  17.     .Subject = "Monthly Report Email with Link"
  18.     .HTMLBody = _
  19.     "<p>Monthly report is ready.  Click to Link to get it.</p>" & _
  20.     "<p><a href=" & Chr(34) & "Z:\Downloads\MTD_Stats.xlsm" & Chr(34) & ">Download Now</a></p>"
  21.     .Display  'Change to .Send to send without reviewing
  22.    End With
  23.    
  24. 'Step 4:  Memory cleanup
  25.    Set OLMail = Nothing
  26.     Set OLApp = Nothing
  27.    
  28. End Sub

.

Here’s how it works:

Step 1: In Step 1, you first declare two variables. OLApp is an object variable that exposes the Outlook Application object. OLMail is an object variable that will hold a mail item.

.

Step 2: In Step 2, you activate outlook and start a new session. Note that you use OLApp.Session.Logon to log on to the current MAPI session with default credentials. You also create a mail item in this step. This is equivalent to selecting the New Message button in Outlook.

.

Step 3: In Step 3, you build the profile of your mail item. This includes the To recipients, the CC recipients, the BCC recipients, the Subject, and the HTMLBody. To create the hyperlink, you can use the HTMLBody property to pass HTML tags. Be sure to replace the file path address shown in the macro with the address for your own file. Also note in this step that the macro is using the .Display method which will open the email for your review. You can replace this with .Send to automatically fire the email without reviewing.

.

Step 4: As always, it’s generally good practice to release the objects assigned to your variables. This reduces the chance of any problems caused by rogue objects that may remain open in memory.

.

There you go Sophia. Happy emailing!

 

 

6 thoughts on “Send an Email with a Link to a Workbook on a Share Drive

  1. Jared

    If you replace the hard coded file path with ActiveWorkbook.FullName it offers a little flexibility if you are using this macro for multiple files.

  2. Fahim

    Hi,

    I would like to use this macro, but I use Lotus Notes as my email provider at work.

    How would I need to adjust this macro to enable Lotus Notes to send the email?

    Best,
    Fahim

  3. Matt

    Thank You – Thank You – Thank You… this was exactly what I was looking for. Just a few minor changes, and I look like an automation rock star.

  4. Frank

    In the example code, drive Z was used. I presume that drive Z is mapped to a networked file server. This works fine as long as the recipient’s workstation has the identical drive Z mapping as the sender. I highly recommend using the URL equivalent instead.

Leave a Reply

Your email address will not be published. Required fields are marked *