Hi, I made a post about this referencing the Outlook inbox on my blog at vbahowto.com (http://vbahowto.com/how-to-extract-data-from-outlook-to-access-with-vba)
This is the Excel version of how to export outlook emails to excel with date and time.
It’s pretty much the same as looping the inbox, but instead of “throwing” the results into a table, we will put the results in the cells on the worksheet.
Just a refresher on the story. Someone was let go from the department, and so my task was to extract all the messages from the user’s inbox, and get all the skype (lync) conversations.
This post shows the conversation part, and the Access post shows how to extract from the inbox (referenced above).
Here is the complete code:
'3/11/19 - http://loeblcomservices.com
Dim olApp As Outlook.Application
Dim ConversationFolder As Outlook.Folder
Dim intCount As Integer
Dim strSQL As String
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
On Error GoTo 0
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
Set ConversationFolder = olApp.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Parent.Folders("Conversation History")
For intCount = 1 To ConversationFolder.Items.Count
Sheets("Conversations").Range("A" & intCount) = ConversationFolder.Items(intCount).ReceivedTime
Sheets("Conversations").Range("B" & intCount) = ConversationFolder.Items(intCount).Subject
Sheets("Conversations").Range("C" & intCount) = ConversationFolder.Items(intCount).Body
Set olApp = Nothing
This will return the data into a worksheet with this format:
In our sample, we are taking any conversations from the “Conversation History” folder.
Here is the folder reference in the code:
Make sure you add a reference to the Outlook Object library:
Just go into the code and press F5 to run the procedure, and your conversations from the “Conversation History” folder will be shown on your worksheet, and the date you’ll be getting is the time the message was received.
Let me know if you have any questions.