When Sending from Excel to Outlook?

[JeffGrant]

Hi All,

Can somebody please tell me why ?

In all the examples that I find on the web, I see that when sending information from Excel (or word) to Outlook, there are these 4 steps for sending variables.

'Step 1 - Declare the Variable
Dim rngTo As Excel.Range

'Step 2 - Set the Variable
Set rngTo = .Range("B11")

'Step 3 - use the Variable
.To = rngTo.Value

'Step 4 - Release the Variable
Set rngTo = Nothing


Please excuse my ignorance, but I don't understand why combining Steps 2 & 3 into one line, like below, is not a good way of doing this.
.To = .Range("B11")

Can I assume that if I use .To = .Range("B11"), that the ".To" may not be explicity declared as an Excel Range, which could be contributing periodic errors?

Can somebody please help me understand?

Thanks in advance

 

[DanteAmor]

Generally you declare a variable, or in this case an object of type range, to use the object in different parts of the code. That way you can get all the properties and methods of the object without having to type the full cell name.
I explain with the following example:

Sub ex()
  'Step 1. Declare the object
  Dim rng As Range
  
  'Step 2. You set the cell object to the rng object
  Set rng = Sheets("Sheet1").Range("B11")
  
  With CreateObject("Outlook.Application").CreateItem(0)
    'Step 3. Use the object (Notice how it is using the Value property of the object to pass the value to the .To statement)
    .To = rng.Value
    .Display
  End With
  
  'Step 4. Release
  Set rng = Nothing
End Sub

You use variables to have everything perfectly organized, but sometimes when you are going to use the object only once, as in this example, it is not necessary to declare the object, and it is perfectly valid to use it in the following way:

Sub ex2()
  With CreateObject("Outlook.Application").CreateItem(0)
    'Everything in one step
    .To = Sheets("Sheet1").Range("B11").Value
    .Display
  End With
End Sub

In VBA it is more comfortable to work with simplified code than to be abundant in declarations.

I hope the above helps you.

 

[JeffGrant]

Thanks Dante. I just checked my code. So I am not specifically doing anything wrong that would cause the occasional errors I am getting.

Because to redo the code as in the example I put up, would be adding hundreds of lines to my model.