Need help with sending multiple .bcc from range in excel

[OFFLINEX]

So I'm trying to send a .oft email to a large list of .bcc emails but I cant seem to get the code to pull a range If I define the range like A1:A5 I get this object does not support this method . What do I need to put into the .bcc to get an entire column of addresses? I also need to do this a few times with different .oft's for different columns of email addresses.

This is my code so far:


Sub EmailTemplate1()
Dim outlookapp As Object
Dim outlookmailitem As Object
Dim cell As range
Set outlookapp = CreateObject("Outlook.Application")
Set outlookmailitem = outlookapp.CreateItemFromTemplate("C:\Users\John\Desktop\Template.oft")
With outlookmailitem
.BCC = range.(B2)
.Subject = "Test"
.Display
'.send
End With
End Sub

[Artik]

If you want to manually insert multiple addresses into the BCC field, they should be entered by separating each address with a semicolon.
Wanting to do this in code, you need to take addresses from a range of cells, separate them with semicolons and insert the resulting string into the field. The task can be done by various methods. Below is one of them.

Sub EmailTemplate2()
Dim outlookapp As Object
Dim outlookmailitem As Object
Set outlookapp = CreateObject("Outlook.Application")
Set outlookmailitem = outlookapp.CreateItemFromTemplate("C:\Users\John\Desktop\Template.oft")
With outlookmailitem
.BCC = Join(TransposeDim(Range("B2:C5").Value), ";")
.Subject = "Test"
.Display
'.send
End With
End Sub

Function TransposeDim(varr As Variant) As Variant
Dim v As Variant
Dim i As Long
ReDim v(1 To UBound(varr))
For i = 1 To UBound(varr)
v(i) = varr(i, 1)
Next i
TransposeDim = v
End Function

To make sure that an array is always created from a range of cells, I create a two-column range (B:C), although I will only take data from the first column of the range.

Artik