How to send email from Excel with hyperlink in the email body?
Have you ever tried to send an email with VBA code in Excel? And how to add hyperlinks to the email body text when sending email? This article will help to solve this problem.
The below VBA code can help to send email from Excel and insert specific hyperlink in the email body. Please do as follows.
1. Launch your workbook, press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, click Insert > Module, and then copy the below VBA code into the Module editor.
VBA code: Insert hyperlink into email body
Sub EmailHyperlink() 'updated by Extendoffice 20190815 Dim xOtl As Object Dim xOtlMail As Object Dim xStrBody As String xStrBody = "Hi there:" & "<br>" _ & "Please click " & "<a href=" & "http://www.extendoffice.com"">Here</a> to open the page" & "<br>" _ & "Thank you." On Error Resume Next Set xOtl = CreateObject("Outlook.Application") Set xOtlMail = xOtl.CreateItem(olMailItem) With xOtlMail .To = "Email Address" .CC = "Email Address " .BCC = " Email Address " .Subject = "Subject line" .HTMLBody = .HTMLBody & xStrBody .Display End With Set xOtl = Nothing Set xOtlMail = Nothing End Sub
- Please change the body content and the hyperlink in the xStrBody line.
- Replace the “Email Address” in the .To, .CC and .BCC lines with the actual email addresses you will send email to. If you don’t need the CC and BCC lines, just remove them from the whole code, or add a single quote before the lines, such as 'CC =”Email Address”.
- Replace the “Subject line” in the .Subject line with your email subject own.
3. Press the F5 key to run the code. Then the email is created with specified fields and body with hyperlink listed inside, click the Send button to send it.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!