CODEWITHSUNDEEP
vbaoutlook
Kim G
Kim G

Reputation: 133

User-defined type not defined error creating Outlook application object using Excel VBA

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

Upvotes: 10

Views: 103871

Answers (2)

Eugene Astafiev
Eugene Astafiev

Reputation: 49455

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.

Upvotes: 19

D. Chirita
D. Chirita

Reputation: 29

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.

Upvotes: 2

Related Questions