Rafael Osipov
Rafael Osipov

Reputation: 740

Excel VBA Error "user-defined type not defined" with Outlook

I am using Office 2016 in my computer and I have a VBA code that sends Email to so mail lists in the file. Every time I want to send the Emails automatically with my CommandButton, I get the error massage: "user-defined type not defined". I made some research in the web and I found out that there is a solution: VB Editor ----> Tools ----> Referenced ----> Microsoft Outlook 16.0 Object Library

But the next time I open the file the same error runs again and again and again. Can someone find me solution that will be permanent? I Don't know what to do more then I already did.

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

Here is the problem based on the VB Editor:

Dim applOL As Outlook.Application

Upvotes: 1

Views: 3144

Answers (1)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

As I suggested in the comment, you can edit your code like below (see three commented lines) and should be able to run without references. I am assuming that the code is correct otherwise and it is providing intended results

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 Object       '\\Outlook.Application
            Dim miOL As Object         '\\Outlook.MailItem
            Dim recptOL As Object      '\\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 Sub

Late binding can help in some other cases as well especially if there are several users and they are having different setups with respect to software versions!

Upvotes: 1

Related Questions