Consulting

Results 1 to 2 of 2

Thread: Error: User-Defined type not defined

  1. #1
    VBAX Regular
    Joined
    Sep 2018
    Posts
    20
    Location

    Error: User-Defined type not defined

    Hi there I run this and code and i had this error above



    Option Explicit


    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 workbookFile As String
    Dim msg As Outlook.MailItem
    Dim nms As Outlook.NameSpace
    Dim fld As Outlook.MAPIFolder
    Dim itm As Object

    'Folder path and file name of an existing Excel workbook

    workbookFile = "C:\Examples\OutlookItems.xls"


    '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")
    Set wkb = appExcel.Workbooks.Open(workbookFile)
    Set wks = wkb.Sheets(1)
    wks.Activate
    appExcel.Application.Visible = True
    Set rng = wks.Range("A1")

    'Copy field items in mail folder.


    For Each itm In fld.Items
    If itm.Class = Outlook.OlObjectClass.olMail Then
    Set msg = itm
    If InStr(msg.Subject, "Changes Required") > 0 And DateDiff("d", msg.SentOn, Now) <= 7 Then
    rng.Offset(0, 0).Value = msg.To
    rng.Offset(0, 1).Value = msg.SenderEmailAddress
    rng.Offset(0, 2).Value = msg.Subject
    rng.Offset(0, 3).Value = msg.SentOn
    rng.Offset(0, 4).Value = msg.Body
    Set rng = rng.Offset(1, 0)
    End If
    End If
    Next

    Set appExcel = Nothing

    Exit Sub

    ErrHandler:
    If Err.Number = 1004 Then
    MsgBox workbookFile & " doesn't exist", vbOKOnly, _
    "Error"
    Else
    MsgBox "Error number: " & Err.Number & vbNewLine & _
    "Description: " & Err.Description, vbOKOnly, "Error"
    End If

    End Sub

  2. #2
    It appears that you are using early binding to Excel and have not set a reference to the Excel object library in tools > references

    You could use late binding to Excel and change the appropriate DIM statements as follows
        Dim appExcel As Object
        Dim wkb As Object
        Dim wks As Object
        Dim rng As Object
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

Posting Permissions

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