Library Not Registered when Opening Outlook from Excel 2010

[Gideon du Toit]

Good Day Everyone,

I have been developing an Excel app that at some point has to open Outlook. Until a few days ago this was working fine. I am using Office 2010 for the development since it works nicely for backward compatibility.
I also had Office 365 on this machine but decided to remove it because of space concerns and I prefer to use 2010. However, since removing it, I keep getting an Automation Error - Library not Registered.

I have the Office Outlook 14 library included in the references and my code used to run perfectly, yet I am now stuck with a seemingly unsolvable issue. On the internet, I have found several suggestions such as reinstalling Office 2010 and re-registering the dll's and I have tried them all to no avail. I am including some screenshots and my code hereunder in the hope that someone might help me solve this issue.

Thank you in advance!

Sub Send_Email_1() 'EMail Tablets

Dim EmailApp As Outlook.Application
Dim NewEmailItem As Outlook.MailItem
Dim strSource As String
Dim strRecipient As String
Dim strCC As String

Set EmailApp = New Outlook.Application
Set NewEmailItem = EmailApp.CreateItem(olMailItem)

strAlertBody = "We are require " & CStr(Sheet6.Range("F46").Value) & " Tablets and SIM cards for the " & Sheet1.Range("B1").Value & _
"Project, which runs between " & Sheet1.Range("B4").Value & " and " & Sheet1.Range("B5").Value & ". The Project Manager " & _
"will be " & Sheet1.Range("B2").Value & " and can be contacted at " & Sheet1.Range("D2").Value & " or " & _
Sheet1.Range("C2").Value & "."

strSource = "Good Day," & vbNewLine & vbNewLine & "This email was automatically generated from the Excel Project Management App." & _
vbNewLine & vbNewLine & strAlertBody & vbNewLine & vbNewLine & "Regards" & vbNewLine & "Project Management"

strRecipient = CStr(Sheet6.Range("H55").Value)
strCC = CStr(Sheet6.Range("H56").Value)

On Error Resume Next
With NewEmailItem
    .To = strRecipient
    .CC = strCC
    .Subject = "Tablets for Project"
    .Body = strSource
Call MyMacroThatUseOutlook
    '.Attachments.Add ("Path)
    .Send
    .Close (olSave)
    Set EmailApp = Nothing
    Set NewEmailItem = Nothing

End With

strLogEvent = " Email for Tablets and SIM Cards was sent"
Call Log_Event

End Sub

 

[Anthony47]

I'd suggest you switch from "early binding" to "late binding".
This involves modifying two Dims and adding two Sets:

Dim EmailApp As Object          ' Outlook.Application
Dim NewEmailItem As Object      ' Outlook.MailItem

Set EmailApp = CreateObject("Outlook.Application")
Set NewEmailItem = OutApp.CreateItem(0)
'your code
'your code

This doesn't require the Outlook library be referenced

 

[Gideon du Toit]

I'd suggest you switch from "early binding" to "late binding".
This involves modifying two Dims and adding two Sets:

Dim EmailApp As Object          ' Outlook.Application
Dim NewEmailItem As Object      ' Outlook.MailItem

Set EmailApp = CreateObject("Outlook.Application")
Set NewEmailItem = OutApp.CreateItem(0)
'your code
'your code

This doesn't require the Outlook library be referenced

Thank you for your advice Anthony. I have tried your suggestion but it produces an "Object Required" error on the line as shown below in the screengrab

 

[RoryA]

Your variable is called EmailApp not OutApp

 

[Anthony47]

Your variable is called EmailApp not OutApp

My error in copying the code

Set NewEmailItem = EmailApp.CreateItem(0)

 

[Gideon du Toit]

My error in copying the code

Set NewEmailItem = EmailApp.CreateItem(0)

Thank you again for your reply. I did change the variable, and it now runs and opens Outlook but although it reads my code for writing the message and filling in the recipients, it does not actually write the message or fill in the recipients in Outlook. The code as I had it has been running perfectly for months but started acting up after I removed Office 365 from this machine. On other PCs, the code still does what it has to. It is only on my machine where I am developing the app that I now have issues.

 

[Anthony47]

I adapted the macro to my test environment as below:

Sub Send_Email_1() 'EMail Tablets

'Dim EmailApp As Outlook.Application
'Dim NewEmailItem As Outlook.MailItem
Dim strSource As String
Dim strRecipient As String
Dim strCC As String

'Set EmailApp = New Outlook.Application
'Set NewEmailItem = EmailApp.CreateItem(olMailItem)


Dim EmailApp As Object          ' Outlook.Application
Dim NewEmailItem As Object      ' Outlook.MailItem

Set EmailApp = CreateObject("Outlook.Application")
Set NewEmailItem = EmailApp.CreateItem(0)

