currently I am trying to format a hyperlink using HTML in an excel macro for an email. I cant seem to figure out why the macro keeps giving me an expression error when I format my hyperlink using HTML. The hyperlink I am looking is is variable "SPoint". Any help at all would be appreciated. Thanks!
-Actual link has been changed to google for testing purposes-
Public Sub CreateEmail() '(emailtype As String)
Dim TabName As String
Dim MR As Integer
Dim MRCol As Integer
Dim Title As String
Dim Priority As String
Dim Developer As String
Dim PeerReviewer As String
Dim BUReviewer As String
Dim PREmail As String
Dim BUEmail As String
Dim BaseWorkspace As String
Dim DevWorkspace As String
Dim Subject As String
Dim Body As String
Dim SPoint As String
Dim DevSteps As Range
Dim ProjDates As Range
Dim CompDates As Range
Dim FinalTable As Range
Dim emailApplication As Object
Dim emailItem As Object
'Get MR information
TabName = ActiveSheet.Name
MR = Range("SelectedMR")
MRCol = WorksheetFunction.Match(MR, Rows(2), 0)
Title = Cells(3, MRCol)
Priority = Cells(4, MRCol)
Developer = Cells(5, MRCol)
PeerReviewer = Cells(6, MRCol)
PREmail = WorksheetFunction.XLookup(PeerReviewer, Worksheets("Emails").Columns("A"), Worksheets("Emails").Columns("B"), 0)
BUReviewer = Cells(7, MRCol)
BUEmail = WorksheetFunction.XLookup(BUReviewer, Worksheets("Emails").Columns("A"), Worksheets("Emails").Columns("B"), 0)
BaseWorkspace = Cells(8, MRCol)
DevWorkspace = Cells(9, MRCol)
SPoint = <a href="https://www.google.com">TEST</a> & MR
'Get MR calendar information
Set DevSteps = Worksheets(TabName).Range(Cells(11, 2), Cells(25, 2))
Set ProjDates = Worksheets(TabName).Range(Cells(11, MRCol), Cells(25, MRCol))
Set CompDates = Worksheets(TabName).Range(Cells(11, MRCol + 1), Cells(25, MRCol + 1))
'Format calendar table
Worksheets(TabName).Range(Cells(100, 2), Cells(114, 2)) = DevSteps.Value
Worksheets(TabName).Range(Cells(100, 3), Cells(114, 3)) = ProjDates.Value
Worksheets(TabName).Range(Cells(100, 4), Cells(114, 4)) = CompDates.Value
Worksheets(TabName).Cells(99, 2) = "Development Step"
Worksheets(TabName).Cells(99, 2).Font.FontStyle = "Bold"
Worksheets(TabName).Cells(99, 3) = "Target Date"
Worksheets(TabName).Cells(99, 3).HorizontalAlignment = xlRight
Worksheets(TabName).Cells(99, 3).Font.FontStyle = "Bold"
Worksheets(TabName).Cells(99, 4) = "Completed"
Worksheets(TabName).Cells(99, 4).HorizontalAlignment = xlRight
Worksheets(TabName).Cells(99, 4).Font.FontStyle = "Bold"
Set FinalTable = Worksheets(TabName).Range(Cells(99, 2), Cells(114, 4))
FinalTable.Font.Size = 9
Set emailApplication = CreateObject("Outlook.Application")
Set emailItem = emailApplication.CreateItem(0)
'Format email based on button pressed
'If emailtype = "Peer Review" Then
emailItem.Subject = "Peer Review Request: MR" & MR & " - " & Title & "; Priority: " & Priority
emailItem.HTMLBody = "Hey " & PeerReviewer & "," & "<br><br>" & "Would you be able to peer review MR" & MR & " when you get a chance?" & _
"<br><br>" & "Sharepoint: " & SPoint & vbNewLine & "<br><br>" & RangetoHTML(FinalTable) & "Thank you," & "<br><br>" & Developer
'End If
emailItem.Display
Worksheets(TabName).Range(Cells(99, 2), Cells(114, 4)).Clear
Set emailItem = Nothing
Set emailApplication = Nothing
End Sub