Import Data from Outlook To Excel

  • Hello XL'rs .
    I'm fairly strong in VBA in excel but have no idea how to write code that will get data from notes in Outlook.


    I have a folder in outlook with about 100 notes . What I would like to do is import this data one at a time and parse out particular information.


    Does anyone have any code for pulling in the information from a folder in outlook ?:puzzled:

  • This is just a guess as to what folder you are wanting to access. The following code, as an example, will import the Contacts list into an active Excel worksheet. As you probably know, you'll need to first go into the VBE, and from Tools > References select (as named in XP anyway) Microsoft Outlook 10.0 Object Library.


    Sub ImportOutlookFolderData()
    Application.ScreenUpdating = False


    Dim myOutlook As Outlook.MAPIFolder
    Set myOutlook = GetObject("", "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderContacts)
    Dim i As Integer, j As Integer, x As Integer


    i = 0
    j = 0
    x = myOutlook.Items.Count


    Range("A1").Value = "Name"
    Range("B1").Value = "Company"
    Range("C1").Value = "Address"
    Range("D1").Value = "City"


    While i < x
    i = i + 1
    With myOutlook.Items(i)
    j = j + 1
    Cells(j + 1, 1).Formula = .FullName
    Cells(j + 1, 2).Formula = .CompanyName
    Cells(j + 1, 3).Formula = .BusinessAddressStreet
    Cells(j + 1, 4).Formula = .BusinessAddressCity
    End With
    Wend


    Range("A1:D1").Font.Bold = True
    Range("A:D").Columns.AutoFit


    Set myOutlook = Nothing

    Application.Goto Range("A1"), True
    Application.ScreenUpdating = True
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!