Set Object Variable inside an If/ElseIf Statement (Run-time error 5)

xenedra13

Board Regular
Joined
Aug 15, 2014
Messages
58
This macro keeps giving me a run-time error '5'.
Code:
Dim myOlApp As Object
    Dim MyItem As Object
    Set myOlApp = CreateObject("Outlook.Application")
    
    If Range("C6").Value = "June" Then
        MyItem = myOlApp.CreateItemFromTemplate(JuneTemplate)                 'THE ERROR HIGHLIGHTS THIS LINE
    ElseIf Range("C6").Value = "July" Then
        MyItem = myOlApp.CreateItemFromTemplate(JulyTemplate)
    End If
I'm assuming that it isn't possible to set an object equal to something inside an if statement. Is this true? And is there any way around this problem?
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,059
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
This macro keeps giving me a run-time error '5'.
Code:
Dim myOlApp As Object
    Dim MyItem As Object
    Set myOlApp = CreateObject("Outlook.Application")
    
    If Range("C6").Value = "June" Then
        [COLOR=#FF0000][B]Set [/B][/COLOR]MyItem = myOlApp.CreateItemFromTemplate(JuneTemplate)                 'THE ERROR HIGHLIGHTS THIS LINE
    ElseIf Range("C6").Value = "July" Then
        [COLOR=#FF0000][B]Set [/B][/COLOR][COLOR=#FF0000][/COLOR]MyItem = myOlApp.CreateItemFromTemplate(JulyTemplate)
    End If
I'm assuming that it isn't possible to set an object equal to something inside an if statement. Is this true? And is there any way around this problem?

You need to use the Set keyword (as shown above in red) when assigning a reference to an object.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,059
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I included the set keyword and I still got the same error.
It would have helped if you mentioned which error you got. Anyway, I don't use Outlook, so I have not direct knowledge of what you are trying to do. Based on your declaring MyItem as an object, the Set keyword is required, so that was a problem. I have to assume you know what you are doing and that CreateItemFromTemplate is a method of myOlApp variable that does, in fact, return an object. If so, then without knowing the specific error message you got, the only thing I can think of is that either JuneTemplate (or JulyTemplate) is a variable that needs to be assigned a string value of some sort OR it is supposed to be a string argument and is missing the surrounding quote marks.
 

xenedra13

Board Regular
Joined
Aug 15, 2014
Messages
58
The error was listed in the first post I made but I will be more specific. I on the same line I got a "Run-Time Error '5'" "Invalid procedure call or argument". Both JuneTemplate and JulyTemplate are assigned to strings in a separate sub that calls the sub in which this segment is included. June and July Templates are assigned to "C:\Filepath\Filename.oft" and refer to a saved template. The CreateItemFromTemplate is indeed a method that returns an object and this procedure has worked in other situations. The only thing different that I can think of that I have done in this procedure as compared to the others that have all worked perfectly is that it is within an if statement. Thank you for your help
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,059
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I on the same line I got a "Run-Time Error '5'" "Invalid procedure call or argument". Both JuneTemplate and JulyTemplate are assigned to strings in a separate sub that calls the sub in which this segment is included.
When the separate sub calls this one, how are the JuneTemplate and JulyTemplate passed... as direct arguments or globally declared variables?
 

xenedra13

Board Regular
Joined
Aug 15, 2014
Messages
58
They were direct arguments. I just moved them to the declarations section and it worked! Thank you! I hadn't thought about that.
 

Forum statistics

Threads
1,181,635
Messages
5,931,127
Members
436,776
Latest member
kranda

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top