How to change relative hyperlink to absolute hyperlink in VBA

[CostasM]

I want to send several attached files from MS Access using CDO.Message.
When I use "objMessage.AddAttachment" I need to indicate full path to the file, but all hyperlinks to files stored in separate column of my query (recordset) but as relative links only. How to change it to absolute hyperlinks?
Full code as below:

Private Sub btn4Pay_Click()
On Error GoTo btn4Pay_Click_Error
 
Dim rst As ADODB.Recordset
Dim strSQL As String
Set rst = New ADODB.Recordset
 
strSQL = "[qryDocsToSend]" 'source of recordset
rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
sFilePath = Trim(rst(8))
 
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "DOCUMENTS" 
objMessage.From = """Sender"" <[e-mail]>"   
objMessage.To = """Recepient"" <[e-mail]>"
 
Do While Not rst.EOF 
objMessage.TextBody = Trim(rs(0)) & "  " & Trim(rs(1))
objMessage.AddAttachment "sFilePath"
 
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
 
objMessage.BodyPart.Charset = "windows-1251"
 
'===SMTP server configuration extracted ===
 
objMessage.Send

[zmbd]

Would you please provide an example of the relative link? I am fairly certain what you are doing; however, having that example would confirm.

[CostasM]

When I add the hyperlink in table I see it as "Folder1\testfile.txt",
using VBA code Trim(rst(8)) I get it as
"#Folder1\testfile.txt#",
but actual path is C:\DOCUMENTS\Folder1\testfile.txt

It seems that I get this link with Hyperlink properties.
I need only full path (absolute) without #-signs and any other info.

My base is located in C:\DOCUMENTS

[NeoPa]

CostasM:
I need only full path (absolute) without #-signs and any other info.

I'm afraid that is just not true. There is no hyperlink without the hash (#) characters. See Error 7980: HyperlinkAddress or HyperlinkSubAddress read-only for Hyperlink for more about how hyperlinks work and how to set them in code when you need to.

For your situation you will need to extract the address that is relative (unless you have that information somewhere already). Fix the address. Unless it is the same every time it's run rou will need to use knowledge only you have. Lastly, redo the hyperlink as explained in the linked thread. Remember, the recipient of any email must be able to access the file from whatever is in the hyperlink, so drive letters are only adequate if they are necessarily the same on their PC. Otherwise you need to consider UNCs (\\Server\Share\folders...\File).

[CostasM]

to NeoPa
Thanks, but I don't need to send a link by e-mail, I need to send attached file, wich has a full path - C:\DOCUMENTS\Folder1\testfile.txt and this path stored in a table. I have table with 2 columns: "DocName" and "DocLink" where DocLink in Hyperlink format.
When I use

objMessage.AddAttachment "C:\DOCUMENTS\Folder1\testfile.txt" 

it works, but when I get this path from query(recordset) I see it as "#C:\DOCUMENTS\Folder1\testfile.txt#"
Probably I need to convert this path to text format in order to use it in objMessage.AddAttachment, but how ?

[ADezii]

Try something similar to (Code intentionally omitted):

Dim sNewFilePath As String
 
'Assuming sFilePath = rst(8), namely
sFilePath = "#C:\DOCUMENTS\Folder1\testfile.txt#"
 
sNewFilePath = CStr(Replace(sFilePath, "#", ""))
 
objMessage.AddAttachment sNewFilePath
 

[NeoPa]

CostasM:
When I add the hyperlink in table I see it as "Folder1\testfile.txt",
using VBA code Trim(rst(8)) I get it as
"#Folder1\testfile.txt#",
but actual path is C:\DOCUMENTS\Folder1\testfile.txt

CostasM:
it works, but when I get this path from query(recordset) I see it as "#C:\DOCUMENTS\Folder1\testfile.txt#"

Your original question was about the problem of the file address being stored as a relative link rather than a full one. My earlier response was directed at that as I can only respond to what you ask. It seems you're now saying that isn't a problem after all, but that you need to extract the value from a (fully formatted) string in Hyperlink format.

Ignoring the point of the original thread for the moment then (It has already been answered, after all, in the earlier post.), to get the value from within a Hyperlink formatted string you can use the Split() function. The following example code assumes you have already extracted that string into a variable called strHyperlink :

Call objMessage.AddAttachment(Split(strHyperlink, "#")(1))

