Microsoft Outlook is one the best email processing application used by user on daily basis. Sometimes, it is used for bulk data transfer medium. users sending bulk emails consist of lots of attached file of various extension such as excel (Microsoft Excel), word (Microsoft Word), PDF (Adobe), Text files, Images (Photoshop file) and so on. Then, it becomes very tedious job to handle large number of attachment in Microsoft Outlook. Saving bulk attachment into specific folder takes lot of productive time which definitely impact on other activities.
Using excel VBA code this task can be automate which will definitely save lot of productive hours. This, VBA code will take hardly 5 minutes to save attachment into specific folder of your Personal Computer.
Here is the code which will save email attachment to your specific folder:
Sub Save_email_attachment_to_folder()
' Author: Dreams24
' Written for VBA Tricks and tips blog
'Parameter 1 = Folder name of folder inside your Inbox e.g. Test_Folder
'Parameter 2 = File extension, "" is every file e.g. .xls, .pdf, .doc and so on.
'Parameter 3 = Save folder, "C:\Users\Dreams\Desktop\Test_Folder" or ""
' If you use "" it will create a date/time stamped folder for you in your "Documents" folder
' Note: If you use this "C:\Users\Dreams\Desktop\Test_Folder\" the folder must exist.
Save_Email_Attachments_To_Folder "Test", "", "C:\Users\Dreams\Desktop\Test_Folder"
End Sub
Write Below function just below above macro:
Function Save_Email_Attachments_To_Folder(OutlookFolderInInbox As String, ExtString As String, DestFolder As String)
' Author: Dreams24
' Written for VBA Tricks and tips blog
Dim ns As Namespace
Dim Inbox As MAPIFolder
Dim SubFolder As MAPIFolder
Dim Item As Object
Dim Atmt As Attachment
Dim FileName As String
Dim MyDocPath As String
Dim i As Integer
Dim wsh As Object
Dim fs As Object
Application.DisplayAlerts = False
Application.ScreenUpdating = False
On Error GoTo Handle_err:
Set ns = GetNamespace("MAPI")
Set Inbox = ns.GetDefaultFolder(olFolderInbox)
Set SubFolder = Inbox.Folders(OutlookFolderInInbox)
i = 0
' Check subfolder for messages and exit of none found
If SubFolder.Items.Count = 0 Then
MsgBox "There are no messages in this folder : " & OutlookFolderInInbox, _
vbInformation, "Nothing Found"
Set SubFolder = Nothing
Set Inbox = Nothing
Set ns = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Exit Function
End If
'Create DestFolder when folder not exists
If DestFolder = "" Then
Set wsh = CreateObject("WScript.Shell")
Set fs = CreateObject("Scripting.FileSystemObject")
MyDocPath = wsh.SpecialFolders.Item("mydocuments")
DestFolder = MyDocPath & "\" & Format(Now, "dd_mmm_yyyy hh_mm_ss")
If Not fs.FolderExists(DestFolder) Then
fs.CreateFolder DestFolder
End If
End If
If Right(DestFolder, 1) <> "\" Then
DestFolder = DestFolder & "\"
End If
' Check each message for attachments and extensions
For Each Item In SubFolder.Items
For Each Atmt In Item.Attachments
If LCase(Right(Atmt.FileName, Len(ExtString))) = LCase(ExtString) Then
FileName = DestFolder & " " & Atmt.FileName
Atmt.SaveAsFile FileName
i = i + 1
End If
Next Atmt
Next Item
' Show this message when Finished
If i > 0 Then
MsgBox "Please Browse below path for your files: " _
& DestFolder, vbInformation, "Finished!"
Else
MsgBox "No attached files in your mail.", vbInformation, "Finished!"
End If
' Code to Release memory
Set SubFolder = Nothing
Set Inbox = Nothing
Set ns = Nothing
Set fs = Nothing
Set wsh = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Exit Function
' Code for Error Handling
Handle_err:
MsgBox "An unexpected error has occurred." _
& vbCrLf & "Please note and report the following information." _
& vbCrLf & "Macro Name: Save_email_attachment_to_folder" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description: " & Err.Description _
, vbCritical, "Error!"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Function