Reputation: 740
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
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