Looking for HTML Link for Local Folder Location Based on Cell Values

[DIYbob21]

Hi everyone!

I've been all over this forum trying to figure out a solution for my macro, but I've having trouble and could really use some help!

I've created an excel document where a user will fill out let's say Row 2 with specific information and then when you hit the macro button I've created, it pops up a window asking what row the data is in and it opens outlook and fills in the entire email based on those values are in row 2. The next user would open that document and fill in their information on row 3 and the button (when putting in 3) will fill out that email with those windows. And the point is to keep doing this for each row. I have that functionality down, so all is great there. It's a new feature I'm struggling with...

I'm trying to add a new feature to the email template so that it will insert a link to a folder location on our companies folders. I've set it up so it inputs a specific folder, but I'm trying to create it so that based on the row the type in (row 2 or 3 in the above example). Below is the code I currently have...


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




So that code there, works and opens up that exact location. However I want the 3 different colored sections to be filled in based on 3 different cells in the row selected. Red would be some sort of Lastrow column E value, green would be Lastrow column H value and purple wold be Lastrow column G value.

Is this possible?

Any help would be GREATLY appreciated.

Thanks!

 

[DIYbob21]

Hi everyone!

I've been all over this forum trying to figure out a solution for my macro, but I've having trouble and could really use some help!

I've created an excel document where a user will fill out let's say Row 2 with specific information and then when you hit the macro button I've created, it pops up a window asking what row the data is in and it opens outlook and fills in the entire email based on those values are in row 2. The next user would open that document and fill in their information on row 3 and the button (when putting in 3) will fill out that email with those windows. And the point is to keep doing this for each row. I have that functionality down, so all is great there. It's a new feature I'm struggling with...

I'm trying to add a new feature to the email template so that it will insert a link to a folder location on our companies folders. I've set it up so it inputs a specific folder, but I'm trying to create it so that based on the row the type in (row 2 or 3 in the above example). Below is the code I currently have...


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 & "," & "
" & "
" & _
"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 & "Key\2018\07-01"">case . " & "Please return this group to me by " & Cells(LastRow, 1).Offset(0, 10).Value & "." & _
"
" & "
" & "Thanks," & "
" & "
" & Cells(LastRow, 1).Offset(0, 8).Value
'.Attachments.Add FileNme
.Display
'.Send
End With

Set OutMail = Nothing
Set OutApp = Nothing
End If

End Sub




So that code there, works and opens up that exact location. However I want the 3 different colored sections to be filled in based on 3 different cells in the row selected. Red would be some sort of Lastrow column E value, green would be Lastrow column H value and purple wold be Lastrow column G value.

Is this possible?

Any help would be GREATLY appreciated.

Thanks!

So it put the link in there... the code I have is....

<a href=""\\MIDP-SFS-113\MidAtlNB\Mid Atlantic Key\2018\07-01"">case </a>.

 

[DIYbob21]

So it put the link in there... the code I have is....

Key\2018\07-01"">case .

So I'm even struggling to put the needed code in..


Anyone able to help write a code using the htmlbody hyperlink function that takes a folder location, but flexes certain parts based on lastrow. value

example..

C\\test\Location *Cell lastrow column E*\*Cell lastrow column H*\*Cell lastrow column G

Thanks.