Losing hyperlinks when emailing

[C Holmes]

I would like to send an Excel file with hyperlinks of photo's attached to cells but when it is sent the recieving person cannot access the hyperlinks anymore. I am using 2007, I have tried to send the folder but that didn't seem to work. Any other way around this?

 

[Gary McMaster]

When I save an email attachment it offers to save it to C:\My Documents ...

I really don't know the answer to your question. It may be something that is set as a system default or simply the same folder I used the last time I saved an attachment.

I know that you don't have to specify a path (in a hyperlink) if the the file you are linking to is in the same folder as the file containing the link. If there are not too many links maybe you can put the Excel file and photos all in the same folder and change the links so that there are no paths. That should work no matter where your end user saves the package.

Gary

 

[arkusM]

Using Macro recorder gave this code.

Sheet1.Hyperlinks.Add Anchor:=Selection, Address:= _
"Path\file.jpg"

 

[C Holmes]

I will play around and see if I can make it work. The activeworkbook.path is that a macro?

 

[arkusM]

I will play around and see if I can make it work. The activeworkbook.path is that a macro?

Yes, I asssumed you were already doing this via VBA.

You can use a Macro to set/get the path of the active workbook.
AND
Set the path of the hyper link.

Sheet1.Hyperlinks.Add Anchor:=Selection, Address:= "path\file" , TextToDisplay:="Your TEXT"

I have not tested but it seems that you could use a OnOpen event to set the hyperlinks to get the path where the file is saved and assuming that you put the files you are linking to in the same directory to relink them to the ActiveWorkbook.Path

 

[arkusM]

This might get you started:
Red stuff needs to be edited and customized.
This would be loopable and you could set the files names in cells and make the links.

In a Standard Module put:

Sub Macro1()
    Dim StrPath As String
    Dim strFile As String
    Dim rngAnchor As Range
 
    StrPath = ActiveWorkbook.Path
 
    strFile = [B][COLOR=red]Range("H13").[/COLOR][/B]Value
    Set rngAnchor = [B][COLOR=red]Range("H13")[/COLOR][/B]
 
    ActiveSheet.Hyperlinks.Add Anchor:=rngAnchor, Address:= _
        StrPath & "\" & strFile
End Sub

 

[arkusM]

Ok Sorry for reposting but have been distracted:

assuming your file names are in A and start in row one and the excel file is in the same directory as the targets, the following will loop through the continouous list of files:

Sub Macro1()
    Dim StrPath As String
    Dim strFile As String
    Dim rngAnchor As Range
    Dim x As Integer
    StrPath = ActiveWorkbook.Path
    x = 1
    With ActiveSheet
        Do
            'On Error Resume Next
            strFile = [COLOR=red][B].Cells(x, 1)[/B][/COLOR][COLOR=black].[/COLOR]Value
            Set rngAnchor = .Cells(x, 1)
            ActiveSheet.Hyperlinks.Add Anchor:=rngAnchor, Address:=StrPath & "\" & strFile
            x = x + 1
        Loop Until .Cells(x, 1) = ""
    End With
End Sub

To change the x to the row number where you list starts
.Cells(x, 1) change the green one ot the column where your data is (a=1, b=2)

 

[C Holmes]

If I type

<CODE>=CELL("filename")</CODE></PRE>
<CODE>in a blank cell it gives me the path. </CODE></PRE>
<CODE>When I copy that path to the hyperlink base in the summary </CODE></PRE>
<CODE>tab it does not restore hyperlinks. What am I doing wrong?</CODE></PRE>

 

[arkusM]

Could be because of the Workbook and sheet reference at the end.
If you are using the Code I posted you should not need the path as the code references the path.
I was not clear. On the sheet on A1 A2 etc should contain the file names only. filename.pdf for example. and the code will loop down the A column until it comes to blank row.

I am gone for a week now so hopefully you can get what you are trying to do done. Good luck.

 

[Gary McMaster]

If you want to change the "Hyperlink Base" with code, you can do it like so:

ThisWorkbook.BuiltinDocumentProperties("Hyperlink base").Value = ThisWorkbook.Path & "\"

The "Workbook Open" event would seem to be the best place to put it.

If you end up with double trailing back slashes you can remove the & "\" from the code.


Gary