VBA pull Outlook emails into Excel


Set inboxName = ns.Folders.Item(“Boxes Name“).Folders(“Inbox”)

Change “Boxes Name” to the Outlook email box where the Inbox emails are located.

A For Each Loop will go through all the emails in the box 1 by 1.  Where you can get the body of the email, the subject line, from, cc line, etc.

Microsoft Outlook 16.0 Object Library must be enabled in order to pull emails from Outlook into Excel.

Sub Pull_Emails_Outlook()
    Dim ns As Outlook.Namespace
    Dim inboxName As Outlook.Folder
    Dim varLine As String
    Dim i, ii, j, msgCount As Long
    Dim strBody As String
    Dim i1 As Items
ii = 1
j = 1

Set ns = GetNamespace("MAPI")
Set inboxName = ns.Folders.Item("Boxes Name").Folders("Inbox")
msgCount = inboxName.Items.Count
MsgBox "There are " & x & " number of emails in this box."

For Each msg In inboxName.Items
    arr = Split(msg.Body, vbCrLf)
    Cells(ii, j).Value = msg.Subject
        For i = LBound(arr) To UBound(arr)
            varLine = arr(i)
            varLine = CStr(varLine)
            Cells(ii + 1, j).Value = varLine
            ii = ii + 1

        Next i
    j = j + 1
    ii = 1
End Sub

Interested in learning new skills?