Email workbooks in a folder

jeh

Active Member
Joined
Apr 27, 2002
Messages
250
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
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

Active Member
Joined
Apr 27, 2002
Messages
250
Thanks Taz & Penny

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


Jim
 

jeh

Active Member
Joined
Apr 27, 2002
Messages
250

ADVERTISEMENT

Thanks Taz & Penny

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


Jim
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
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!

Code:
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

Active Member
Joined
Apr 27, 2002
Messages
250
Wow Taz - Way cool. I think I can work with this.

Thank you very much,

Jim
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
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! :LOL:
 

Forum statistics

Threads
1,147,623
Messages
5,742,208
Members
423,712
Latest member
edzubur

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top