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
– JohnyL, Commented Dec 14, 2017 at 7:01
Or if performance not horrendously impacted consider late binding? Removes the need for a lot of the code. — QHarr
– QHarr, Commented Dec 14, 2017 at 7:23
@ashleedawg I have made a stab at correcting this in the hope the OP will come back and further improve. — QHarr
– QHarr, Commented Dec 14, 2017 at 7:24

Tim Williams · Accepted Answer · 2017-12-14 17:19:59Z

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

Kazimierz Jawor · Accepted Answer · 2017-12-14 08:23:29Z

3

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.

edited Dec 14, 2017 at 8:23

answered Dec 14, 2017 at 7:23

Kazimierz Jawor

19.1k7 gold badges38 silver badges58 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related