send attachment using file path from hyperlink

[Steve]

Hi,


I have some code that send an email attachment like

..Attachments.Add ("Path/filename")

instead I would like to use a hyperlink to get the text
string from, like

..Attachments.Add Cells(1, 2)

(which of course doesn't work)

where B1 would have a hyperlink with the path and
filename.

So I would like replace the ("Path/filename")
with the text string of the hyperlink in B1


Thanks,

Steve

 

[Ron de Bruin]

Try this (untested)

.Attachments.Add HyperlinkAddress(Cells(1, 2))


Function HyperlinkAddress(cell)
'David McRitchie
On Error Resume Next
HyperlinkAddress = cell.Hyperlinks(1).Address
If HyperlinkAddress = 0 Then HyperlinkAddress = ""
End Function

Sub TEST()
MsgBox HyperlinkAddress(Cells(1, 2))
End Sub

 

[Steve]

Thanks Ron..

I found another problem now, the hyperlinks I insert when
don't use the full path, instead I get

...\Desktop\DW Install .doc

it works if I would use the full path and address but if
I let excel do it it will return similar things like the
above and the macro will retun an error
So my next question is, if I always use the same folder
and I want all files in that folder. If I use


..Attachments.Add ("C\MyFolder\*.*")

it will return an error saying it can't attach a folder,
is there a way to get all files in MyFolder?

Thanks,

Steve

Try this (untested)

.Attachments.Add HyperlinkAddress(Cells(1, 2))


Function HyperlinkAddress(cell)
'David McRitchie
On Error Resume Next
HyperlinkAddress = cell.Hyperlinks(1).Address
If HyperlinkAddress = 0 Then HyperlinkAddress = ""
End Function

Sub TEST()
MsgBox HyperlinkAddress(Cells(1, 2))
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)




"Steve" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

Hi Steve

Is the text of the hyperlink the full path ??

 

[Steve]

Hi Ron,


no, that is the problem, if I open a new workbook and do
insert>hyperlink, browse the right folder and then select
the file. Then it works because I get the full path like
in ("C\My Documents\MySend\MyFile,pdf"), but in the saved
workbook that is in My Documents when I do the same thing
it only gives me ("..\\MyFile.pdf"). Thus when running
the macro it will come up with an error since it needs
the full path but I can't get the full path once I save
the workbook.

So my idea instead was that if I in the code could refer
to the folder where the attachment(s) can be, then attach
all of the files that is/are in a folder called MySend..

That would be the easiest way, then I would only have to
put the file(s) I want to attach there and run the macro,
but when I changed the line to

..Attachments.Add ("C\MyFolder\*.*")

it will return an error so I assume I must somehow get a
file list and refer to that somehow. Problem is I don't
know how to do that?

Thanks,

Steve

-----Original Message-----
Hi Steve

Is the text of the hyperlink the full path ??



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)




"Steve" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

I hope to try something for you tomorrow if
you don't get a answer Steve.

 

[Dick Kusleika]

Steve

Putting those files in a folder sounds like a pain. Here's a function that
will convert a hyperlink address to a path. You call it like

..Attachments.Add HypToPath(Range("a1").Hyperlinks(1)

I tested it on a few combinations, but have missed something. Let me know
if you have problems with it.

Function HypToPath(hyp As Hyperlink) As String

Dim CurrAdd As String
Dim GoBack As Long
Dim CurrFldr As String
Dim CAddStrip As String
Dim i As Long
Dim OldDir As String

CurrAdd = hyp.Address
CAddStrip = Replace(CurrAdd, "..\", "")
CurrFldr = hyp.Parent.Parent.Parent.Path
OldDir = CurDir

GoBack = (Len(CurrAdd) - Len(CAddStrip)) / 3

If GoBack > 0 Then
ChDir CurrFldr

For i = 1 To GoBack
ChDir ".."
Next i

If Not CurDir Like "?:\" Then
CAddStrip = "\" & CAddStrip
End If

HypToPath = CurDir & CAddStrip

ChDir OldDir
ElseIf Mid(CurrAdd, 1, 2) = "\\" Then
HypToPath = CurrAdd
Else
HypToPath = CurrFldr & "\" & CurrAdd
End If

End Function

 

[Ron de Bruin]

Work like a charm your function Dick