Attach All Files from Folder

Question

A client prefers their weekly invoice attachments sent in a single e-mail.

I set up the system to send all invoices to me so that I can attach them to a single e-mail.

The macro I created saves all the attachments from e-mails in a specific Outlook folder to a specific folder on my computer. It then drafts an e-mail for me to send to my client.

I need for the macro to attach all the saved files to the drafted e-mail then delete the files from the folder on my computer.

Dim ol As Outlook.Application
Dim ns As Outlook.NameSpace
Dim fol As Outlook.MAPIFolder
Dim i As Object
Dim mi As Outlook.MailItem
Dim at As Outlook.Attachment

Set ol = New Outlook.Application
Set ns = ol.GetNamespace("MAPI")
Set fol = ns.GetDefaultFolder(olFolderInbox)
Set fol = fol.Folders("_CLIENT INVOICES")

For Each i In fol.Items

    If i.Class = olMail Then
    
        Set mi = i
        
        If mi.Attachments.Count > 0 Then
            
            For Each at In mi.Attachments
            
                If Right(at.FileName, 3) = "pdf" Then
            
                    at.SaveAsFile "C:\Users\MYNAME\OneDrive\CLIENT Invoices\" & at.FileName
                        
                End If
                
            Next at
            
        End If
        
    End If

Next i

'Drafting Email

Dim outlookapp As Object
Dim outlookmessage As Object

Set outlookapp = GetObject(Class:="Outlook.Application")
Set outlookmessage = outlookapp.CreateItem(0)

With outlookmessage
    .SentOnBehalfOfName = "OUR EMAIL"
    .To = "CLIENT EMAIL"
    .Subject = "Invoices"
    .Body = "Dear Valued Client," & vbNewLine & vbNewLine & "Attached please find the invoices for       
services provided." & vbNewLine & vbNewLine & "Thank you,"
    .Display
End With
On Error GoTo 0

Set outlookmessage = Nothing
Set outlookapp = Nothing

End Sub

Does this answer your question? [How to attach all files in folder to an email - vba code](https://stackoverflow.com/questions/54218769/how-to-attach-all-files-in-folder-to-an-email-vba-code) — niton, Mar 19 '23 at 12:29

score 0 · Answer 1 · answered Feb 25 '23 at 15:30

First of all, there is no need to create and retrieve an Outlook Application instance in the code. The following lines of code were found:

Set ol = New Outlook.Application

but later for sending an email an instance is retrieved anew:

Set outlookapp = GetObject(Class:="Outlook.Application")

Second, when items are processed in the folder all attachments are saved to the same folder:

For Each at In mi.Attachments
  If Right(at.FileName, 3) = "pdf" Then
    at.SaveAsFile "C:\Users\MYNAME\OneDrive\CLIENT Invoices\" & at.FileName
  End If
Next at

There is a chance that files with the same name could be saved to the same folder with the same, so already saved file can be overwritten. I'd suggest adding any IDs to the filename and do not rely on the FileName property of the attached file. For example, you may consider using the RecievedTime property value.

Third, to attach all files from a folder you can use the following code:

Dim fso As Object
Dim fsFolder As Object
Dim fsFile As Object

Set fso = CreateObject("Scripting.FileSystemObject")
Set fsFolder = fso.GetFolder(strFolder)

For Each fsFile In fsFolder.Files
   If fsFile.Name Like "*.pdf" Then
      .Attachments.Add strFolder & "\" & fsFile.Name  
   End If
Next

Hi @eugeneastafiev ..I keep getting an error when I try inserting the code you provided. Can you update my code to include your suggestions to see if I am off somewhere? Please note that the file name will not be an issue as the invoices are generated in sequential order and there will not be duplicate file names — linzomania, Feb 27 '23 at 13:38

Attach All Files from Folder

1 Answers1