Hyperlink to Files

[Threshold]

Hi All,

Got a quick question, I'm using the following code to send emails to people:

Sub Email_Notification()'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Working in Office 2000-2016
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
    Dim strto As String
    For Each cell In ThisWorkbook.Sheets("Settings").Range("C11:C17")
        If cell.Value Like "?*@?*.?*" Then
            strto = strto & cell.Value & ";"
        End If
    Next cell
    If Len(strto) > 0 Then strto = Left(strto, Len(strto) - 1)
    
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")


    On Error GoTo cleanup
    For Each cell In Columns("O").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And _
           LCase(Cells(cell.Row, "F").Value) = "x" _
           And LCase(Cells(cell.Row, "N").Value) <> "notified" Then


            Set OutMail = OutApp.CreateItem(0)


            On Error Resume Next
            With OutMail
                .To = strto 'cell.Value
                .Subject = "ECO No. " & Cells(cell.Row, "A").Value & " Closed"
                .Body = "Dear All," _
                      & vbNewLine & vbNewLine & _
                        "The following drawings have been released for closure:" & _
                        vbNewLine & vbNewLine & _
                        Cells(cell.Row, "C").Value
                'You can add files also like this
                '.Attachments.Add ("C:\test.txt")
                .Display  'Or use Send
            End With
            On Error GoTo 0
            Cells(cell.Row, "N").Value = "Notified"
            Set OutMail = Nothing
        End If
    Next cell


cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

I'd like to add a hyperlink in the emails to the respective files but not sure how to do this.

Example: It sends out an email and the ECO# is 201, so it would link the following file: N:\ENGINEERING\ECO\201\201.xlsm
Please keep in mind that the 201 in both links will vary as it increase, so the next one would be 202 and your link would be N:\ENGINEERING\ECO\202\202.xlsm

Any and all help would be appreciated. Thank you in advance!

 

[John_w]

Change .Body to .HTMLBody, and use an online HTML editor to create the HTMLBody string with required formatting and text.

I tried to change your code to include HTML, but the forum screwed up the formatting and made the code impossible to read.

 

[Threshold]

Thanks, makes sense. Can you still use the Cells(cell.Row, "A") and stuff like that with the html coding?

 

[John_w]

Yes, use string concatenation to include a cell value in the HTML:

.HTMLBody = "< p>Cell value" & Cells(cell.Row,"A").Value & "< /p>"

Note - that isn't correct HTML!

 

[Threshold]

So I've gotten the HTML body to work for me, the issue I'm having now is adding in the file path information:

Excerpt of the fixed area:

With OutMail
                .To = strto 'cell.Value
                .Subject = "ECO No. " & Cells(cell.Row, "A").Value & " Closed"
                .HTMLBody = "Dear All, 

" & "The following drawings have been released for closure: 

" & Cells(cell.Row, "C").Value & " a href="file://N:\ENGINEERING\ECO\5416">click here to view spreadsheet /a "
                'You can add files also like this
                '.Attachments.Add ("C:\test.txt")
                .Display  'Or use Send
            End With

(Please Ignore the missing <> in the code, it's just so it shows up properly)

Thank you in advance for the help

 

[Threshold]

Apologize, I figured it out for the one thing, I have quotes when I should not.

 

[Threshold]

Last question on this, is there an easy way to make it either .xls or .xlsm at the end of the code for the file in html?

 

[John_w]

You can use single quotes (apostrophes) instead of double quotes to delimit attributes within the HTML, which makes the VBA code easier to read:

.HTMLbody = "< p>Link: < a href='file://N:\ENGINEERING\ECO\5416\etc.'>Excel file< /a>< /p>"

Last question on this, is there an easy way to make it either .xls or .xlsm at the end of the code for the file in html?

Do you mean that the linked file can be either .xls or .xlsm and you want one or the other as the hyperlink depending on which one exists? If so, use the Dir function like this:

    Dim filePath As String, fullFile As String
    filePath = "N:\ENGINEERING\ECO\" & Cells(cell.Row, "A").Value & "\" & Cells(cell.Row, "A").Value
    If Dir(filePath & ".xls") <> vbNullString Then
        fullFile = filePath & ".xls"
    ElseIf Dir(filePath & ".xlsm") <> vbNullString Then
        fullFile = filePath & ".xlsm"
    End If
    'Now use fullFile in the hyperlink