E-Mail as Hyperlink
 

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. :confused:

Thanks in Advance

--
BN-C
-----------------------------------------------------------------------
BN-CD's Profile: http://www.excelforum.com/member.php...fo&userid=3537
View this thread: http://www.excelforum.com/showthread.php?threadid=55148

E-Mail as Hyperlink
 

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"



--
Regards Ron De Bruin
http://www.rondebruin.nl



"BN-CD" wrote in message
...

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

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

Thanks in Advance!


--
BN-CD
------------------------------------------------------------------------
BN-CD's Profile: http://www.excelforum.com/member.php...o&userid=35374
View this thread: http://www.excelforum.com/showthread...hreadid=551489

E-Mail as Hyperlink
 

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"
--------------------

I'm "learning as I go" here so any help is appriciated.


--
BN-CD
------------------------------------------------------------------------
BN-CD's Profile: http://www.excelforum.com/member.php...o&userid=35374
View this thread: http://www.excelforum.com/showthread...hreadid=551489

E-Mail as Hyperlink
 

One way is to use the date and time

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



--
Regards Ron De Bruin
http://www.rondebruin.nl



"BN-CD" wrote in message
...

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"
--------------------

I'm "learning as I go" here so any help is appriciated.


--
BN-CD
------------------------------------------------------------------------
BN-CD's Profile: http://www.excelforum.com/member.php...o&userid=35374
View this thread: http://www.excelforum.com/showthread...hreadid=551489