Excel VB - Object Reference not set to an instance of the object?

DylaBrion · Mar 29, 2019

Hi Can anyone help with the following code which is triggered by a command button to save the excel file and create an email in outlook

The saving of the excel works fine

When the code then tries to create the email I get the following message

Object Reference not set to an instance of the object

I click the okay button below the message and the email is created

It's probably something obvious but I'm struggling to see what I am doing wrong

Many Thanks

Here is the code

Sub Close_CR()

Dim OutApp As Object
Dim OutMail As Object
Dim exptrknumber As String
Dim claimantemail As String
Dim ccemail As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

StartDate = [C11]
StartTime = [E11]
EndDate = [G11]
EndTime = [I11]
ActualStartDate = [C41]
ActualStartTime = [E41]
ActualEndDate = [G41]
ActualEndTime = [I41]
ActualStartDate = Format(ActualStartDate, "Long Date")
ActualEndDate = Format(ActualEndDate, "Long Date")
ActualStartTime = Format(ActualStartTime, "hh:mm")
ActualEndTime = Format(ActualEndTime, "hh:mm")
ActualStartDayName = Format(ActualStartDate, "dddd")
ActualEndDayName = Format(ActualEndDate, "dddd")
Title = [I13]

CompletionStatus_State = [C43]
ClosureNotes = [C45]

Application.ScreenUpdating = False ' Prevents screen refreshing.
Name = ActualStartDate & " - CR COMPLETION - " & Title & ".xlsm"
CurrentFile = ThisWorkbook.Name

NewFileType = "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm,"

NewFile = Application.GetSaveAsFilename(InitialFileName:=Name, fileFilter:=NewFileType)

If NewFile <> "" And NewFile <> "False" Then
ActiveWorkbook.SaveAs Filename:=NewFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled

End If

Application.ScreenUpdating = True

createemail:
On Error Resume Next
With OutMail
.to = ""

.CC = ""
.BCC = ""
.Subject = ActualStartDate & " - CR COMPLETION - " & Title
.BodyFormat = olFormatHTML
.htmlBody = "Please see details of the Change Request Completion Below "
.htmlBody = .htmlBody & " Actual Start Date: " & ActualStartDayName & ", " & ActualStartDate
.htmlBody = .htmlBody & " Actual Start Time: " & ActualStartTime
.htmlBody = .htmlBody & " Actual End Date: " & ActualEndDayName & ", " & ActualEndDate
.htmlBody = .htmlBody & " Actual End Time: " & ActualEndTime
.htmlBody = .htmlBody & " Completion Status: " & CompletionStatus_State
.htmlBody = .htmlBody & " Closure Notes: " & ClosureNotes

.htmlBody = .htmlBody & " "
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Andrzejek · Mar 29, 2019

Is any particular line of code highlighted when your error happens?

And please, use TGML code tags to present your code. (You can edit your post to do so)

---- Andy

There is a great need for a sarcasm font.

DylaBrion · Mar 29, 2019

Hi Andrzejek

Thanks for your help

I will check what line of code highlights

Sorry but I am a novice in this and have no idea what TGML code tags are

Thanks

Andrzejek · Mar 29, 2019

Above the box where you write your post/replays, there are some icons to format the text you type. Highlight some text and click on Code icon. Try some of them - use Preview before you post.

---- Andy

There is a great need for a sarcasm font.

combo · Mar 29, 2019

When testing, comment [tt]On Error Resume Next[/tt], do not turn off screen updating. Does the code breaks in specific line? If not, set the environment to break on all errors (VBE IDE menu, Tools>Options dialog).
If you change somewhere [tt]Application.DisplayAlerts[/tt], make sure it is set to true. Before using [tt]ActiveWorkbook[/tt], check its name.
What is the title of error message dialog?
Do you have com addins installed that may interact with workbook?

combo

DylaBrion · Mar 29, 2019

Thanks to both of you for your help

I've been testing the code again and I cannot seem to repeat the fault and it is now working

