User defined type not defined on Outlook.Namespace from Excel VBA?

Question

I am trying to search for an Outlook email with a specific subject from Excel:

Sub Work_with_Outlook()

    Set olApp = CreateObject("Outlook.Application")

    Dim olNs As Outlook.Namespace
    Dim Fldr As Outlook.MAPIFolder
    Dim olMail As Variant
    Dim sir() As String

    Set olApp = New Outlook.Application
    Set olNs = olApp.GetNamespace("MAPI")
    Set Fldr = olNs.GetDefaultFolder(olFolderInbox)
    Set myTasks = Fldr.Items

    Set olMail = myTasks.Find("[Subject] = ""*desired subject*""")
    If Not (olMail Is Nothing) Then
        sir = Split(olMail.Body, vbCrLf)
        For i = 1 To UBound(sir)
            ActiveWorkbook.Sheets("Sheet1").Cells(i, 1).Value = sir(i)
        Next i
        olMail.Delete
    End If

End Sub

I get a error in Excel saying

user defined type not defined

on this line:

Dim olNs As Outlook.Namespace

score 2 · Accepted Answer · answered Jul 14 '15 at 09:49

2

You need to add reference to Outlook in your VBA Project.

In VBA editor menu bar click Tools -> References and check Microsoft Outlook 14.0 Object Library (version number can be different than 14.0, depending on your version of MS Office).

answered Jul 14 '15 at 09:49

mielk

3,890
12
19

thanks this fixed the problem, also do you know how I can search for my email subject using wildcard? as this is currently not working, only works with exact text at the minute? – Mark harris Jul 14 '15 at 10:15
here is post about it: http://stackoverflow.com/questions/21549938/vba-search-in-outlook – mielk Jul 14 '15 at 10:37

User defined type not defined on Outlook.Namespace from Excel VBA?

1 Answers1