Get mail info into Excel using Outlook VBA

Question

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

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
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
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
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

Alina Li Alina Li · Accepted Answer · 2018-11-23T03:09:11

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

Get mail info into Excel using Outlook VBA

1 Answers