Excel With VBA

How To Export Outlook Emails To Excel With Date And Time

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.

****************************************************

admin