0

I'm trying to extract information from unread e-mails in a specific folder and paste it in a structured Excel file.

I need the subject, the body, the sender, received time, conversation ID and the attachment names.

Important: it has to be an Outlook macro and not an Excel macro.

I have the following code but it is giving me

"runtime error '9': subscript out of range.

Sub WriteTextFile()

    Dim wkb As Workbook
    Set wkb = Workbooks.Open("C:\Users\bebxadvypat\Desktop\Test VBA Macros.xlsx")
    
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    Set Workbook = objExcel.Workbooks.Open("C:\Users\bebxadvypat\Desktop\Test VBA Macros.xlsx")
    
    Dim objNS As Outlook.NameSpace
    Dim objFolder As Outlook.MAPIFolder
    Dim row As Integer
    
    row = 2

    objExcel.Worksheets("Sheet1").Cells(1, 1).Value = "Sender"
    objExcel.Worksheets("Sheet1").Cells(1, 2).Value = "Subject"
    objExcel.Worksheets("Sheet1").Cells(1, 3).Value = "Date"
    objExcel.Worksheets("Sheet1").Cells(1, 4).Value = "ID"
    objExcel.Worksheets("Sheet1").Cells(1, 5).Value = "Body"

    Set objNS = GetNamespace("MAPI")
    Set objFolder = objNS.Folders.GetLast
    Set objFolder = objFolder.Folders("Deleted Items")
    
    For Each item In objFolder.Items
        objExcel.Worksheets("Sheet1").Cells(row, 1).Value = item.sender
        objExcel.Worksheets("Sheet1").Cells(row, 2).Value = item.Subject
        objExcel.Worksheets("Sheet1").Cells(row, 3).Value = item.ReceivedTime
        objExcel.Worksheets("Sheet1").Cells(row, 4).Value = item.ConversationID
        objExcel.Worksheets("Sheet1").Cells(row, 5).Value = item.Body
    Next

    Workbook.Save
    Workbook.Saved = True
    Workbook.Close
    objExcel.Quit
      
    Set Workbook = Nothing
    Set objExcel = Nothing
    
End Sub
Community
  • 1
  • 1
Yanni Pattas
  • 25
  • 7
  • 1
    What line throws the error? – BigBen Nov 22 '18 at 15:29
  • There are only a few things I can see that can throw error 9 - one is the hard-coded sheet name. "Sheet1" is possibly not the actual name of the sheet you want to work with, esp. if Excel isn't installed in English. For example the default sheet name will be "Feuil1" in French. Consider pulling `objExcel.Worksheets(1)` into its own `Worksheet` variable, instead of dereferencing it every single time. The other is the hard-coded "Deleted Items" folder name. If the specified hard-coded string isn't a key in the collection you're pulling from, error 9 is thrown. – Mathieu Guindon Nov 22 '18 at 16:02
  • Hi @MathieuGuindon, indeed the issue was related to the non-english version! thanks a lot. However: a) what line of code should I get to get the attachment names as well? b) how can I download the attachments to a specific folder? c) to actually only do all of this for unread e-mails – Yanni Pattas Nov 22 '18 at 22:07
  • Hit F2 to bring up the *Object Browser*, then find the `MailItem` class and search for something that looks like a collection of attachments. Then look for a class that could conceivably *be* an attachment - that class likely has a `Name` property. – Mathieu Guindon Nov 22 '18 at 22:11
  • You set `objFolder` to `objNS.Folders.GetLast` and then reset it to `objFolder.Folders("Deleted Items")`. Does the folder returned by the first statement contain folder "Deleted Items"? If it does not, that would explain the error 9. Try adding `Debug.Print objFolder.Name` after each `Set` and see what it gives you. – Tony Dallimore Nov 22 '18 at 22:39
  • This answer of mine, https://stackoverflow.com/a/12146315/973283, contains a macro that outputs selected details of every email in Inbox to an Excel worksheet. There is much about this macro that does not conform to your requirement. But it also contains much that is relevant such as accessing the attachments of an email. I think it will be valuable as a source of ideas. – Tony Dallimore Nov 22 '18 at 22:46
  • You open an existing workbook then start output at row 2. If the new macro outputs fewer rows than the previous run, the excess rows will remain. You should either delete all existing rows or add new rows under the previous row. – Tony Dallimore Nov 22 '18 at 22:52

1 Answers1

-1

According to test your code, you could try to add row = row+1 and use this method to get Delete Items:

Set olNs = Application.GetNamespace("MAPI")
Set DeletedFolder = olNs.GetDefaultFolder(olFolderDeletedItems)
    For Each Item In objFolder.Items
        objExcel.Worksheets("Sheet1").Cells(row, 1).Value = Item.Sender
        objExcel.Worksheets("Sheet1").Cells(row, 2).Value = Item.Subject
        objExcel.Worksheets("Sheet1").Cells(row, 3).Value = Item.ReceivedTime
        objExcel.Worksheets("Sheet1").Cells(row, 4).Value = Item.ConversationID
        objExcel.Worksheets("Sheet1").Cells(row, 5).Value = Item.Body
        row = row + 1
    Next
Alina Li
  • 884
  • 1
  • 6
  • 5
  • Incrementing rows would be useful in the final product but the "subscript out of range", according to the OP's comment, was due to a non-English version not recognizing "Sheet1". – niton Nov 23 '18 at 14:26