Results 1 to 3 of 3

Thread: Linking Outlook to Excel
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2011
    Location
    Utah
    Posts
    97
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Linking Outlook to Excel

    I found this code to link Outlook to Excel but I keep getting a compile error. It says "User-defined type not defined."

    Please help. This is the website i got the instructions from.

    [URL="http://www.techrepublic.com/blog/msoffice/quickly-export-outlook-e-mail-items-to-excel/744"]



    Sub ExportToExcel()
    On Error GoTo ErrHandler

    Dim appExcel As Excel.Application

    Dim wkb As Excel.Workbook

    Dim wks As Excel.Worksheet

    Dim rng As Excel.Range

    Dim strSheet As String

    Dim strPath As String

    Dim intRowCounter As Integer

    Dim intColumnCounter As Integer

    Dim msg As Outlook.MailItem

    Dim nms As Outlook.NameSpace

    Dim fld As Outlook.MAPIFolder

    Dim itm As Object
    strSheet = "OutlookItems.xls"
    strPath = "C:Examples\"

    strSheet = strPath & strSheet

    Debug.Print strSheet
    'Select export folder
    Set nms = Application.GetNamespace("MAPI")

    Set fld = nms.PickFolder
    'Handle potential errors with Select Folder dialog box.
    If fld Is Nothing Then

    MsgBox "There are no mail messages to export", vbOKOnly, _
    "Error"

    Exit Sub

    ElseIf fld.DefaultItemType <> olMailItem Then

    MsgBox "There are no mail messages to export", vbOKOnly, _
    "Error"

    Exit Sub

    ElseIf fld.Items.Count = 0 Then

    MsgBox "There are no mail messages to export", vbOKOnly, _
    "Error"

    Exit Sub

    End If
    'Open and activate Excel workbook.
    Set appExcel = CreateObject("Excel.Application")

    appExcel.Workbooks.Open (strSheet)

    Set wkb = appExcel.ActiveWorkbook

    Set wks = wkb.Sheets(1)

    wks.Activate

    appExcel.Application.Visible = True
    'Copy field items in mail folder.
    For Each itm In fld.Items

    intColumnCounter = 1

    Set msg = itm

    intRowCounter = intRowCounter + 1

    Set rng = wks.Cells(intRowCounter, intColumnCounter)

    rng.Value = msg.To

    intColumnCounter = intColumnCounter + 1

    Set rng = wks.Cells(intRowCounter, intColumnCounter)

    rng.Value = msg.SenderEmailAddress

    intColumnCounter = intColumnCounter + 1

    Set rng = wks.Cells(intRowCounter, intColumnCounter)

    rng.Value = msg.Subject

    intColumnCounter = intColumnCounter + 1

    Set rng = wks.Cells(intRowCounter, intColumnCounter)

    rng.Value = msg.SentOn

    intColumnCounter = intColumnCounter + 1

    Set rng = wks.Cells(intRowCounter, intColumnCounter)

    rng.Value = msg.ReceivedTime

    Next itm
    Set appExcel = Nothing
    Set wkb = Nothing

    Set wks = Nothing

    Set rng = Nothing

    Set msg = Nothing

    Set nms = Nothing

    Set fld = Nothing

    Set itm = Nothing
    Exit Sub
    ErrHandler: If Err.Number = 1004 Then

    MsgBox strSheet & " doesn't exist", vbOKOnly, _
    "Error"

    Else

    MsgBox Err.Number & "; Description: ", vbOKOnly, _
    "Error"

    End If

    Set appExcel = Nothing

    Set wkb = Nothing

    Set wks = Nothing

    Set rng = Nothing

    Set msg = Nothing

    Set nms = Nothing

    Set fld = Nothing

    Set itm = Nothing
    End Sub

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Linking Outlook to Excel

    You need to add the reference to Microsoft Outlook using Tools | References...
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Board Regular
    Join Date
    Dec 2011
    Location
    Utah
    Posts
    97
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Linking Outlook to Excel

    Thanks, that worked.

    I now would like it to automatically select a folder instead of me manually selecting it. The folder name is "Budget"

    Thanks in advance

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •