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:
Sub GetConversations() '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") End If 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 Next MsgBox "Complete" ExitRoutine: Set olApp = Nothing End Sub
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.
****************************************************
|