Need Help with Fixing Code- File Location

bamaisgreat

Well-known Member
Joined
Jan 23, 2012
Messages
831
Office Version
  1. 365
Platform
  1. Windows
I was trying to add a couple line in the code below to have a link that would take someone to the file location. I have the problem area highlighted in Red. I cant seem to get the info entered without an error.

Code:
Sub REVISED_REQ()
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Working in Excel 2000-2016
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String


    If ActiveWorkbook.Path <> "" Then
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)


               strbody = "<font size=""3"" face=""Calibri"">" & _
                  "Joe,<br><br>" & _
                  "See link to " & ActiveWorkbook.Name & _
                  " revised req below.<br>" & _
                  " " & _
                  "<A HREF=""file://" & ActiveWorkbook.FullName & _
                  """>Link to the req</A>" & _
                  "<br><br>Thanks," & _
                  "<br><br>Jamey</font>"
                  
[COLOR=#ff0000]                  "<br><br>Click link below for file location" & _[/COLOR]
[COLOR=#ff0000]                  "Application.ActiveWorkbook.Path[/COLOR]


        On Error Resume Next
        With OutMail
            .To = "jgpiller@oosteel.com"
            .CC = ""
            .BCC = ""
            .Subject = "Revised Req# " & ActiveWorkbook.Name
            .HTMLBody = strbody
            .Display   'or use .Send
        End With
        On Error GoTo 0


        Set OutMail = Nothing
        Set OutApp = Nothing
    Else
        MsgBox "The ActiveWorkbook does not have a path, Save the file first."
    End If
End Sub
 
Last edited:

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Hello bamaisgreat,

Links can only be created in email using an HTML Anchor element and the file must be on a server that others can access. The file cannot reside on your hard disk.
 
Upvote 0
Yes, this is saved on our companies server. I use this macro often but I was wanting to also add a link for the file location.
 
Upvote 0
Rich (BB code):
const Q = """"

With OutMail
    .To = "jgpiller@oosteel.com"

    .HTMLBody ="
The Document LInk"
end with

the web keeps altering the code into a literal link, so i cannot post the vb code.
 
Last edited:
Upvote 0
I am wanting a hyperlink that will open explorer to the file location.


Example: Call Shell("explorer.exe" & " " & "P:\Engineering", vbNormalFocus)
 
Upvote 0
Sorry the Bold Text in Orange is ok its the text in Red that im having an issue with.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top