Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
send attachment using file path from hyperlink
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
send attachment using file path from hyperlink
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) www.rondebruin.nl "Steve" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
send attachment using file path from hyperlink
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) www.rondebruin.nl "Steve" wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
send attachment using file path from hyperlink
Hi Steve
Is the text of the hyperlink the full path ?? -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Steve" wrote in message ... 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) www.rondebruin.nl "Steve" wrote in message ... 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
send attachment using file path from hyperlink
Hi Ron,
no, that is the problem, if I open a new workbook and do inserthyperlink, 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) www.rondebruin.nl "Steve" wrote in message ... 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) www.rondebruin.nl "Steve" wrote in message ... 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 . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
send attachment using file path from hyperlink
I hope to try something for you tomorrow if
you don't get a answer Steve. -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Steve" wrote in message ... Hi Ron, no, that is the problem, if I open a new workbook and do inserthyperlink, 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) www.rondebruin.nl "Steve" wrote in message ... 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) www.rondebruin.nl "Steve" wrote in message ... 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 . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
send attachment using file path from hyperlink
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 -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Ron de Bruin" wrote in message ... I hope to try something for you tomorrow if you don't get a answer Steve. -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Steve" wrote in message ... Hi Ron, no, that is the problem, if I open a new workbook and do inserthyperlink, 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) www.rondebruin.nl "Steve" wrote in message ... 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) www.rondebruin.nl "Steve" wrote in message ... 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 . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
send attachment using file path from hyperlink
Work like a charm your function Dick
-- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Dick Kusleika" wrote in message ... 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 -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Ron de Bruin" wrote in message ... I hope to try something for you tomorrow if you don't get a answer Steve. -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Steve" wrote in message ... Hi Ron, no, that is the problem, if I open a new workbook and do inserthyperlink, 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) www.rondebruin.nl "Steve" wrote in message ... 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) www.rondebruin.nl "Steve" wrote in message ... 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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Send active workbook as attachment via hyperlink | Excel Worksheet Functions | |||
Send file as attachment | Excel Discussion (Misc queries) | |||
Send Excel File as Attachment | Excel Discussion (Misc queries) | |||
unable to send excel file as attachment tab greyed out in file com | Excel Discussion (Misc queries) | |||
how to send excel file as an attachment with OE instead of Outloo | New Users to Excel |