Jedrei over at outlookforums.com wanted to send all of the files in a folder as email attachments, one attachment per message.
The following macro attaches one file to an email message and sends it, then sends the next file in the folder, repeating until all files are sent.
When it's finished, a dialog box tells you how many files were sent. It also lists the file names in the Immediate window, if you want to review and confirm each file was processed and sent.
To use this macro, open the VBA editor, add a new module and paste the code into it. Change fldName to use the correct path then run the macro.
If you need a record of the files sent, press Ctrl+G to open the Immediate window in the VBA Editor to view the list created by Debug.Print fName.
Dim fldName As String Sub SendFilesbuEmail() ' From http://slipstick.me/njpnx Dim sFName As String i = 0 fldName = "C:\Users\Diane\" sFName = Dir(fldName) Do While Len(sFName) > 0 Call SendasAttachment(sFName) sFName = Dir i = i + 1 Debug.Print fName Loop MsgBox i & " files were sent" End Sub Function SendasAttachment(fName As String) Dim olApp As Outlook.Application Dim olMsg As Outlook.MailItem Dim olAtt As Outlook.Attachments Set olApp = Outlook.Application Set olMsg = olApp.CreateItem(0) ' email Set olAtt = olMsg.Attachments ' attach file olAtt.Add (fldName & fName) ' send message With olMsg .Subject = "Here's that file you wanted" .To = "email@example.com" .HTMLBody = "Hi " & olMsg.To & ", <br /><br /> I have attached " & fName & " as you requested." .Send End With End Function
Send all attachments in one message
When you want to send all attachments that are in a folder, you'll need to loop through the attachments in the function to add them to one message. The following example offers two options: add all items in the folder or add only certain file types to the message.
Sub SendFilesbyEmail() Call SendFiles("C:\Users\diane\Test\") ' use one line per file type 'Call SendFiles("C:\Users\diane\Test\", "*.docx") 'Call SendFiles("C:\Users\diane\Test\", "*.txt") End Sub Function SendFiles(fldName As String, Optional FileType As String = "*.*") Dim fName As String Dim sAttName As String Dim olApp As Outlook.Application Dim olMsg As Outlook.MailItem Dim olAtt As Outlook.Attachments Set olApp = Outlook.Application Set olMsg = olApp.CreateItem(0) ' email Set olAtt = olMsg.Attachments ' to send all fName = Dir(fldName) 'to send only certain extensions 'fName = Dir(fldName & FileType) Do While Len(fName) > 0 olAtt.Add fldName & fName sAttName = fName & "<br /> " & sAttName Debug.Print fName fName = Dir Loop ' send message With olMsg .Subject = "Here's that file you wanted" .To = "firstname.lastname@example.org" .HTMLBody = "Hi " & olMsg.To & ", <br /><br /> I have attached <br /> " & sAttName & "as you requested." .Display End With End Function
Attach files beginning with specific characters
' place at the top with other Dims Dim strName As String strName = InputBox("Enter first 4 characters of filename") Do While Len(fName) > 0 If Left(fName, 4) = strName Then olAtt.Add fldName & fName sAttName = fName & "<br />" & sAttName End If fName = Dir Loop
How to use macros
First: You will need macro security set to low during testing.
To check your macro security in Outlook 2010 or 2013, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, it’s at Tools, Macro Security.
After you test the macro and see that it works, you can either leave macro security set to low or sign the macro.
Open the VBA Editor by pressing Alt+F11 on your keyboard.
To put the code in a module:
- Right click on Project1 and choose Insert > Module
- Copy and paste the macro into the new module.
More information as well as screenshots are at How to use the VBA Editor