VBA Code to put hyperlink into email

[Gilesbee1]

Hi,

First post in here & getting to grips with VBA so please bear with me!

I'm setting up an Excel file that needs to be completed by various functions within our business on a step by step basis, with each function hitting a button once they've completed their information so that it notifies the next person in the chain to start work on their part. All that is sorted & good.
What I'd like to do, to save people having to hunt down the file in our network drives, is when the person working on the file clicks the button to send it to the next recipient, the file is saved and then the VBA puts a hyperlink to the file in the body of the email.

Is this easy to do?!

Cheers for any help!

 

[Micron]

Should not be too difficult as long as the email body is created as html. Then html coding can be used to create the file link. VBA is what creates the email body.

W3Schools online HTML editor

The W3Schools online code editor allows you to edit code and view the result in your browser

www.w3schools.com

 

[Micron]

Sorry, I posted a link to the incorrect topic. That site will help you with html though. Your link would look something like

<a href="file:///C:/Users/Micron/AccessStuff/Links.txt</a>

If your file path contains spaces, I think you are going to start learning why I hated it when people put spaces in path and file names and I had to deal with it when coding. You will probably have to replace spaces with %20. Note that just because you can send an email with links in it does not mean people are going to be able to open the files. For one, they must have network permissions on the file location. Plus, network security may prevent them from being able to do so even if they have the required permissions.

 

[Gilesbee1]

Sorry, I posted a link to the incorrect topic. That site will help you with html though. Your link would look something like

<a href="file:///C:/Users/Micron/AccessStuff/Links.txt</a>

If your file path contains spaces, I think you are going to start learning why I hated it when people put spaces in path and file names and I had to deal with it when coding. You will probably have to replace spaces with %20. Note that just because you can send an email with links in it does not mean people are going to be able to open the files. For one, they must have network permissions on the file location. Plus, network security may prevent them from being able to do so even if they have the required permissions.

Hi Micron,
Many thanks for your response - yes I've had to put the %20 into all the spaces! Next problem is that the macro runs ok and puts a hyperlink into the email that is named correctly with the file name but it only links to the directory not the file itself. To clarify, I'm working on a file called "NPI 999" which is buried in a directory thus

\\WBHFILE01\Data\WBH Public\NPI Master Register\NPI Documents\NPI 999.xlsm

So I've coded it like this:

Please click the hyperlink below to view the file </p>" _
& "<a href=""\\WBHFILE01\Data\WBH%20Public\NPI%20Master%20Register\NPI%20Documents"">" & ActiveWorkbook.Name & "</a>"

But it takes me to the directory not the file. Are you able to decipher where I'm going wrong? Apologies, like I say I'm a novice at this but determined to learn more!

Cheers

 

[Micron]

Looks like you're missing the last slash before the file name.
Troubleshooting 101 tip: put your concatenation into a variable and in your code, debug.print the variable, step through the code (F8) and check the printout in the immediate window.
Or learn how to use the immediate window to check your concatenation: ?"myPathStringHere & Activeworkbook.Name (missing characters/spaces should be obvious)
Or learn how to use the watch window.
BTW, fairly certain that hyperlink paths use / and not \