One of my most popular articles is where I showed how to create a hyperlink in an Excel spreadsheet that when clicked composes an email message. This method used only hyperlink and concatenate formulas; no VBA macros. It’s simple, easy to use, and not too complicated. But you can only do so much with this method.
Today, I will be using the more complicated method of using VBA macros to show how you can add a button to your spreadsheet that when pressed will automatically compose an email containing a link to the file location of the spreadsheet. I’m constantly sharing spreadsheets with friends of coworkers. I don’t know how many times a day I create a new email message, copy and paste a file location into the message, create the hyperlink to the spreadsheet, etc. This macro will help save you some time (and hopefully impress your coworkers in the process).
Sub Compose_Email()
I almost always add these to optimize macro speed:
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Dim Wb1 As Workbook
Set Wb1 = ThisWorkbook
Declare variable names for the cells where the email addresses will be saved:
Dim SendTo1 As String
Dim SendTo2 As String
Dim cc1 As String
Dim cc2 As String
The email address are entered on SendList sheet, my “helper” sheet. As I said earlier, you can change email address without having to change VBA code because the macro refers to the cell’s location as opposed to entering the email address itself:
Wb1.Sheets("SendList").Select
SendTo1 = Range("B1").Value
SendTo2 = Range("C1").Value
cc1 = Range("B2").Value
cc2 = Range("C2").Value
'back to the first sheet
ThisWorkbook.Sheets(1).Select
Now get the name shown in the upper right hand corner of Excel to use as the signature:
Dim OwnerName As String
OwnerName = Application.UserName
Get the workbook name. We’ll use this as our email subject line:
Dim WorkbookName As String
WorkbookName = Wb1.Name
Get the location where the spreadsheet is saved:
Dim FileLoc As String
FileLoc = Wb1.FullName
Time to start Microsoft Outlook if it hasn't already been started:
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
On Error Resume Next
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
The code will be slightly different for non-html email versus html email. For now, I am only going to show you how to do html email so we can easily link back to the spreadsheet. This is the body of the email. <br> is html code to go to the next line. Chr(34) is for quotation marks. This will make more sense once you run the macro and see the end result.
xMailBody = "Dear Team, <br><br>" & "I've updated the weekly financial report. Please check and sign this:" & "<br><br>" & _
"<a href=" & Chr(34) & FileLoc & Chr(34) & " > " & WorkbookName & " </a> " _
& "<br><br>" & "Thanks," & "<br><br>" & OwnerName
Fill in each section of the newly created email message:
On Error Resume Next
With xOutMail
.To = SendTo1 & "; " & SendTo2
.CC = cc1 & "; " & cc2
.BCC = ""
.Subject = WorkbookName
'.Body = xMailBody
.HTMLBody = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
Reset Macro Optimization Settings:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Finally, now that the code is done you can add a button to your spreadsheet by going to the Developer tab, Insert, Button (Form Control). Link your newly created macro to the button, customize the button text, and that’s all there is to it!
I’ve made what I’ve shown into a simple template you can download here:
Compose Email with Link Example Spreadsheet.xlsm download here
I hope you find this example will make sharing spreadsheets with your coworkers easier. Let me know what you think about this template in the comments below.
Today, I will be using the more complicated method of using VBA macros to show how you can add a button to your spreadsheet that when pressed will automatically compose an email containing a link to the file location of the spreadsheet. I’m constantly sharing spreadsheets with friends of coworkers. I don’t know how many times a day I create a new email message, copy and paste a file location into the message, create the hyperlink to the spreadsheet, etc. This macro will help save you some time (and hopefully impress your coworkers in the process).
Setup Your Spreadsheet to be Able to Send Emails From Excel
If you’re going to send the email to the exact same people every time, you could just include the email addresses inside the vba code itself. But if the people change or someone who doesn’t know VBA needs to update it, this is not the best solution. What I like to do is add a helper sheet, and on this sheet list all the email addresses of who to include in the “TO” line and who to “CC.” This way anyone with no coding knowledge can easily change who the email is for.
VBA Macro to Compose Email with Hyperlink
Now it’s time to write the Excel macro. I’ll walk you through it step by step.Sub Compose_Email()
I almost always add these to optimize macro speed:
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Dim Wb1 As Workbook
Set Wb1 = ThisWorkbook
Declare variable names for the cells where the email addresses will be saved:
Dim SendTo1 As String
Dim SendTo2 As String
Dim cc1 As String
Dim cc2 As String
The email address are entered on SendList sheet, my “helper” sheet. As I said earlier, you can change email address without having to change VBA code because the macro refers to the cell’s location as opposed to entering the email address itself:
Wb1.Sheets("SendList").Select
SendTo1 = Range("B1").Value
SendTo2 = Range("C1").Value
cc1 = Range("B2").Value
cc2 = Range("C2").Value
'back to the first sheet
ThisWorkbook.Sheets(1).Select
Now get the name shown in the upper right hand corner of Excel to use as the signature:
Dim OwnerName As String
OwnerName = Application.UserName
Get the workbook name. We’ll use this as our email subject line:
Dim WorkbookName As String
WorkbookName = Wb1.Name
Get the location where the spreadsheet is saved:
Dim FileLoc As String
FileLoc = Wb1.FullName
Time to start Microsoft Outlook if it hasn't already been started:
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
On Error Resume Next
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
The code will be slightly different for non-html email versus html email. For now, I am only going to show you how to do html email so we can easily link back to the spreadsheet. This is the body of the email. <br> is html code to go to the next line. Chr(34) is for quotation marks. This will make more sense once you run the macro and see the end result.
xMailBody = "Dear Team, <br><br>" & "I've updated the weekly financial report. Please check and sign this:" & "<br><br>" & _
"<a href=" & Chr(34) & FileLoc & Chr(34) & " > " & WorkbookName & " </a> " _
& "<br><br>" & "Thanks," & "<br><br>" & OwnerName
Fill in each section of the newly created email message:
On Error Resume Next
With xOutMail
.To = SendTo1 & "; " & SendTo2
.CC = cc1 & "; " & cc2
.BCC = ""
.Subject = WorkbookName
'.Body = xMailBody
.HTMLBody = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
Reset Macro Optimization Settings:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Add a Button to Run the Macro
Finally, now that the code is done you can add a button to your spreadsheet by going to the Developer tab, Insert, Button (Form Control). Link your newly created macro to the button, customize the button text, and that’s all there is to it!
Watch how the macro works in the video below where I also walk you through it step by step:
I’ve made what I’ve shown into a simple template you can download here:
Compose Email with Link Example Spreadsheet.xlsm download here
I hope you find this example will make sharing spreadsheets with your coworkers easier. Let me know what you think about this template in the comments below.
