How to Export Sent Items Data from Outlook to Excel

Extracting Outlook Email Data to Excel: The First Step in Organizing Work Performance

Why Organize Outlook Email Data in Excel?

As the year-end approaches, it's time to organize work performance. Emails, in particular, are a key record of work and important data that demonstrates the history of communication. However, manually organizing email data in Outlook is time-consuming. That's why I tried using Outlook and Excel VBA (Visual Basic for Applications) to automatically extract and organize sent email data. This process helped me efficiently organize my performance, and I want to share it with you all.

Preparing to Write and Execute VBA Code

To use VBA, you first need to enable the Developer tab in Outlook and write the code. Below are the step-by-step instructions:

1. Open the VBA Editor

To call VBA in Outlook, use the following shortcut key:

• Alt + F11: This is the shortcut to open the VBA editor.

Pressing the shortcut will open the Microsoft Visual Basic for Applications window. Here, you can write and execute code.

2. Add a Module

In the VBA editor, add a module by following these steps:

1. In the Project Explorer window on the left, select `ThisOutlookSession`.
2. From the top menu, click Insert → Module.
3. When the new module window opens, paste the code here.

3. Run the Macro

To run the macro after writing the code, follow these steps:

1. Close the VBA editor and go back to Outlook.
2. Press Alt + F8 to open the Macro dialog box.
3. Select the macro to run (Sub procedure name) from the list and click the Run button.

Example VBA Code

Below is the main part of the VBA code I wrote. This code extracts the subject, recipients, sent date, and the first 400 bytes of the body from the sent mail folder into Excel.

= startDate And olMail.SentOn <= endDate Then
                With ws
                    .Cells(i + 1, 1).Value = olMail.SentOn
                    .Cells(i + 1, 2).Value = olMail.To
                    .Cells(i + 1, 3).Value = olMail.Subject
                    .Cells(i + 1, 4).Value = Left(olMail.Body, 400)
                End With
            End If
        End If
    Next i

    MsgBox "Email data extracted successfully!"
End Sub

Helpful Tips When Writing Code

Here are some tips I learned while writing the code:

• Process Items in Reverse Order: To avoid missing the latest emails, process items in reverse order.
• Conditional Filtering: To extract only emails that match a specific period or keyword, use If statements.
• Convert Date Format: To convert date data to text in Excel, use the TEXT function. For example, entering `=TEXT(A1, "yyyy-mm-dd hh:mm:ss")` will do the trick.

Results and Use Cases

As a result of running this code, I was able to neatly organize sent email data into Excel. This was a great help when writing year-end performance reports or analyzing communication histories. In particular, automating repetitive tasks saved time and reduced errors.

Concluding Remarks

By using Outlook and Excel VBA, you can efficiently manage email data. Initially, writing code might be difficult, but with gradual improvements and customization to fit your work, it can become a great tool. I hope you use this method to neatly organize your year-end work performance!