''strAlertBody = "We are require " & CStr(Sheet6.Range("F46").Value) & " Tablets and SIM cards for the " & Sheet1.Range("B1").Value & _
"Project, which runs between " & Sheet1.Range("B4").Value & " and " & Sheet1.Range("B5").Value & ". The Project Manager " & _
"will be " & Sheet1.Range("B2").Value & " and can be contacted at " & Sheet1.Range("D2").Value & " or " & _
Sheet1.Range("C2").Value & "."

strAlertBody = "This is a test"                     '*** TEST

strSource = "Good Day," & vbNewLine & vbNewLine & "This email was automatically generated from the Excel Project Management App." & _
vbNewLine & vbNewLine & strAlertBody & vbNewLine & vbNewLine & "Regards" & vbNewLine & "Project Management"

''strRecipient = CStr(Sheet6.Range("H55").Value)
''strCC = CStr(Sheet6.Range("H56").Value)
strRecipient = Sheets("Sheet2").Range("B2")         '** TEST
strCC = Sheets("Sheet2").Range("B3")                '** TEST


On Error Resume Next
With NewEmailItem
    .To = strRecipient
    .CC = strCC
    .Subject = "Tablets for Project"
    .Body = strSource
''Call MyMacroThatUseOutlook
    '.Attachments.Add ("Path)
    .Send
    .Close (olSave)
    Set EmailApp = Nothing
    Set NewEmailItem = Nothing

End With
'
strLogEvent = " Email for Tablets and SIM Cards was sent"
'''Call Log_Event

End Sub

Text of the message is a string not cells concatenation, for testing
Destination addresses are fetched from B2/B3 of one of my worksheets
Calls to outside macros have been removed (unknown macros)

When I run the macro, it completes without errors, the email is sent and then received.

If the macro doesn't work for you, I suggest that you add these two commends in this position:

''Call MyMacroThatUseOutlook
    '.Attachments.Add ("Path)
    .display
    Stop
    .Send
    .Close (olSave)

Then run the macro: it should stop with the email compiled and ready for sending; if you "continue" the macro the email will be sent

However I suggest using the following version:

Sub Send_Email_my() 'EMail Tablets

'Dim EmailApp As Outlook.Application
'Dim NewEmailItem As Outlook.MailItem
Dim strSource As String
Dim strRecipient As String
Dim strCC As String

'Set EmailApp = New Outlook.Application
'Set NewEmailItem = EmailApp.CreateItem(olMailItem)


Dim EmailApp As Object          ' Outlook.Application
Dim NewEmailItem As Object      ' Outlook.MailItem

Set EmailApp = CreateObject("Outlook.Application")
Set NewEmailItem = EmailApp.CreateItem(0)

''strAlertBody = "We are require " & CStr(Sheet6.Range("F46").Value) & " Tablets and SIM cards for the " & Sheet1.Range("B1").Value & _
"Project, which runs between " & Sheet1.Range("B4").Value & " and " & Sheet1.Range("B5").Value & ". The Project Manager " & _
"will be " & Sheet1.Range("B2").Value & " and can be contacted at " & Sheet1.Range("D2").Value & " or " & _
Sheet1.Range("C2").Value & "."

strAlertBody = "This is a test"                     '*** TEST

strSource = "Good Day," & vbNewLine & vbNewLine & "This email was automatically generated from the Excel Project Management App." & _
vbNewLine & vbNewLine & strAlertBody & vbNewLine & vbNewLine & "Regards" & vbNewLine & "Project Management"

''strRecipient = CStr(Sheet6.Range("H55").Value)
''strCC = CStr(Sheet6.Range("H56").Value)
strRecipient = Sheets("Sheet2").Range("B2")         '** TEST
strCC = Sheets("Sheet2").Range("B3")                '** TEST


'On Error Resume Next                       'Avoid as possible
With NewEmailItem
    .To = strRecipient
    .CC = strCC
    .Subject = "Tablets for Project"
    .Body = strSource
''Call MyMacroThatUseOutlook
    '.Attachments.Add ("Path)
    .display
    Stop                                                          '*** TEST
    .Send
    Application.Wait (Now + TimeValue("0:00:01"))   'Added
End With
Set EmailApp = Nothing                          'Moved here
Set NewEmailItem = Nothing

strLogEvent = " Email for Tablets and SIM Cards was sent"
''Call Log_Event

End Sub

On Error Resume Next has been removed
Two Set xx = Nothing moved outside the With /EndWith
One Application.Wait has been added
.Close (olSave) has been removed (the object is indeed no longer there after having sent)

I work with Office 365

 

[Gideon du Toit]

I adapted the macro to my test environment as below:

Sub Send_Email_1() 'EMail Tablets

'Dim EmailApp As Outlook.Application
'Dim NewEmailItem As Outlook.MailItem
Dim strSource As String
Dim strRecipient As String
Dim strCC As String

'Set EmailApp = New Outlook.Application
'Set NewEmailItem = EmailApp.CreateItem(olMailItem)


Dim EmailApp As Object          ' Outlook.Application
Dim NewEmailItem As Object      ' Outlook.MailItem

Set EmailApp = CreateObject("Outlook.Application")
Set NewEmailItem = EmailApp.CreateItem(0)

''strAlertBody = "We are require " & CStr(Sheet6.Range("F46").Value) & " Tablets and SIM cards for the " & Sheet1.Range("B1").Value & _
"Project, which runs between " & Sheet1.Range("B4").Value & " and " & Sheet1.Range("B5").Value & ". The Project Manager " & _
"will be " & Sheet1.Range("B2").Value & " and can be contacted at " & Sheet1.Range("D2").Value & " or " & _
Sheet1.Range("C2").Value & "."

strAlertBody = "This is a test"                     '*** TEST

strSource = "Good Day," & vbNewLine & vbNewLine & "This email was automatically generated from the Excel Project Management App." & _
vbNewLine & vbNewLine & strAlertBody & vbNewLine & vbNewLine & "Regards" & vbNewLine & "Project Management"

''strRecipient = CStr(Sheet6.Range("H55").Value)
''strCC = CStr(Sheet6.Range("H56").Value)
strRecipient = Sheets("Sheet2").Range("B2")         '** TEST
strCC = Sheets("Sheet2").Range("B3")                '** TEST


On Error Resume Next
With NewEmailItem
    .To = strRecipient
    .CC = strCC
    .Subject = "Tablets for Project"
    .Body = strSource
''Call MyMacroThatUseOutlook
    '.Attachments.Add ("Path)
    .Send
    .Close (olSave)
    Set EmailApp = Nothing
    Set NewEmailItem = Nothing

End With
'
strLogEvent = " Email for Tablets and SIM Cards was sent"
'''Call Log_Event

