VBA that will send with mail all files from folder

**forfiett** · 08-24-2014, 09:24 AM

Good day to all.

I need a code that will send with e-mail (outlook) all files from a defined folder. In that folder will be max 10 PDF files.

Thanks in advance.

Best regards,
Forfiett

**fredlo2010** · 08-24-2014, 10:56 AM

Hi,

Maybe this

Sub SendFilesInFolder()

    Dim otApp As Object
    Dim otEmail As Object
    
    Dim fso As Object
    Dim fsoFolder As Object
    Dim fsoFile As Object
    
    Const strFOLDER_PATH As String = "C:\Users\Alfred\Desktop\x"
    
    ' Initialize the variables
    Set otApp = CreateObject("Outlook.Application")
    Set otEmail = otApp.CreateItem(0)
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fsoFolder = fso.GetFolder(strFOLDER_PATH)
    
    
    ' Create the email
    With otEmail
        .To = "my@gmail.com"
        .Subject = "Files Enclosed"
        .Body = "Pleasse find attached the requested files."
        
        ' Loop through the files in folder and add them
        For Each fsoFile In fsoFolder.Files
            .Attachments.Add fsoFile.Path, 1
        Next
        
        .Display
        
        ' If you want to send it directly then uncomment below
        '.Send
        
    End With
    
    ' Clean up
    Set otApp = Nothing
    Set otEmail = Nothing
    Set fso = Nothing
    Set fsoFolder = Nothing
    Set fsoFile = Nothing
    
    
End Sub

Thanks

**forfiett** · 08-25-2014, 07:37 AM

Dear,

thanks, it is working perfectly..but it is possible to use HTMLBody..how to do it?

**fredlo2010** · 08-25-2014, 08:53 AM

Hi,

Try this. Now heads up HTMLbody can be a headache lol Things not always come out the way you want them to so you might need to play with the HTML section of the body for a bit before you get expected output.

Option Explicit

Sub SendFilesInFolder()

    Dim otApp As Object
    Dim otEmail As Object
    
    Dim fso As Object
    Dim fsoFolder As Object
    Dim fsoFile As Object
    
    Const strFOLDER_PATH As String = "C:\Users\Alfred\Desktop\x"
    
    ' Initialize the variables
    Set otApp = CreateObject("Outlook.Application")
    Set otEmail = otApp.CreateItem(0)
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fsoFolder = fso.GetFolder(strFOLDER_PATH)
    
    
    ' Create the email
    With otEmail
        .To = "my@gmail.com"
        .Subject = "Files Enclosed"
        .BodyFormat = 2
        .HTMLBody = "<HTML><BODY><b>Please</b> find attached the requested files. </BODY></HTML>"
            
        ' Loop through the files in folder and add them
        For Each fsoFile In fsoFolder.Files
            .Attachments.Add fsoFile.path, 1
        Next
        
        .Display
        
        ' If you want to send it directly then uncomment below
        '.Send
        
    End With
    
    ' Clean up
    Set otApp = Nothing
    Set otEmail = Nothing
    Set fso = Nothing
    Set fsoFolder = Nothing
    Set fsoFile = Nothing
    
End

Thanks

**forfiett** · 08-26-2014, 02:46 PM

Dear,

Thanks for help. Suggest to use HTML5, as bellow:

"<span style='font-family:calibri;font-size:11pt'>" & "Good day," & "<br"> & _

Best regards,
forfiett

VBA that will send with mail all files from folder

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA that will send with mail all files from folder

Re: VBA that will send with mail all files from folder

Re: VBA that will send with mail all files from folder

Re: VBA that will send with mail all files from folder

Re: VBA that will send with mail all files from folder

Thread Information

Users Browsing this Thread

Similar Threads

How to Send automated mails with attachment files saved in a folder?

Macro to send a mail on last day of every month if last day is sat or sunday send mail on

[SOLVED] attach file from folder on c:\ to outlook mail and send

Macro code should find inv no in folder and send attachhed mail.

How to e-mail selected row and use e-mail address in a cell to send e-mail from excel

Bookmarks

Bookmarks

Posting Permissions