quick help with VB code on emailing sheets

admiral_Tristan

Board Regular
Joined
Dec 18, 2002
Messages
127
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...
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
Try adding in something like:

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

admiral_Tristan

Board Regular
Joined
Dec 18, 2002
Messages
127
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

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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>
 

Forum statistics

Threads
1,148,530
Messages
5,747,238
Members
424,070
Latest member
smanni3

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