Hello,
I am fairly new to VBA in Excel. Can you please provide help with an issue I am having? I have found and slightly modified a VBA macro that automatically extracts xls. or xlsx. email attachments from any unread email in my inbox and saves the attachment to a folder on my hard drive. The macro works great but I require it to go a bit more granular.
Can you please show me how I can make the macro extract the attachments from emails that are in a subfolder and not from my inbox?
Ideally I would have like to have used a VBA script in Outlook, but I am limited by our IT policy here. This is why I have chosen to run this macro through Excel and not use a script in Outlook.
Also, because I cannot use script within Outlook, is there any way to have this macro activated every time a new email comes into the subfolder? Could this be achieved through an ItemAdd VBA in Outlook? Or is there a way to achieve this through Excel? (I wasn't sure if I should make a separate thread for this question or not)
Thank you for any help you can provide.
Below is the macro I am currently using:
I am fairly new to VBA in Excel. Can you please provide help with an issue I am having? I have found and slightly modified a VBA macro that automatically extracts xls. or xlsx. email attachments from any unread email in my inbox and saves the attachment to a folder on my hard drive. The macro works great but I require it to go a bit more granular.
Can you please show me how I can make the macro extract the attachments from emails that are in a subfolder and not from my inbox?
Ideally I would have like to have used a VBA script in Outlook, but I am limited by our IT policy here. This is why I have chosen to run this macro through Excel and not use a script in Outlook.
Also, because I cannot use script within Outlook, is there any way to have this macro activated every time a new email comes into the subfolder? Could this be achieved through an ItemAdd VBA in Outlook? Or is there a way to achieve this through Excel? (I wasn't sure if I should make a separate thread for this question or not)
Thank you for any help you can provide.
Below is the macro I am currently using:
Code:
Sub GetAttachments()
' This Outlook macro checks a the Outlook Inbox for messages
' with attached files (of any type) and saves them to disk.
' NOTE: make sure the specified save folder exists before
' running the macro.
On Error GoTo GetAttachments_err
' Declare variables
Dim ns As Namespace
Dim Inbox As MAPIFolder
Dim Item As Object
Dim Atmt As Attachment
Dim FileName As String
Dim i As Integer
Set ns = GetNamespace("MAPI")
Set Inbox = ns.GetDefaultFolder(olFolderInbox)
i = 0
' Check Inbox for messages and exit of none found
If Inbox.Items.Count = 0 Then
MsgBox "There are no messages in the Inbox.", vbInformation, _
"Nothing Found"
Exit Sub
End If
' Check each message for attachments
For Each Item In Inbox.Items
If Item.UnRead = True Then 'Add this for checking unread emails
' Save any attachments found
For Each Atmt In Item.Attachments
If (Right(Atmt.FileName, 4) = "xlsx") Or (Right(Atmt.FileName, 4) = ".xls") Then
' This path must exist! Change folder name as necessary.
FileName = "S:\Maintenance\Test\" & _
Format(Item.CreationTime, "yyyymmdd_hhnnss_") & Atmt.FileName
Atmt.SaveAsFile FileName
Item.UnRead = False 'Mark email item as read
i = i + 1
End If
Next Atmt
End If
Next Item
' Show summary message
If i > 0 Then
MsgBox "I found " & i & " attached files." _
& vbCrLf & "I have saved them into the S:\Maintenance\Test." _
& vbCrLf & vbCrLf & "Have a nice day.", vbInformation, "Finished!"
Else
MsgBox "I didn't find any attached files in your mail.", vbInformation, "Finished!"
End If
' Clear memory
GetAttachments_exit:
Set Atmt = Nothing
Set Item = Nothing
Set ns = Nothing
Exit Sub
' Handle errors
GetAttachments_err:
MsgBox "An unexpected error has occurred." _
& vbCrLf & "Please note and report the following information." _
& vbCrLf & "Macro Name: GetAttachments" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description: " & Err.Description _
, vbCritical, "Error!"
Resume GetAttachments_exit
End Sub