E-Mail as Hyperlink

[BN-CD]

I have a macro that generates an investigation form, saves it to th
network. I would like to place a button on the worksheet to send th
worksheet as a hyperlink. This way when the people who receive it ca
fill it out and just save it when they are complete.
I have read through several (seems like 100's ) posts and help file
but can't seem to find the right code to make an e-mail with
hyperlink. Being new to writing macros I'm pretty much confused at thi
point. (too much research...LOL)

Everything I have tried makes the workbook an attachement not
hyperlink.

Thanks in Advance

 

[Ron de Bruin]

Hi

If you use Outlook ?
http://www.rondebruin.nl/sendmail.htm


Use links in the body

..Body = "file://Yourcomputer/YourFolder/Week2.xls"

If there are spaces use %20
..Body = "file://Yourcomputer/YourFolder/Week%202.xls"

Example for a file on a website
..Body = "http://www.rondebruin.nl/files/EasyFilter.zip"

 

[BN-CD]

Thanks for pointing me in the right direction. I was able to get it
working right, finally.

One more question, If I may.

I have the macro automatically saving the workbook then e-mailing the
form to a distribution list. The file name that the workbook is saved
under comes from a job and lot number along with the current date all
pulled from sheet1 in workbook. The problem is: there will be cases
where this information will be the same so when the macro tries to save
the workbook it tells you there is already a file names "xx".xls, do you
want to overwrite it. We obviously don't want to overwrite it since we
are making a new form. I'm woundering if there is a way to write the
code so IF there is already a file with the same name in that directory
add an "a" or whatever at the end of the file name. Would this be as
simple as adding an IF statement to the save part of the code? If it is
I don't have a clue how to check to see if the file name already
exists.

If it will help here is the code I have to save the document:


Code:
--------------------
Dim sDept As String
Dim sDate As String
Dim sJob As String
Dim sLot As String
Dim fileloc As String
Dim sShift As String

sDept = Format(ActiveSheet.Range("B3").Value)
sDate = Format(Date, "yymmdd")
sJob = Format(ActiveSheet.Range("A6").Value)
sLot = Format(ActiveSheet.Range("B6").Value)
sShift = Format(ActiveSheet.Range("E6").Value)

fileloc = "G:\GENERAL\Scrap_Investigation\" & sDept & "\" & sJob & "_" & sLot & "_" & sDate

ActiveWorkbook.SaveAs Filename:=fileloc

MsgBox "New file created at: " & fileloc, vbInformation, "File Complete"

 

[Ron de Bruin]

One way is to use the date and time

Format(Now, "dd-mm-yy h-mm-ss")