If it happens again I will follow your advice and let you know

Thanks again

DylaBrion · Apr 10, 2019

Hi Both

I am still experiencing the problem and have followed the help given

I have noticed that the problem occurs when the codeline .Display is executed

Also once I have set the code to break on all errors, it breaks on .BodyFormat = olFormatHTML

Can you please help

Code:

Sub Close_CR()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim exptrknumber As String
    Dim claimantemail As String
    Dim ccemail As String
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    

    ActualStartDate = [C41]
    ActualStartTime = [E41]
    ActualEndDate = [G41]
    ActualEndTime = [I41]
    ActualStartDate = Format(ActualStartDate, "Long Date")
    ActualEndDate = Format(ActualEndDate, "Long Date")
    ActualStartTime = Format(ActualStartTime, "hh:mm")
    ActualEndTime = Format(ActualEndTime, "hh:mm")
    ActualStartDayName = Format(ActualStartDate, "dddd")
    ActualEndDayName = Format(ActualEndDate, "dddd")
    Title = [I13]
    
    CompletionStatus_State = [C43]
    ClosureNotes = [C45]
    
'Application.ScreenUpdating = False    ' Prevents screen refreshing.
Name = ActualStartDate & " - CR COMPLETION - " & Title & ".xlsm"
CurrentFile = ThisWorkbook.Name
 
    NewFileType = "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm,"
 
 
     NewFile = Application.GetSaveAsFilename(InitialFileName:=Name, fileFilter:=NewFileType)
 
    If NewFile <> "" And NewFile <> "False" Then
        ActiveWorkbook.SaveAs Filename:=NewFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled
 
    End If
 
    Application.ScreenUpdating = True

createemail:
On Error Resume Next
With OutMail
.to = ""


