Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    Unanswered: import data from XLS mail attachment from many mails

    i have 100 inbound e-mails (currently in an outlook folder, but I'm happy to move them anywhere). each mail has an attached single-sheet XLS with data i want to get into a table.

    XLS have identical layout, but 50 cells in each XLS contain the data i'm looking for. rest of XLS is text to be discarded.

    anyone got some hints for A2000 code something like this:

    dim var1, var2, var3 etc
    for each message in WhereEver
    open attached XLS
    var1 = XLS.C5.value
    var2 = XLS.C6.value
    var3 = XLS.C7.value
    ...etc
    close attached XLS
    next message

    i would prefer to avoid manually SaveAs-ing each of the attachments.

    thanks for any hints.

    izy

  2. #2
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122
    Dim myOlApp
    Dim myNameSpace
    Dim myFolder
    Dim myItem
    Dim myDestFolder
    Dim i


    Set myOlApp = CreateObject("Outlook.Application")
    Set myNamespace = myOlApp.GetNamespace("MAPI")
    Set myFolder = myNameSpace.Folders("Personal Folders").Folders("Inbox")
    Set myDestFolder = myNameSpace.Folders("Personal Folders").Folders("ProcessedItems")

    While myFolder.Items.Count > 0
    Set myItem = myFolder.Items(0)

    For i = 0 to myItem.Attachments.Count
    myItem.Attachments(i).SaveAsFile "C:\My Documents\" & _
    myItem.Attachments.(i).DisplayName

    'Now you have the attachment saved as a file on disk
    'you could link the file or import or manipulate it through an Excel object.
    'Insert your code from here
    '-------------------------------
    'until here

    Kill "C:\My Documents\" & _
    myItem.Attachments.(i).DisplayName
    Next i
    MyFolder.Items.Move myDestFolder
    Wend


    Set myItem = Nothing
    Set myDestFolder= Nothing
    Set myFolder =Nothing
    Set myNameSpace = Nothing
    Set myOLApp = Nothing


    Hope I haven't made too many typing mistakes...

    Good luck

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    thanks Danny


    izy

Posting Permissions

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