Excel Email Macro with Hyperlink Help

[DIYbob21]

Hi everyone, hew to the board here and I'm in need of some assistance.

I have a macro built in excel to send out an email and I have my template and everything set up, but I'm having issues with the hyperlink functionality. I can get it to point to a specific location (ex. C\Files\Folder1\Subfolder1), but I'm looking to have "Folder1" and "Subfolder1" be values in cells on my spreadsheet.

Can anyone help with the below code?? I've highlighted the 3 difference sections that I need to point to 3 difference cells on this spreadsheet.

Thanks in advance!

Sub Mail_Outlook()
Dim OutApp As Object
Dim OutMail As Object


LastRow = CLng(InputBox("Row"))

If Cells(LastRow, 1).Value <> "" Then

MailTo = Cells(LastRow, 1).Value



MailSubject = Cells(LastRow, 1).Offset(0, 11).Value & "CHECKER - " & Cells(LastRow, 1).Offset(0, 5).Value & " - " & Cells(LastRow, 1).Offset(0, 6).Value & " - ERNIE ID: " & Cells(LastRow, 1).Offset(0, 7).Value






'Send Mail
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(o)
With OutMail
.Subject = MailSubject
.To = MailTo
.HTMLBody = "Hi " & Cells(LastRow, 1).Offset(0, 1).Value & "," & "<br />" & "<br />" & _
"You're up next on the checker sheet. " & "This is a " & Cells(LastRow, 1).Offset(0, 4).Value & ", " & Cells(LastRow, 1).Offset(0, 3).Value & "<a href=""\\MIDP-SFS-113\MidAtlNB\Mid Atlantic Key\2018\07-01"">case </a>. " & "Please return this group to me by " & Cells(LastRow, 1).Offset(0, 10).Value & "." & _
"<br />" & "<br />" & "Thanks," & "<br />" & "<br />" & Cells(LastRow, 1).Offset(0, 8).Value
'.Attachments.Add FileNme
.Display
'.Send
End With

Set OutMail = Nothing
Set OutApp = Nothing
End If

End Sub

 

[DIYbob21]

Bump - Any help please????

 

[daverunt]

Hi,

Posting HTML always seems to omit bits so I've put a text file here to view just for the portion of code covering the path.
I hope it is correct. Change the ranges as required.

https://www.dropbox.com/s/103230xutcih13h/fPath.txt?dl=0

 

[DIYbob21]

Hi,

Posting HTML always seems to omit bits so I've put a text file here to view just for the portion of code covering the path.
I hope it is correct. Change the ranges as required.

https://www.dropbox.com/s/103230xutcih13h/fPath.txt?dl=0

You're my hero! Thank you so much!