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.
- Sub Email_With_Link()
- 'Step 1: Declare your variables
- Dim OLApp As Object
- Dim OLMail As Object
- 'Step 2: Open Outlook start a new mail item
- Set OLApp = CreateObject("Outlook.Application")
- Set OLMail = OLApp.CreateItem(0)
- 'Step 3: Build your mail item and send
- With OLMail
- .To = "email@example.com; firstname.lastname@example.org"
- .CC = "email@example.com"
- .BCC = ""
- .Subject = "Monthly Report Email with Link"
- .HTMLBody = _
- "<p>Monthly report is ready. Click to Link to get it.</p>" & _
- "<p><a href=" & Chr(34) & "Z:\Downloads\MTD_Stats.xlsm" & Chr(34) & ">Download Now</a></p>"
- .Display 'Change to .Send to send without reviewing
- End With
- 'Step 4: Memory cleanup
- Set OLMail = Nothing
- Set OLApp = Nothing
- 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!