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

Question

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

Make an early binding to "Office14" Outlook (the lowest denominator). If user has higher version, VBA will fix the reference automatically. — JohnyL, Dec 14 '17 at 07:01
Or if performance not horrendously impacted consider late binding? Removes the need for a lot of the code. — QHarr, Dec 14 '17 at 07:23
@ashleedawg I have made a stab at correcting this in the hope the OP will come back and further improve. — QHarr, Dec 14 '17 at 07:24

Kazimierz Jawor · Answer 1 · 2017-12-14T08:23:29.107

During pre-compile process Outlook.Application is not valid as it is not set in Tools\References.... If you want to keep your code working you need to run ini_set first and before sendMail is even compiled. Try to add new subroutine to call both in a sequence:

Sub MainSub()
    call ini_set
    call sendMail
End Sub

To make it clear- remove Call ini_set from your sendMail and each time you have to call both do it from separate subroutine.

Important! With this solution you could keep outlook appilcation constants (like olMailItem) which is not possible when you switch to Late binding solution.

Tim Williams · Accepted Answer · 2017-12-14T17:19:59.583

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...

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

2 Answers2