Attachment Download

vmjan02

Active Member
Try this please:

Run the macro and select the folder that contains the emails.

Code:
Sub download_attachments()
  Dim olApp As Outlook.Application, olmail As MailItem, Att As Object
  Dim olFolder As Outlook.Folder, sPath As String, y As Long, strfile As String
  sPath = ThisWorkbook.Path & "\"
  '
  Set olApp = CreateObject("Outlook.application")
  Set olmail = olApp.CreateItem(olMailItem)
  Set olFolder = olApp.GetNamespace("MAPI").PickFolder
  '
  For Each olmail In olFolder.Items
    If TypeName(olmail) = "MailItem" Then
      y = 1
      For Each Att In olmail.Attachments
        strfile = sPath & olmail.Attachments.Item(y).Filename
        olmail.Attachments.Item(y).SaveAsFile strfile
        y = y + 1
      Next Att
    End If
  Next
  MsgBox "Done"
End Sub
getting error on olApp As Outlook.Application -- "user-defined type not defined"
 

DanteAmor

Well-known Member
To automate Outlook based tasks from Excel you need to add Outlook Object Library (Microsoft Outlook XX.X Object Library) in Excel References. You can follow below steps to add Outlook reference in Excel VBA:

1. From the Menu Bar, click on Tools > References

2. Select ‘Microsoft Outlook XX.X Object Library’ and click on ‘OK’ button
 
Last edited:

ZVI

MrExcel MVP
getting error on olApp As Outlook.Application -- "user-defined type not defined"
In fact, this should not happen if the code is in the Outlook module,
as it was mentioned in the first post:
I have the below code and have it is on Outlook VBA on ThisOutlookSession
So where actually the code is?
What version of MS Office are you using?
 

DanteAmor

Well-known Member
Another update, try this please.

Code:
Sub download_attachments()
  'Dim olApp As Outlook.Application, olmail As MailItem, Att As Object
  Dim olApp As Object, olmail As Object, Att As Object
  Dim olFolder As Object, sPath As String, y As Long, strfile As String
  sPath = ThisWorkbook.Path & "\"
  '
  Set olApp = CreateObject("Outlook.application")
  Set olmail = olApp.CreateItem(0)
  Set olFolder = olApp.GetNamespace("MAPI").PickFolder
  '
  For Each olmail In olFolder.Items
    If TypeName(olmail) = "MailItem" Then
      y = 1
      For Each Att In olmail.Attachments
        strfile = sPath & olmail.Attachments.Item(y).Filename
        olmail.Attachments.Item(y).SaveAsFile strfile
        y = y + 1
      Next Att
    End If
  Next
  MsgBox "Done"
End Sub
 

vmjan02

Active Member
In fact, this should not happen if the code is in the Outlook module,
as it was mentioned in the first post:

So where actually the code is?
What version of MS Office are you using?
Code is not in outlook module. and version is 2010
 

ZVI

MrExcel MVP
Code is not in outlook module. and version is 2010
Thanks for the clarification, but please bear in mind that my code posted in this thread should be in the ThisOutlookSession Outlook's module.
 
Last edited:

DanteAmor

Well-known Member
Hi @vmjan02,
Did you try the codes again? in post #26 or the previous code with the reference update.
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top