How to fix Compile Error: User-defined type not defined when using Excel VBA from Outlook?

excel vba outlook

17,202

Solution 1

References

It's an error that appears when a reference is missing.
Try to add in Tools-> References:

Microsoft Excel [Your Version] Object Library
Microsoft Outlook [Your Version] Object Library

Code

Try to change how the Excel App is initialized, using this:

Dim objExcelApp As New Excel.Application

Instead of:

Dim objExcelApp As Excel.Application

So your code will look like this:

Private Sub objMails_ItemAdd(ByVal Item As Object)
    Dim objMail As Outlook.MailItem
    Dim strExcelFile As String
    Dim objExcelApp As New Excel.Application
    Dim objExcelWorkBook As Excel.Workbook
    Dim objExcelWorkSheet As Excel.Worksheet
    Dim nNextEmptyRow As Integer
    Dim strColumnB As String
    Dim strColumnC As String
    Dim strColumnD As String
    Dim strColumnE As String

    If Item.Class = olMail Then
       Set objMail = Item
    End If

    'Specify the Excel file which you want to auto export the email list
    'You can change it as per your case
    strExcelFile = "H:\SF_Mail\Emails.xlsx"

    'Get Access to the Excel file
    Set objExcelWorkBook = objExcelApp.Workbooks.Open(strExcelFile)
    Set objExcelWorkSheet = objExcelWorkBook.Sheets("Sheet1")

    'Get the next empty row in the Excel worksheet
    nNextEmptyRow = objExcelWorkSheet.Range("B" & objExcelWorkSheet.Rows.Count).End(xlUp).Row + 1

    'Specify the corresponding values in the different columns
    strColumnB = objMail.SenderName
    strColumnC = objMail.SenderEmailAddress
    strColumnD = objMail.Subject
    strColumnE = objMail.ReceivedTime

    'Add the vaules into the columns
    objExcelWorkSheet.Range("A" & nNextEmptyRow) = nNextEmptyRow - 1
    objExcelWorkSheet.Range("B" & nNextEmptyRow) = strColumnB
    objExcelWorkSheet.Range("C" & nNextEmptyRow) = strColumnC
    objExcelWorkSheet.Range("D" & nNextEmptyRow) = strColumnD
    objExcelWorkSheet.Range("E" & nNextEmptyRow) = strColumnE

    'Fit the columns from A to E
    objExcelWorkSheet.Columns("A:E").AutoFit

    'Save the changes and close the Excel file
    objExcelWorkBook.Close SaveChanges:=True
    objExcelApp.Quit 'Quit Excel application
End Sub

Notes

Usually it's a bad idea to use the instruction On Error Resume Next, because it suppresses every error you get on runtime execution. However, there're some exceptions to the rule and you can check @FunThomas answer for clarification.

Solution 2

This is not really an answer but too long for a comment to the answer of @Louis and the following discussion.

On Error Resume Next usually is evil, but sometimes it is the best way to deal with a statement that might fail. In this case, the command Set objExcelApp = GetObject(, "Excel.Application") will assign a running instance of Excel to the variable objExcelApp, but will fail (and throw an error) if Excel is currently not active. The following If Error <> 0 Then checks if an error occurred and if yes, it will open a new Excel instance and assign it to objExcelApp.

At that point, Excel should be available to the Macro, either an existing or a new Instance. An exception could only be if Excel is not available at all (not installed) or cannot be started (out of memory). However, the On Error Resume Next is still active and will continue to ignore all runtime errors, and that is bad. So, after the the assignment of the variable, revert to the standard error handling and see what fails:

'Get Access to the Excel file
On Error Resume Next
Set objExcelApp = GetObject(, "Excel.Application")
If Error <> 0 Then
   Set objExcelApp = CreateObject("Excel.Application")
End If
On Error Goto 0

17,202

Losdragon

Updated on June 04, 2022

Comments

Losdragon almost 2 years

I'm setting up an automatic solution to export incoming mails from Outlook into an Excel file.
I found several solutions online but I get a compile error.

I'm using Outlook 2016 and Windows 8.1.

I thought it's a reference problem, but I found the FM20.DLL and it's still not working.

The error I get:

Compile error: User-defined type not defined

at line Dim objExcelApp As Excel.Application

Public WithEvents objMails As Outlook.Items

Private Sub Application_Startup()
    Set objMails = 
Outlook.Application.Session.GetDefaultFolder(olFolderInbox).Items
End Sub

Private Sub objMails_ItemAdd(ByVal Item As Object)
    Dim objMail As Outlook.MailItem
    Dim strExcelFile As String
    Dim objExcelApp As Excel.Application
    Dim objExcelWorkBook As Excel.Workbook
    Dim objExcelWorkSheet As Excel.Worksheet
    Dim nNextEmptyRow As Integer
    Dim strColumnB As String
    Dim strColumnC As String
    Dim strColumnD As String
    Dim strColumnE As String

    If Item.Class = olMail Then
       Set objMail = Item
    End If

    'Specify the Excel file which you want to auto export the email list
    'You can change it as per your case
    strExcelFile = "H:\SF_Mail\Emails.xlsx"

    'Get Access to the Excel file
    On Error Resume Next
    Set objExcelApp = GetObject(, "Excel.Application")
    If Error <> 0 Then
       Set objExcelApp = CreateObject("Excel.Application")
    End If
    Set objExcelWorkBook = objExcelApp.Workbooks.Open(strExcelFile)
    Set objExcelWorkSheet = objExcelWorkBook.Sheets("Sheet1")

    'Get the next empty row in the Excel worksheet
    nNextEmptyRow = objExcelWorkSheet.Range("B" & objExcelWorkSheet.Rows.Count).End(xlUp).Row + 1

    'Specify the corresponding values in the different columns
    strColumnB = objMail.SenderName
    strColumnC = objMail.SenderEmailAddress
    strColumnD = objMail.Subject
    strColumnE = objMail.ReceivedTime

    'Add the vaules into the columns
    objExcelWorkSheet.Range("A" & nNextEmptyRow) = nNextEmptyRow - 1
    objExcelWorkSheet.Range("B" & nNextEmptyRow) = strColumnB
    objExcelWorkSheet.Range("C" & nNextEmptyRow) = strColumnC
    objExcelWorkSheet.Range("D" & nNextEmptyRow) = strColumnD
    objExcelWorkSheet.Range("E" & nNextEmptyRow) = strColumnE

    'Fit the columns from A to E
    objExcelWorkSheet.Columns("A:E").AutoFit

    'Save the changes and close the Excel file
    objExcelWorkBook.Close SaveChanges:=True
End Sub

Alex K. about 5 years

What error are you encountering & where? (On Error Resume Next is not helpful when your trying to locate a problem)
Losdragon about 5 years

I get: Compile error: User-defined type not defined. And the Dim objExcelApp As Excel.Application part is highlited.
Nacorid about 5 years

A compilation error always highlights the line which causes the compilation error
Losdragon about 5 years

I added a screenshot.
Nacorid about 5 years

You need to reference the Excel Object Library to use it.
Luuklag about 5 years

Write the text of an error message, don't attach it as a screenshot. If you write it out your question can be found by others with the same problem.

Losdragon about 5 years

Ok! The error message is gone. Now it's simply not working... Until this point, whenever I got a new mail, the error message appeared, and the excel file opened. Now just nothing happens.
Louis about 5 years

I notice that you also need the Outlook library. You don't see any errors because you used On Error Resume Next. Remove that line and tell me what error it gives you.
Losdragon about 5 years

Run-time error '429': ActiveX component can't create object. Set objExcelApp = GetObject(, "Excel.Application") is highlighted with yellow.
Louis about 5 years

Try to change how you initialize Excel App and tell me if it works.
Louis about 5 years

Yes, I assumed that the intent of that On Error Resume Next was the one you're saying. With the code I wrote, the most common case where it can fail is when Excel isn't installed. However, it's very difficult to execute an Excel Macro without Excel, so this isn't really a real case scenario to check. Anyway, I appreciated the clarification. I'm pointing out your response in my answer. +1
FunThomas about 5 years

@Louis: Your attempt will work, however it will open a new instance of Excel every time, no matter if Excel is already running or not. All in all, this is just a matter of taste, but I usually find this a little bit confusing. And, if you don't close it and rerun the code multiple times, you end up with lots of Excel instances.
Louis about 5 years

You're right, I was in a hurry and I didn't watch how he was handling the closure. Now it should work as intended.