.CC = ""
.BCC = ""
.Subject = ActualStartDate & " - CR COMPLETION - " & Title
[highlight #FCE94F].BodyFormat = olFormatHTML[/highlight]
.htmlBody = "Please see details of the Change Request Completion Below "
.htmlBody = .htmlBody & "<br/><br/><b>Actual Start Date: </b>" & ActualStartDayName & ", " & ActualStartDate
.htmlBody = .htmlBody & "<br/><b>Actual Start Time: </b>" & ActualStartTime
.htmlBody = .htmlBody & "<br/><b>Actual End Date: </b>" & ActualEndDayName & ", " & ActualEndDate
.htmlBody = .htmlBody & "<br/><b>Actual End Time: </b>" & ActualEndTime
.htmlBody = .htmlBody & "<br/><b>Completion Status: </b>" & CompletionStatus_State
.htmlBody = .htmlBody & "<br/><b>Closure Notes: </b>" & ClosureNotes


.htmlBody = .htmlBody & "<br/><br/><br/><br/><br/><br/>"
.Attachments.Add ActiveWorkbook.FullName
[highlight #FCE94F].Display[/highlight]
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

[/code]

Andrzejek · Apr 10, 2019

Looks to me like you are using late binding to the Outlook object, but you use olFormatHTML, which is probably not available this way.

Try this instead:

Code:

...
    .BodyFormat = 2   [green]'olFormatHTML[/green]
...

Good job formatting your code [thumbsup2]

---- Andy

There is a great need for a sarcasm font.

DylaBrion · Apr 10, 2019

Hi Andrzejek

Thanks for your help. It seems to be working at the moment

I will let you know if I have any further issue

Thanks again

Andrzejek · Apr 10, 2019

I know you show us just a part of your code, but I hope you always have [tt]Option Explicit[/tt] at the top, right?

---- Andy

There is a great need for a sarcasm font.

DylaBrion · Apr 10, 2019

Er no

I was not aware of that. Where should I put that and should it be added at the start of every Sub / Code ?

Andrzejek · Apr 10, 2019

You can find some information about it here or here

You should also (in your VBA IDE) go to Tools - Options... - Editor tab, and check "Require Variable Declaration" check box. That will put Option Explicit in any new Form, Module, etc.

---- Andy

There is a great need for a sarcasm font.

DylaBrion · Apr 11, 2019

Hi Andrzejek

I have taken you advise and added the Option Explicit Statement. The coder is working well when Outlook is open but I get the same issue as before when outlook is not open.

Is there a way / code that can open Outlook and check that outlook is open (if it is not open) before moving on to the next line in the code?

My current code is below

Code:

Sub option_explicit_SaveandEmail_Click()

'Working in 2000-2010
'This example send the last saved version of the Activeworkbook

    Dim OutApp As Object
    Dim OutMail As Object
    Dim exptrknumber As String
    Dim claimantemail As String
    Dim ccemail As String
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    StartDate = [C11]
    StartTime = [E11]
    EndDate = [G11]
    EndTime = [I11]
    StartDate = Format(StartDate, "Long Date")
    EndDate = Format(EndDate, "Long Date")
    StartTime = Format(StartTime, "hh:mm")
    EndTime = Format(EndTime, "hh:mm")
    StartDayName = Format(StartDate, "dddd")
    EndDayName = Format(EndDate, "dddd")
    TimingofWork = [K11]
    BuildingofWork = [C13]
    Title = [I13]
    DetailedDescriptionofWorks = [C15]
    ImplementationPlan = [F17]
    Whatmonitoring = [F19]
    Backoutplan = [F21]
    IncidentPriority = [F23]
    TestPlan = [F25]
    PostImplementationVerification = [F27]
    ImpacttoSytemOutputsandUsers = [C29]
    Otherifapplicable = [C31]
    CRNumber = [J33]
    Mailaddress1 = [D1]
    Mailaddress2 = [E1]


Application.ScreenUpdating = False    ' Prevents screen refreshing.
Name = StartDate & " - CR Works - " & Title & ".xlsm"
CurrentFile = ThisWorkbook.Name
 
    NewFileType = "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm,"
 
 
     NewFile = Application.GetSaveAsFilename(InitialFileName:=Name, fileFilter:=NewFileType)
 
    If NewFile <> "" And NewFile <> "False" Then
        ActiveWorkbook.SaveAs Filename:=NewFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled
 
       ' Set ActBook = ActiveWorkbook
        'Workbooks.Open CurrentFile
        'ActBook.Close
    End If
 
    Application.ScreenUpdating = True

createemail:
On Error Resume Next
With OutMail
.to = Mailaddress1 & ";" & Mailaddress2


 .CC = ""
.BCC = ""
.Subject = StartDate & " - CR Works - " & Title
.BodyFormat = 2 'olFormatHTML
.htmlBody = "Please see details of the Change Request Below "
.htmlBody = .htmlBody & "<br/><b>Start Date: </b>" & StartDayName & ", " & StartDate
.htmlBody = .htmlBody & "<br/><b>Start Time: </b>" & StartTime
.htmlBody = .htmlBody & "<br/><b>End Date: </b>" & EndDayName & ", " & EndDate
.htmlBody = .htmlBody & "<br/><b>End Time: </b>" & EndTime
.htmlBody = .htmlBody & "<br/><b>Timing of Works: </b>" & TimingofWork
.htmlBody = .htmlBody & "<br/><br/><b>Building: </b>" & BuildingofWork
.htmlBody = .htmlBody & "<br/><br/><b> Title: </b>" & Title
.htmlBody = .htmlBody & "<br/><br/><b>Detailed Description of Works: </b>" & DetailedDescriptionofWorks
.htmlBody = .htmlBody & "<br/><br/><b>Implementation Plan : </b>" & ImplementationPlan
.htmlBody = .htmlBody & "<br/><br/><b>Monitoring: </b>" & Whatmonitoring
.htmlBody = .htmlBody & "<br/><br/><b>What is the Back out Plan: </b>" & Backoutplan
.htmlBody = .htmlBody & "<br/><br/><b>Incident Priority if Change Fails or Back Out Plan Fails: </b>" & IncidentPriority
.htmlBody = .htmlBody & "<br/><br/><b>Test Plan: </b>" & TestPlan
.htmlBody = .htmlBody & "<br/><br/><b>Post Implementation Verification: </b>" & PostImplementationVerification
.htmlBody = .htmlBody & "<br/><br/><b>Impacts: </b>" & ImpacttoSytemOutputsandUsers
.htmlBody = .htmlBody & "<br/><br/><b>Other Impacts: </b>" & Otherifapplicable

.htmlBody = .htmlBody & "<br/><br/><br/><br/><br/><br/>"
.Attachments.Add ActiveWorkbook.FullName

.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
'theend2:
End Sub

Andrzejek · Apr 11, 2019

Some code to check it is available here or there and everywhere

---- Andy

There is a great need for a sarcasm font.

combo · Apr 11, 2019

If ActiveWorkbook is the same as ThisWorkbook, I would use ThisWorkbook everywhere, it is more clear and secure.
In the other thread you started you already test if outlook is open. GetObject raises error if the object (Outlook) does not exists, in this case you clear error and execute CreateObject, that opens Outlook.
I would try to start working with Outlook aftes processing excel, i.e. (after changing the way of getting outlook - test with GetObject first):

Code:

...
createemail:
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
    .to = Mailaddress1 & ";" & Mailaddress2
    ...

combo

Andrzejek · Apr 11, 2019

DylaBrion said:
The coder is working well when Outlook is open

I assume Outlook is open before you start Excel...
If that's the case, it is kind of backwards, since you create new object every time you run your code, and do not rely on already open object (of Outlook)
[tt]
...
Set OutApp = CreateObject("Outlook.Application")
...
[/tt]

---- Andy

There is a great need for a sarcasm font.

DylaBrion · Apr 12, 2019

Hi Combo and Andrzejek

I've tried a number of methods to resolve this and taken a different approach. The user now sees a message box telling them to open outlook and try again. The code is below for reference. Thanks for your help

Now to try and resolve the issue on my other thread!

Code:

Sub option_explicit_SaveandEmail_Click()


    Dim OutApp As Object
    Dim OutMail As Object
    Dim exptrknumber As String
    Dim claimantemail As String
    Dim ccemail As String
 
On Error Resume Next
Set OutApp = GetObject(, "Outlook.Application")
On Error GoTo 0
If Not OutApp Is Nothing Then
  'Outlook is open!
Else: MsgBox "Please Click Okay and Open Outlook and then Save and email again", vbOK + vbExclamation, "Outlook is not Open"

GoTo theend2
  'Outlook not open
End If
    
    StartDate = [C11]
    StartTime = [E11]
    EndDate = [G11]
    EndTime = [I11]
    StartDate = Format(StartDate, "Long Date")
    EndDate = Format(EndDate, "Long Date")
    StartTime = Format(StartTime, "hh:mm")
    EndTime = Format(EndTime, "hh:mm")
    StartDayName = Format(StartDate, "dddd")
    EndDayName = Format(EndDate, "dddd")
    TimingofWork = [K11]
    BuildingofWork = [C13]
    Title = [I13]
    DetailedDescriptionofWorks = [C15]
    ImplementationPlan = [F17]
    Whatmonitoring = [F19]
    Backoutplan = [F21]
    IncidentPriority = [F23]
    TestPlan = [F25]
    PostImplementationVerification = [F27]
    ImpacttoSytemOutputsandUsers = [C29]
    Otherifapplicable = [C31]
    CRNumber = [J33]
    Mailaddress1 = [D1]
    Mailaddress2 = [E1]


Application.ScreenUpdating = False    ' Prevents screen refreshing.
Name = StartDate & " - CR Works - " & Title & ".xlsm"
CurrentFile = ThisWorkbook.Name
 
    NewFileType = "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm,"
 
 
     NewFile = Application.GetSaveAsFilename(InitialFileName:=Name, fileFilter:=NewFileType)
 
    If NewFile <> "" And NewFile <> "False" Then
        ThisWorkbook.SaveAs Filename:=NewFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled
 
       ' Set ActBook = ActiveWorkbook
        'Workbooks.Open CurrentFile
        'ActBook.Close
    End If
 
    Application.ScreenUpdating = True
    
createemail:

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.to = Mailaddress1 & ";" & Mailaddress2


 .CC = ""
.BCC = ""
.Subject = StartDate & " - CR Works - " & Title
.BodyFormat = 2 'olFormatHTML
.htmlBody = "Please see details of the Change Request Below "
.htmlBody = .htmlBody & "<br/><b>Start Date: </b>" & StartDayName & ", " & StartDate
.htmlBody = .htmlBody & "<br/><b>Start Time: </b>" & StartTime
.htmlBody = .htmlBody & "<br/><b>End Date: </b>" & EndDayName & ", " & EndDate
.htmlBody = .htmlBody & "<br/><b>End Time: </b>" & EndTime
.htmlBody = .htmlBody & "<br/><b>Timing of Works: </b>" & TimingofWork
.htmlBody = .htmlBody & "<br/><br/><b>Building: </b>" & BuildingofWork
.htmlBody = .htmlBody & "<br/><br/><b> Title: </b>" & Title
.htmlBody = .htmlBody & "<br/><br/><b>Detailed Description of Works: </b>" & DetailedDescriptionofWorks
.htmlBody = .htmlBody & "<br/><br/><b>Implementation Plan : </b>" & ImplementationPlan
.htmlBody = .htmlBody & "<br/><br/><b>Monitoring: </b>" & Whatmonitoring
.htmlBody = .htmlBody & "<br/><br/><b>What is the Back out Plan: </b>" & Backoutplan
.htmlBody = .htmlBody & "<br/><br/><b>Incident Priority if Change Fails or Back Out Plan Fails: </b>" & IncidentPriority
.htmlBody = .htmlBody & "<br/><br/><b>Test Plan: </b>" & TestPlan
.htmlBody = .htmlBody & "<br/><br/><b>Post Implementation Verification: </b>" & PostImplementationVerification
.htmlBody = .htmlBody & "<br/><br/><b>Impacts: </b>" & ImpacttoSytemOutputsandUsers
.htmlBody = .htmlBody & "<br/><br/><b>Other Impacts: </b>" & Otherifapplicable

.htmlBody = .htmlBody & "<br/><br/><br/><br/><br/><br/>"
.Attachments.Add ThisWorkbook.FullName

.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
theend2:
End Sub

combo · Apr 13, 2019

Why not instead of displaying message to the user open Outlook by code here? I.e.:
[pre]On Error Resume Next
Set OutApp = GetObject(, "Outlook.Application")
On Error GoTo 0
If OutApp Is Nothing Then
' Outlook not open
Set OutApp = CreateObject("Outlook.Application")
End If[/pre]
without creating Outlook later again.

Your current code is strange, the flow is following (pseudocode):
1) test if outlook is open, if yes - continue, otherwise ask user to open Outlook manually and terminate procedure,
2) Outlook is open, load and process excel data to VBA variables,
3) save workbook with code under user defined name,
4) open Outlook (in fact, new instance),
5) create mail and display it.

For me more natural would be:
1) save workbook with code under user defined name,
2) load and process excel data to VBA variables,
3) test if outlook is open, if not- open it (code above),
4) create mail and display it.

combo

DylaBrion · Apr 13, 2019

Combo

Thanks for your help and patience with this. I’m sure you have worked out by now that I am a novice regards coding.

Your help is much appreciated

I will review and let you know how this goes.

Thanks

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Excel VB - Object Reference not set to an instance of the object?

Technical User

Programmer

Technical User

Programmer

Technical User

Technical User

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Technical User

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor