Macro 88: Send Mail with a Link to Our Workbook
Sometimes, you don’t need to send an attachment at all. Instead, you simply want to send an auto-
mated e-mail with a link to a file. This macro does just that.
How it works
Keep in mind that because this code will be run from Excel, we need to set a reference to the
Microsoft Outlook Object Library. We can set the reference by opening the Visual Basic Editor in Excel
and selecting Tools➜References. Scroll down until we find the entry Microsoft Outlook XX Object
Library, where the XX is your version of Outlook. Select the check box next to the entry.
Sub Macro88()
‘Step 1: Declare our variables
Dim OLApp As Outlook.Application
Dim OLMail As Object
‘Step 2: Open Outlook start a new mail item
Set OLApp = New Outlook.Application
Set OLMail = OLApp.CreateItem(0)
OLApp.Session.Logon
‘Step 3: Build our mail item and send
With OLMail
.To = “admin@datapigtechnologies.com; mike@datapigtechnologies.com”
.CC = “”
.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\MonthlyReport.xlsx” &
_ Chr(34) & “>Download Now</a></p>”
.Display
End With
‘Step 4: Memory cleanup
Set OLMail = Nothing
Set OLApp = Nothing
End Sub
1. Step 1 declares two variables. OLApp is an object variable that exposes the Outlook
Application object. OLMail is an object variable that holds a mail item.
2. Step 2 activates Outlook and starts a new session. Note that we use OLApp.Session.
Logon to log on to the current MAPI session with default credentials. This step also creates a
mail item. This is equivalent to selecting the New Message button in Outlook.
3. Step 3 builds the profile of our mail item. This includes the To recipients, the CC recipients,
the BCC recipients, the Subject, and the HTMLBody.
To create the hyperlink, we need to use the HTMLBody property to pass HTML tags. We can
replace the file path address shown in the macro with the address for our file. Note this
macro is using the .Display method, which opens the e-mail for our review. We can
replace .Display with .Send to automatically fire the e-mail without reviewing.
4. It is generally good practice to release the objects assigned to our variables. This reduces the
chance of any problems caused by rouge objects that may remain open in memory. In Step
4, we simply set variable to Nothing.
How to use it
To implement this macro, we can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11 on our keyboard.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code into the
