Htykuut

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. I have no experience with VBA macros so please help :). Important: it has to be an outlook macro and not an excel macro.

I have the following code but it is giving me error

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

Sub WriteTextFile()



    Dim wkb As Workbook

    Set wkb = Workbooks.Open("C:UsersbebxadvypatDesktopTest VBA Macros.xlsx")



    Set objExcel = CreateObject("Excel.Application")

    objExcel.Visible = True

    Set Workbook = objExcel.Workbooks.Open("C:UsersbebxadvypatDesktopTest 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

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