End Sub

Text of the message is a string not cells concatenation, for testing
Destination addresses are fetched from B2/B3 of one of my worksheets
Calls to outside macros have been removed (unknown macros)

When I run the macro, it completes without errors, the email is sent and then received.

If the macro doesn't work for you, I suggest that you add these two commends in this position:

''Call MyMacroThatUseOutlook
    '.Attachments.Add ("Path)
    .display
    Stop
    .Send
    .Close (olSave)

Then run the macro: it should stop with the email compiled and ready for sending; if you "continue" the macro the email will be sent

However I suggest using the following version:

Sub Send_Email_my() 'EMail Tablets

'Dim EmailApp As Outlook.Application
'Dim NewEmailItem As Outlook.MailItem
Dim strSource As String
Dim strRecipient As String
Dim strCC As String

'Set EmailApp = New Outlook.Application
'Set NewEmailItem = EmailApp.CreateItem(olMailItem)


Dim EmailApp As Object          ' Outlook.Application
Dim NewEmailItem As Object      ' Outlook.MailItem

Set EmailApp = CreateObject("Outlook.Application")
Set NewEmailItem = EmailApp.CreateItem(0)

''strAlertBody = "We are require " & CStr(Sheet6.Range("F46").Value) & " Tablets and SIM cards for the " & Sheet1.Range("B1").Value & _
"Project, which runs between " & Sheet1.Range("B4").Value & " and " & Sheet1.Range("B5").Value & ". The Project Manager " & _
"will be " & Sheet1.Range("B2").Value & " and can be contacted at " & Sheet1.Range("D2").Value & " or " & _
Sheet1.Range("C2").Value & "."

strAlertBody = "This is a test"                     '*** TEST

strSource = "Good Day," & vbNewLine & vbNewLine & "This email was automatically generated from the Excel Project Management App." & _
vbNewLine & vbNewLine & strAlertBody & vbNewLine & vbNewLine & "Regards" & vbNewLine & "Project Management"

''strRecipient = CStr(Sheet6.Range("H55").Value)
''strCC = CStr(Sheet6.Range("H56").Value)
strRecipient = Sheets("Sheet2").Range("B2")         '** TEST
strCC = Sheets("Sheet2").Range("B3")                '** TEST


'On Error Resume Next                       'Avoid as possible
With NewEmailItem
    .To = strRecipient
    .CC = strCC
    .Subject = "Tablets for Project"
    .Body = strSource
''Call MyMacroThatUseOutlook
    '.Attachments.Add ("Path)
    .display
    Stop                                                          '*** TEST
    .Send
    Application.Wait (Now + TimeValue("0:00:01"))   'Added
End With
Set EmailApp = Nothing                          'Moved here
Set NewEmailItem = Nothing

strLogEvent = " Email for Tablets and SIM Cards was sent"
''Call Log_Event

End Sub

On Error Resume Next has been removed
Two Set xx = Nothing moved outside the With /EndWith
One Application.Wait has been added
.Close (olSave) has been removed (the object is indeed no longer there after having sent)

I work with Office 365

Thank you so much for your help! I am going to give it a go using the changes that you suggested, and I will give feedback on how it went.

 

[Gideon du Toit]

Thank you for all your advice Anthony! It worked like a charm! Very much appreciated!

 

[RoryA]

Please do not mark your own post as the answer unless it actually is.