Consulting

Results 1 to 4 of 4

Thread: Extracting excel files from emails.

  1. #1
    VBAX Newbie
    Joined
    Jun 2014
    Posts
    4
    Location

    Extracting excel files from emails.

    I am writing to request some help in regards extracting excel attachments from outlook emails.

    I have found the following code which is able to extract all attachments from specific outlook email folder. However, I would like the code below to be able to do the following:
    · Extract only excel attachments formats = xls, xlsx & csv.
    · Extract all the attachments attachment into sheets of one workbook – excel file, instead of multiple excel files in the ‘outlook file’ folder.

    Sub GetAttachments()
    
     Dim ns As NameSpace
     Dim Inbox As MAPIFolder
     Dim Item As Object
     Dim Atmt As Attachment
     Dim FileName As String
     Dim i As Integer
     Dim myExt As String
     
     Set ns = GetNamespace("MAPI")
     Set Inbox = ns.GetDefaultFolder(olFolderInbox).Folders("Offer")
     i = 0
    
    
    If Inbox.Items.Count = 0 Then
        MsgBox "There are no messages in the Inbox.", vbInformation, _
               "Nothing Found"
        Exit Sub
     End If
     
     For Each Item In Inbox.Items
        For Each Atmt In Item.Attachments
        
          Select Case myExt
           Case "xls", "xlsm", "xlsx"
                    
           FileName = "C:\Users\Jun\Desktop\Outlook_files\" & Atmt.FileName
           Atmt.SaveAsFile FileName
           i = i + 1
        Case Else
                'do nothing
                End Select
        Next Atmt
     Next Item
    
    
    GetAttachments_exit:
       Set Atmt = Nothing
       Set Item = Nothing
       Set ns = Nothing
       Exit Sub
    
    
    End Sub
    Any further assistance or help would be very much appreciated. Many Thanks.
    Last edited by jun25; 08-17-2015 at 07:49 AM.

  2. #2
    You haven't defined what myExt refers to and you have a counter 'i' which doesn't appear to be doing anything useful
    myExt = Mid(Atmt.FileName, InStrRev(Atmt.FileName, Chr(46)))
                Select Case myExt
                    Case ".xls", ".xlsx", ".csv"
    will give you the required attachments.
    You will have to save them as separate files before you can do anything with them. However it is not clear which workbook you want to import the files, but whichever it is you will not be able to do that until they are saved as separate files
    Atmt.SaveAsFile FileName
                        'Import the saved file to Excel here
                        '
    I think I would be inclined to make a sub that imports the current file into the workbook of choice and call it where indicated.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    VBAX Newbie
    Joined
    Jun 2014
    Posts
    4
    Location
    Hi, Thank you for your time and great help. I really appreciate your feedback and I am very thankful for your time and support.
    Many thanks.

  4. #4
    Helpedadvicegmayor?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •