Solved

Excel VBA - Attach All Files in a Directory

Posted on 2011-09-21
7
668 Views
Last Modified: 2012-05-12
What's the code to attach all files in a directory?

This is what I have...

            With OutMail
                .To = Range("Rde_Reviewer2_GatekeeperEmail").Value
                .CC = Range("Rd_Reviewer2ReqCC_Email").Value
                .Subject = "Unit Review of LOGCAP CO: " _
                    & v_ReqName & " - " _
                    & Range("c_db1_PPRnum").Value
                .DeliveryReceiptRequested = True
                .OriginatorDeliveryReportRequested = True
                .Body = strBody

                '//put code here to attach all files in c:\MainDir\SubDir1\SubDir2\*.*
                .Attachments.Add    ????

            End With

Thanks, --Andres
0
Comment
Question by:AndresHernando
  • 3
  • 3
7 Comments
 
LVL 4

Expert Comment

by:jmdl1983
ID: 36573994
Sub openAllfilesInALocation()
Dim i as integer, wb as workbook
With Application.FileSearch
..NewSearch
..LookIn = "c:\MainDir\SubDir1\SubDir2\"
..SearchSubFolders = False
..FileName = "*.*"
..Execute
For i = 1 To .FoundFiles.Count
 'Put Your Attach Code Here Now
Next i
End With
End Sub
0
 
LVL 33

Expert Comment

by:Norie
ID: 36574009
Try this.
strPath = "C:\MainDir\SubDir1\Subdir2\"
strFileName = Dir(strPath & "*.*")

Do While Len(strFilename)>0

       .Attachments.Add strPath & strFileName

Loop

Open in new window

0
 

Author Comment

by:AndresHernando
ID: 36579221
imnorie, your code is very close...  However, it's an infinite loop that attaches the first file in the dir over and over.

Is there a line we can insert to "kill"  (delete) each file as it is attached?

Thanks, --Andres
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 33

Expert Comment

by:Norie
ID: 36579625
Add this in the loop:

strFileName = Dir

It will repeat the Dir and return the next file to attach.
0
 

Author Comment

by:AndresHernando
ID: 36580709
imnorie, I added the line of code "strFileName = Dir" (see below) but still have same result:  first file in Dir gets attached over and over in an infinite loop.

What line in the code gets Excel to look for the next file?

    strPath = v_DirSaveTo
    strFileName = Dir(strPath & "*.*")
    i = 0
   
    Do While Len(strFileName) > 0
        .Attachments.Add strPath & strFileName
        strFileName = Dir
        strFileName = Dir(strPath & "*.*")

        i = i + 1
        If i > 15 Then Exit Do
    Loop

Thanks, --Andres
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 36580898
Why did you repeat the initial Dir in the loop?

It's not needed and will actually reset the Dir back to the first file.

This is the whole loop.
Do While Len(strFileName) > 0
        .Attachments.Add strPath & strFileName
        strFileName = Dir
Loop

Open in new window

0
 

Author Comment

by:AndresHernando
ID: 36596989
imnorie,  sorry for the slow response.  Your code works great!   Thanks!!!  --Andres
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now