File Lookup and log into spreadsheet

[davidtown23]

I am needing code to do the following for a file logging and tracking application.

First. Code to open a certain drive letter on the network. then the user can choose the file, but once it is chosen the code will imput the file name in column B, the File Date in column D and provide a hyperlink to the file in Column E (The text displayed on the hyper link will always be PDF).

(need to use for multiple files to create an ongoing file log)

Second. Once these are filled in, the user will enter the co-worker's id in column C, then code will automatically send the coworker an email notification of the file received. (the coworker's email address is their ID typed in colum C if that helps)

(I have created this shop drawing submittal program, but I want to make it more automated with these codes)

[austenr]

Dont have time to put it all together but here is what you need:

1. [vba]GetOpenFilename("Excel Files (*.xls), *.xls")[/vba]
opens a dialogue box where the user can select the file they want.

2. [vba]Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Workbooks("MyWorkbook.xls")
.Sheets("Sheet1").Range("A65000").End(xlUp).Offset(1, 0) = ThisWorkbook.Name
& " was last saved on " & Format(Date, "mm-dd-yy") & " at " &
Format(Time, "hh:mm")
End With
End Sub [/vba]
This is a UDF and goes in the workbook module, Before Save. Change the Sheets, Workbooks and range to suit your needs.
3. [vba]
Sub Send1Sheet_ActiveWorkbook()'Creates a new Workbook Containing 1 Sheet _ and sends as attachment.
With ActiveWorkbook
.Sheets(1).Copy .SendMail Recipients:="myname@vbaexpress.com", _ Subject:="Try Me " & Format(Date, "dd/mmm/yy")
.Close
SaveChanges:=False
End With
End Sub[/vba]

You will need to adjust the recipient and subject to suit.

Im sure someone will be along to help finish it.

[davidtown23]

Thanks for the quick response. I will start with these that you listed.