quick help with VB code on emailing sheets

[admiral_Tristan]

hello gang, i have the following code:

rec = Array("tristan.flynn@irvingoil.com", "phillip.meahan@irvingoil.com,")

I have another email i want to include in this but the address is actually in a cell in the sheet. how can i include the cell name in this list, the cell is I4, however i keep getting errors, i think I'm just missing a " or a braceket somewhere...

 

[tactps]

Try adding in something like:

EmailName= range("I4").text
rec = Array("tristan.flynn@irvingoil.com", "phillip.meahan@irvingoil.com,", EmailName)

 

[admiral_Tristan]

that doesn't seem to work for me. I remember i had the code right in the array part but i can't seem to get the right syntax

 

[tactps]

It may need to be:
rec = Array("tristan.flynn@irvingoil.com", "phillip.meahan@irvingoil.com,", EmailName & ",")
to get the comma at the end.

 

[Zack Barresse]

Hi,

Check out the ReDim and Preserve methods. It will do what you want, whilst keeping your array values. An example might look like this ...


<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> foo()
    <SPAN style="color:#00007F">Dim</SPAN> rec(), i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, str <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    rec = Array("tristan.flynn@irvingoil.com", "phillip.meahan@irvingoil.com")
    <SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> rec(0 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(rec) + 1)
    rec(2) = Range("I7").Text
<SPAN style="color:#007F00">'   'for an example \/\/</SPAN>
    <SPAN style="color:#00007F">For</SPAN> i = <SPAN style="color:#00007F">LBound</SPAN>(rec) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(rec) <SPAN style="color:#00007F">Step</SPAN> 1
        str = str & rec(i) & ", "
    <SPAN style="color:#00007F">Next</SPAN> i
    MsgBox str
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>