The Sent folder varies depending on if on Exchange or not. I commented the default way but used the more elaborate method to set the folder for an account.
Run Main() with a blank sheet active. Some property values are limited to just the item in your Sent folder. e.g. ResponseStatus which is just for your response.
First off, here is the Main() routine. The 2nd code block shows how to get the string for your Sent folder or any Outlook folder. Be sure to add the Outlook reference as I commented.
Sub Main()
Dim a, b
'Early Binding: Tools > References > Microsoft Outlook xx.0 Object Library > OK
Dim oApp As Outlook.Application
Dim oG As Outlook.Folder 'Method for IMAP, as used by Gmail.
Dim oM As Outlook.MeetingItem, oAA As Outlook.AppointmentItem
Dim sMsg$, sAdd$, i As Long, j As Long
'Late Binding:
'Dim oApp As Object, oG As Object
Set oApp = CreateObject("Outlook.Application")
'Set oG = oNS.GetDefaultFolder(5) 'olFolderSentMail=5
'Set oG = GetFolderPath("\\ken@gmail.com\[Gmail]\Sent Mail", oApp)
Set oG = GetFolderPath("\\ken@school.edu\Sent Items", oApp)
For i = 1 To oG.Items.Count
'Debug.Print i, TypeName(oG.Items(i))
If TypeName(oG.Items(i)) = "MeetingItem" Then j = j + 1
Next i
If j = 0 Then Exit Sub
ReDim a(1 To j, 1 To 8)
On Error Resume Next
j = 0
For i = 1 To oG.Items.Count
If TypeName(oG.Items(i)) = "MeetingItem" Then
'Set oM = oG.Items(i) 'Let's you use itellisense whereas oG.Items(i) does not.
'Set oAA = oG.Items(i).GetAssociatedAppointment(False)
'With oAA
With oG.Items(i).GetAssociatedAppointment(False)
j = j + 1
a(j, 1) = .Organizer 'Could error if no orgnaizer
a(j, 2) = .Subject
'a(j, 3) = oM.ReceivedTime
a(j, 3) = .CreationTime
a(j, 4) = .Start
a(j, 5) = .Location
a(j, 6) = .RequiredAttendees
a(j, 7) = .OptionalAttendees
'https://docs.microsoft.com/en-us/office/vba/api/outlook.olresponsestatus
a(j, 8) = .ResponseStatus
End With
End If
Next i
On Error GoTo 0
'Title in row 1.
b = Split("Oraganizer,Subject,CreationTime,Start,Location,RequiredAddttendees,OptionalAttendees,ResponseStatus", ",")
[A1].Resize(, UBound(b) + 1) = b
'Data from Outlook Sent folder's MeetingItem properties.
'Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(UBound(a), UBound(a, 2)).Value = a
[A2].Resize(UBound(a), UBound(a, 2)).Value = a
ActiveSheet.UsedRange.EntireColumn.AutoFit
[A1].Select
End Sub
If you use the Default folder, you won't need this. The first routine let's you pick an Outlook folder to get the string for the 2nd function that was called in Main().
'Get the FolderPath string to pass to GetFolderPath().
Sub GetFolder()
Dim olApp As Outlook.Application
Dim olNS As Outlook.Namespace
Dim olFolder As Outlook.MAPIFolder
Set olApp = Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")
Set olFolder = olNS.PickFolder
Debug.Print olFolder.FolderPath
MsgBox olFolder.FolderPath
End Sub
'IMAP, folder path, https://www.slipstick.com/outlook/outlook-2013-imap-folder/
'Similar to, http://www.slipstick.com/developer/working-vba-nondefault-outlook-folders/#GetFolderPath
''Early Binding: Tools > References > Microsoft Outlook xx.0 Object Library > OK
Function GetFolderPath(ByVal FolderPath As String, oApp As Outlook.Application) As Outlook.Folder
Dim oFolder As Outlook.Folder
Dim FoldersArray As Variant
Dim i As Integer
On Error GoTo GetFolderPath_Error
If Left(FolderPath, 2) = "\\" Then
FolderPath = Right(FolderPath, Len(FolderPath) - 2)
End If
'Convert folderpath to array
FoldersArray = Split(FolderPath, "\")
'Set oFolder = Application.Session.Folders.Item(FoldersArray(0))
Set oFolder = oApp.Session.Folders.Item(FoldersArray(0))
If Not oFolder Is Nothing Then
For i = 1 To UBound(FoldersArray, 1)
Dim SubFolders As Outlook.Folders
Set SubFolders = oFolder.Folders
Set oFolder = SubFolders.Item(FoldersArray(i))
If oFolder Is Nothing Then
Set GetFolderPath = Nothing
End If
Next
End If
'Return the oFolder
Set GetFolderPath = oFolder
Exit Function
GetFolderPath_Error:
Set GetFolderPath = Nothing
Exit Function
End Function