send attachment using file path from hyperlink

  • Thread starter Thread starter Steve
  • Start date Start date
S

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
 
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
 
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
Click to expand...
message news:[email protected]...
 
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
Click to expand...
message news:[email protected]...
 
Back
Top