Email workbooks in a folder

[jeh]

Hello,

I have a daily routine of running after-the-fact reports and then going to the different folders where the workbooks are saved and then emailing them to the different people.

Is there a way that I could email workbooks without opening them, and can I do this based on dates?

If Excel isn't the way to go here, does anyone have any suggestions?

Thanks for your time,

Jim

 

[Smitty]

Is there a way that I could email workbooks without opening them, and can I do this based on dates?

What's your e-mail client?

If it's Outlook, see www.rondebruin.nl/sendmail.htm

HTH,

Smitty

 

[Tazguy37]

I don't have time to do it tonight, but you could use the FileSearch object to loop through all the files in a folder, and find the ones you want. Insert each filename as a hyperlink into a worksheet and email that.

Shouldn't be too difficult to crank out tomorrow, I have to come to work anyway (to babysit those who can't tell the difference between test problems and hardware problems).

(If you have the VBA and Macros for Microsoft Excel book, a good starting point is on page 115. Hope that helps! Back tomorrow!

 

[jeh]

Thanks Taz & Penny

I'll see what I can do with your advice. Thanks again for your time!


Jim

 

[jeh]

Thanks Taz & Penny

I'll see what I can do with your advice. Thanks again for your time!


Jim

 

[Tazguy37]

Here's something I think you can use. You might want to modify it some, depending. It'll take all the files in a path (filespec and path are hardcoded at this point) and write the path and filename (as a hyperlink) in column A, and the time/date the file was last modified in column B. It then sorts (oldest first) on the time/date. Hope this gets you closer!

Sub listWorkbookLinks()
Dim filesToProcess, i, j, nextRow As Long
Dim thisEntry As String
Dim fs, f

    'This macro outputs a list of links to all .xls
    'files from the specified folder and all subfolders

    'Clear all cells in the sheet
    Cells.Clear
    
    'Add headings
    Range("A1:B1").Value = Array("Filename", "Date Last Modified")
    nextRow = 2
    
    'Use the Filesearch Object
    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\Documents and Settings\sewardt\Desktop\"
        .SearchSubFolders = True
        .FileName = "*.xls"
        .Execute
        filesToProcess = .FoundFiles.Count
        'loop through each workbook in the directory
        For i = 1 To .FoundFiles.Count
            thisEntry = .FoundFiles(i)              'get the next file
            ActiveSheet.Hyperlinks.Add anchor:=Range("A" & nextRow), Address:=thisEntry 'add to sheet as link
            Set fs = CreateObject("Scripting.FileSystemObject")
            Set f = fs.GetFile(thisEntry)
            Cells(nextRow, 2) = f.DateLastModified  'put the date in
            nextRow = nextRow + 1
        Next i
    End With
    Cells.Columns.AutoFit
    Columns("A:B").Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("A2") _
        , Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
        
End Sub

 

[jeh]

Wow Taz - Way cool. I think I can work with this.

Thank you very much,

Jim

 

[Tazguy37]

Pretty cool, huh? I played with that for a while from the original. You could put in inputboxes for the starting path, filespec, and whether you want to look in subfolders or not, but the basics are all there. That was fun to do!