×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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 & "<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
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

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

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.

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

(OP)
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

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

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.

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

When testing, comment On Error Resume Next, 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 Application.DisplayAlerts, make sure it is set to true. Before using ActiveWorkbook, check its name.
What is the title of error message dialog?
Do you have com addins installed that may interact with workbook?

combo

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

(OP)
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

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

(OP)
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
.BodyFormat = olFormatHTML
.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
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub 
[/code]





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

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

Good job formatting your code thumbsup2


---- Andy

There is a great need for a sarcasm font.

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

(OP)
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

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

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


---- Andy

There is a great need for a sarcasm font.

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

(OP)
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 ?

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

You can find some information about it here or here smile
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.

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

(OP)
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 

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

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


---- Andy

There is a great need for a sarcasm font.

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

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

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

Quote (DylaBrion)

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)

...
Set OutApp = CreateObject("Outlook.Application")
...


---- Andy

There is a great need for a sarcasm font.

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

(OP)
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 

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

Why not instead of displaying message to the user open Outlook by code here? I.e.:
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 
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

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close