VBA 'User Defined Type Not Defined' Compile Error with Outlook

Rafael Osipov

I have a big Excel file that sends, with a command button, e-mails to Managers at work, and then they can press the button and it sends the files to Managers below them.

Since every manager has her/his own version of MS Office, I have a sub that checks which version (s)he has on her/his computer and marks V in References.

When I save the file, I save it in a status that Outlook Object Library is not marked with V, and I have code that someone else built. The code runs through 3 subs. The first sub has a msgbox that when you answer on it, Yes , it sends you to next sub.

Public Sub before_send_mail()

    answer = MsgBox("Send Email?", vbYesNo + vbQuestion, "Empty Sheet")

    If answer = vbYes Then
        Call excel_ver
        Call sendMail
        Call remove_ref
    Else
     'do nothing
    End If

End Sub

Then, I have the "references picker by office version" that checks which version there is installed on the computer and marks V automatically in Tools---->References in Outlook object. That part seems to work well too.

Sub excel_ver()

    On Error Resume Next
    ver = Application.Version

    If ver = 16 Then
        tmp_name = "C:\Program Files\Microsoft Office\Office16\MSOUTL.OLB"
        Application.VBE.ActiveVBProject.References.AddFromFile tmp_name
        Exit Sub
    End If

    If ver = 15 Then
        tmp_name = "C:\Program Files\Microsoft Office\Office15\MSOUTL.OLB"
        Application.VBE.ActiveVBProject.References.AddFromFile tmp_name
        Exit Sub
    End If

    If ver = 14 Then
        tmp_name = "C:\Program Files\Microsoft Office\Office14\MSOUTL.OLB"
        Application.VBE.ActiveVBProject.References.AddFromFile tmp_name
        Exit Sub
    End If

End Sub

And then we get to the problem. When I get to sub sendMail it gives me an error on the line Dim applOL As Outlook.Application

Public Sub sendMail()

    Call ini_set

    If mail_msg.Cells(200, 200) = 1 Then

        lr = main_dist.Cells(main_dist.Rows.Count, "A").End(xlUp).Row

        On Error Resume Next

        For i = 2 To lr

            Application.DisplayAlerts = False

            Dim applOL As Outlook.Application 'Here is the error ---- that line
            Dim miOL As Outlook.MailItem
            Dim recptOL As Outlook.Recipient

            mail_msg.Visible = True

            mailSub = mail_msg.Range("B1")
            mailBody = mail_msg.Range("B2")

            mail_msg.Visible = False

            Set applOL = New Outlook.Application
            Set miOL = applOL.CreateItem(olMailItem)
            Set recptOL = miOL.Recipients.Add(main_dist.Cells(i, 5))
            recptOL.Type = olTo

            tempPath = ActiveWorkbook.Path & "\" & main_dist.Cells(i, 4) & ".xlsm"

            With miOL
                .Subject = mailSub
                .Body = mailBody
                .Attachments.Add tempPath
                .send     
            End With

            Set applOL = Nothing
            Set miOL = Nothing
            Set recptOL = Nothing

            Application.DisplayAlerts = True

        Next i
   End If
End Sub
Tim Williams

Should run with no reference required:

Public Sub sendMail()

    Dim applOL As Object, miOL As Object, recptOL As Object
    Dim i As Long

    ini_set

    If mail_msg.Cells(200, 200) = 1 Then

        Set applOL = CreateObject("Outlook.Application")

        For i = 2 To main_dist.Cells(main_dist.Rows.Count, "A").End(xlUp).Row

            Set miOL = applOL.CreateItem(0)  'olMailItem=0
            Set recptOL = miOL.Recipients.Add(main_dist.Cells(i, 5))
            recptOL.Type = 1  ' olTo=1

            With miOL
                .Subject = mail_msg.Range("B1")
                .Body = mail_msg.Range("B2")
                .Attachments.Add ActiveWorkbook.Path & "\" & _
                                 main_dist.Cells(i, 4) & ".xlsm"
                .send
            End With
        Next i
        Set applOL = Nothing
   End If
End Sub

EDIT: in the code above I removed some of your "single-use" variables, but that's just my preference...

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Late binding compile error: User-defined type not defined referencing Outlook mailitem in Excel VBA

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

Word VBA Macro error; 'Compile Error: User-defined type not defined' Attempting to Compile Code

Error on Loop Through Outlook MailItems using Late Binding in Access VBA: User-defined Type Not Defined

What is "Compile error User-defined type not defined"?

Visual Basic 6 - Compile Error: User-defined type not defined

VBA sub call gives "compile error: Type mismatch: array or user-defined type expected"

Problom With VBA code throw an “user defined type not defined” error

VBA "Compile Error: Label not defined"

VBA User-Defined type not defined

VBA "user-defined type not defined"

Excel VBA user defined type not defined-

Ambiguous "User-defined type not defined" Error

ignore "User Defined Type Not Defined" error

How to solve the error User defined type not defined?

Declaring variable as Internet Explorer generates Compile error: user-defined type not defined

Compile error: User-defined type not defined when updating an older macro for Access library

Trying to generate email from data in Excel spreadsheet, getting compile error "User defined type not defined"

Compile Error "Variable not defined" in VBA Excel

Compiler Error 'User defined type not defined' at line of DLL declaration from VBA

Create a property for a user-defined type in vba

Late binding to avoid "User defined type not defined" error

Error creating the user defined data type

SQL statement with user defined type produces error

Document type not defined in VBA

Excel VBA "Type mismatch:array or user-defined type expected"

Applicative for a user defined type

User defined type as reference

User defined type Ocaml