[NeoPa]

It seems I cross-posted with ADezii. His approach will work for you with the data as you've given as an example, but be aware that it will fail if given a hyperlink value containing either the first or third elements (Display value or Internal (within the file) link). If that's a possibility in your situation then you need another approach (such as using Split() of course).

[CostasM]

Sorry, I've mistaked in my last post, when I run VBA code I get only "#Folder1\testfile.txt#" without C:\DOCUMENTS\. In order to see full path I had to enter it manualy to Hyperlink field, but it is extra work, usually I use traditional window Hyperlink/Edit or F4 and see only the relative link to the file - Folder1\testfile.txt

Using Split() and Replace() functions I will get requested value, but for the case when I typed hyperlink manualy in Hyperlink window and not for the case when I choose file from spicified folder in browser.

How to find first part of the file's path - C:\DOCUMENTS\ ?

[NeoPa]

CostasM:
How to find first part of the file's path - C:\DOCUMENTS\ ?

Good question. Now tell us how you know where to find it, so that we can consider how to go about programming it to do that same job.

If it's always C:\Documents\ then it's relatively straightforward (excuse the pun and see example code below), but if there is some reasoning behind where it should be found then we need to know that instead.

Call objMessage.AddAttachment("C:\Documents\" & Split(strHyperlink, "#")(1))

[CostasM]

toNeoPa
Thanks, now I check it in Immediate window and get sFilePath=Folder1\testfile.txt#Folder1\testfile.tx t
Can you clarify again how to use Split() function to get value from Hyperlink formatted string. I can add manualy "C:\Documents\" before and then to bound it with "sFilePath", but how to exclude all data after #-sign. It seems that it is 3rd element of Hyperlink filed.
Also original line was - objMessage.AddAttachment sFilePath,
why now - Call objMessage.AddAttachment(.....(1)) ?
Can you explain the 2nd part of this line after &

Call objMessage.AddAttachment("C:\Documents\" & Split(strHyperlink, "#")(1))

what does (1) parametr mean?

and what do you mean "I have already extracted that string into a variable called strHyperlink" ?

[NeoPa]

Costa,
When you check the Help section for Split() you will see that it returns a value that is an array. The (1) after the call is how VBA says it wants to use element #1 from an array. The default array indexing starts from zero so element #1 is the second element - IE. The data between the first and second separator charactes ("#" in this case) in the text. Try the following from the Immediate Pane of the VBE window (Use Ctrl-G to get there) :

?Split("A@B@C@D@E@F","@")(2)

You will see the value "C" printed.

CostasM:
and what do you mean "I have already extracted that string into a variable called strHyperlink" ?

I didn't ever say that, or anything that had that meaning. What I said was :
"The following example code assumes you have already extracted that string into a variable called strHyperlink :"
This means that the code is designed based on the assumption that the hyperlink string is stored in strHyperlink. It's a variable name so any other variable name will do as well.

The syntax where Call is used is optional in many cases, but I prefer to use it consistently as it highlights that a procedure is being used. VBA syntax (IMHO) is quite ambiguous when it comes to using procedures so I always try to make it as obvious and clear as possible that my code is using one when it is. Your earlier syntax would still work, but I will continue to use the clearer syntax in all my code.

I see no line where sFilePath is declared (See Require Variable Declaration), but as you seem to have that available already the code might look like :

Call objMessage.AddAttachment("C:\Documents\" & Split(sFilePath, "#")(1))

[CostasM]

To NeoPa
Great, Now it works ! Correct line wich I need is

Call objMessage.AddAttachment("C:\Documents\" & Split(sFilePath, "#")(0))

Thanks again. We can consider this thread closed, but 2 other questions have arisen from this :

** Extra questions removed as per site rules. See following post from NeoPa. **

[NeoPa]

Costa,
You need to post each question in a separate thread. We don't allow multiple questions in the same thread as it would cause all sorts of problems.

May I suggest you take both extra question, add enough information for them to make good sense so they won't just be deleted immediately, then post them as separate threads. You may post links into this thread if you like to draw the attention of any experts already involved, once the threads have been posted. Please pay particular attention to the instructions about fixing the questions before posting though. We are not mind-readers, and technical questions nearly always rely on various detailed information, without which it will be impossible to respond with meaningful answers.

Best wishes.