Solved: Assistance with email SendTo (xl '97)

[phendrena]

Hi,

I currently have the following code :
SendTo = Array(address1@nospam.com, "address2@nospam.com", "address3@nospam.com")
Is it possible to change the above to be built based on the values from several User form textboxes?
For example (which doesn't work):
Dim strArray As String
strArray = txtbox1.value & ", " & txtbox2.value & ", " & txtbox3.Value
SendTo = strArray
The email is being sent via Lotus Notes and not Outlook.
Would anyone be able to offer any suggestions?

[GTO]

Hi Phendrena :-)

Assuming the top one works, maybe this will help(?). Since you know how many textboxes you have, you could do like...


Option Explicit

Sub ary()
Dim a, b, c
Dim ary(0 To 2)
Dim SendTo
Dim i As Long

'// a, b, and c represent the textboxes
a = "address1@nospam.com"
b = "address2@nospam.com"
c = "address3@nospam.com"

ary(0) = a
ary(1) = b
ary(2) = c

SendTo = ary

For i = LBound(ary) To UBound(ary)
MsgBox ary(i)
Next
End Sub


This will not compensate for empty textboxes though, so you may wish to increase the array's size (ReDim Preserve) as you go through the textboxes, or, only act upon elements of the array that have a value in them.

Hope that helps,

Mark

[phendrena]

Hi Mark,

Yes the first example does work.

Thanks,

[GTO]

My apologies,

I thought (silly me, with as slow as I type) that I could get away with editing my first post (at #2).

For others: I did ask whether the first code (in #1) worked, then edited to the current...

Shame on me...

Mark

[GTO]

AAACKKK!!!

That is supposed to be:

For i = LBound(SendTo) To UBound(SendTo)

...for the example, on top of which I forgot to say that you can simply build/size SendTo the same way.

My bad, way to tired...

Mark

[phendrena]

'// a, b, and c represent the textboxes
a = "address1@nospam.com"
b = "address2@nospam.com"
c = "address3@nospam.com"

ary(0) = a
ary(1) = b
ary(2) = c
Where you say that a, b & c represent the texboxes, do i need to change the a, b, c to the names of the textboxes or the value after the = to the textbox?

Thanks,

[GTO]

Dear Phedrena,

Sorry for such a terrible example. The values shown to the right of the equal signs would be replaced with txtbox"x".Value, where "x" is the number of the textbox.

I am off to hit the sack, as obviously my "thinker" has shut down.

Maybe this will seem more sensible:

Sub exa()
'// Size SendTo based on how many textboxes there are //
Dim SendTo(0 To 2)
'// just for a counter //
Dim i As Long

'// fill the three elements in SendTo with the vals from the textboxes //
SendTo(0) = txtbox1.Value '"address1@nospam.com"
SendTo(1) = txtbox2.Value '"address2@nospam.com"
SendTo(2) = txtbox3.Value '"address3@nospam.com"

'// Then use L and U Bound to loop thru //
For i = LBound(SendTo) To UBound(SendTo)
If InStr(1, SendTo(i), "@", vbTextCompare) > 0 Then
MsgBox "Do something here with: " & SendTo(i)
End If
Next
End Sub



A great day to you and yours,

Mark

[phendrena]

Thank Mark.
That'll do nicely :)