10

I get error

user-defined type not defined

in the Function GetOutlookApp() As Outlook.Application.

Sub CreateAppointments()
     
    Dim cell As Excel.Range
    Dim rng As Excel.Range
    Dim wholeColumn As Excel.Range
    Dim startingCell As Excel.Range
    Dim oApp As Outlook.Application
    Dim tsk As Outlook.TaskItem
    Dim wkbk As Excel.Workbook
    Dim wksht As Excel.Worksheet
    Dim lastRow As Long
    Dim arrData As Variant
    Dim i As Long
     
' start Outlook app

    Set oApp = GetOutlookApp
    If oApp Is Nothing Then
      MsgBox "Could not start Outlook.", vbInformation
      Exit Sub
    End If
     
' get worksheet range into an array in one go

    Set wkbk = ActiveWorkbook
    Set wksht = wkbk.ActiveSheet
    Set wholeColumn = wksht.Range("B:B")
    lastRow = wholeColumn.End(xlDown).Row - 2
    Set startingCell = wksht.Range("B2")
    Set rng = wksht.Range(startingCell, startingCell.Offset(lastRow, 1))
    arrData = Application.Transpose(rng.Value)
     
' loop through array and create tasks for each record

    For i = LBound(arrData, 2) To UBound(arrData, 2)
      Set tsk = oApp.CreateItem(olTaskItem)
      With tsk
        .DueDate = arrData(2, i)
        .Subject = arrData(1, i)
        .Save
      End With
    Next I
     
End Sub

    
Function GetOutlookApp() As Outlook.Application
    On Error Resume Next
    Set GetOutlookApp = CreateObject("Outlook.Application")
End Function
Community
  • 1
  • 1
Kim G
  • 133
  • 1
  • 1
  • 10
  • 7
    Did you add a reference to the office COM server? *Tools > References > tick Microsoft Outlook XXX Object Library* – Alex K. Jun 02 '15 at 14:22
  • Bingo! That was it. Thanks!!! – Kim G Jun 02 '15 at 14:29
  • 1
    Possible duplicate of [User Defined Type Not Defined - Excel Macros](http://stackoverflow.com/questions/24261557/user-defined-type-not-defined-excel-macros) – Zev Spitz Dec 29 '16 at 08:05
  • 1
    Possible duplicate of [User-defined type not defined for AppointmentItem in Excel](https://stackoverflow.com/questions/3645470/user-defined-type-not-defined-for-appointmentitem-in-excel) – niton Oct 31 '17 at 10:14

2 Answers2

19

The How to automate Outlook from another program article describes all the required steps for automating Outlook. It states:

To use early binding, you first need to reference the available Outlook object library. To do this from Visual Basic (VB) or Visual Basic for Applications, follow these steps:

  1. In the Visual Basic Editor, on the Tools menu, click References.
  2. Click to select the Microsoft Outlook 15.0 Object Library check box, and then click OK.
Eugene Astafiev
  • 47,483
  • 3
  • 24
  • 45
2

I was have the same problem when I use Outlook in my scripts VBA Excel and I select:

Tools > References > Check the checkbox in front of "Microsoft Outlook 15.0 Object Library.

D. Chirita
  • 29
  • 1