working with array 1

[MJD1]

I'M Working on a project that I though I had figured out.

Short of it is that I'm linking access to lotus notes to automatically send an email to selected recipients.

I have a form that contains a list box where I can select whom I want to send the email to. The problem is that it puts the emails addresses one after the other seperated by a semi-colon:

ex: email1;email2;emails..ect

The problem is that Lotus notes only sends to the first recipient in the list, not the others.

So I understand that I need to "convert" into an array.

With my limited knowledge, I thought it would be easier to have 4 fields on the form, named email1,email2,email3 and email4 instead of a listbox.

I setup the code in the lotus notes module to retrieve the emails from those above fields.

so far, I can retrieve one email address but not all 4.

I have adapted the Lotus notes code that links to access ,that I obtained on tek-tips, to try and suit this need.
When I use the below code I get a compile error, type mismatch at the strTo= recipient line.

Here in an excerpt:

Dim rec2 As Form
Dim recipient(0 To 4) As String

Set rec2 = [Forms]![export]


recipient(1) = rec2![strTo]
recipient(2) = rec2![strTo3]
recipient(3) = rec2![strTo4]
recipient(4) = rec2![strTo5]

strTo = recipient


Can anyone help me get back on track??
If theres a way of doing this with a listbox, I would appreciate the help, if not I can live with having 4 dropdown boxes.

thanks

 

[gavinjb]

Hi,

I have included a sample code that will split an list box into individual items and place them in an array, hope this helps.

Gavin,

Dim sinput As String
Dim sArray As Variant

sinput = "val1;val2;val3;val4"

sArray = Split(sinput, ";")
Debug.Print sArray(0)
Debug.Print sArray(1)
Debug.Print sArray(2)
Debug.Print sArray(3)

 

[MJD1]

Thanks Gavin,

I'll try and work with your exemple later today. would I be correct in trying the below code:

Dim lboemail As String
Dim sArray As Variant

lboemail = "val1;val2;val3;val4"

sArray = Split(lboemail, ";")
strTo = sArray(0)
strTo = sArray(1)
strTo = sArray(2)
strTo = sArray(3)


thanks

 

[gavinjb]

Yes that would work, but you would be better defining strTo as an array and using

strTo(0)=sArray(0)
...
strTo(3)=sArray(3)

As you willl overwrite the values if not.

Gavin,

 

[fredk]

Dumb question - Is the split function a custom function that you created? Any chance I could have the code for that?

Thanks much!!!!

Fred

 

[EBGreen]

Split is built into VBA.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[fredk]

I get an error - sub or function not defined when I try to run the code ?

Thanks!!!

Fred

 

[lespaul]

//fill the array

Dim recipients( 1 To 2 ) As String
recipients( 1 ) = "someone@somewhere.com"
recipients( 2 ) = "someoneelse@nowhere.com"

//send the email

Call maildoc.Send( False, recipients )

is that how you were trying to send the email?

Leslie

 

[fredk]

Actually, I already have a form set up that calls a function to send the email - I set up my own group in lotus notes with all of the email address' that I wanted - then I just use the group name - However, after viewing this post, I wanted to work on the same scenario (as a learning tool)

I simply placed the code above (see below) into the onclick event of a button to see what results I would get

Dim sinput As String
Dim sArray As Variant

sinput = "val1;val2;val3;val4"

sArray = Split(sinput, ";")
MsgBox sArray(0)
MsgBox sArray(1)
MsgBox sArray(2)
MsgBox sArray(3)

However, I am getting the error that the sub or function (split) is not defined

Thanks for the help!!!!

Fred

 

[lespaul]

I believe that Split is a built in function in Access2000 and above, if you are using Access 97 it won't work.

leslie

here's a function I found in the Access VBA FAQ area that does the same thing:

Public Function Split(ByVal sString As String _
                    , sDelimiter As String _
                    , Optional iCompare As Long 
                            = vbBinaryCompare) As Variant
                    
    Dim sArray() As String, iArrayUpper As Integer _
        , iPosition As Integer
    iArrayUpper = 0
    iPosition = InStr(1, sString, sDelimiter, iCompare)
    Do While iPosition > 0
        ReDim Preserve sArray(iArrayUpper)
        sArray(iArrayUpper) = Left$(sString, iPosition - 1)
        sString = Right$(sString, Len(sString) - iPosition)
        iPosition = InStr(1, sString, sDelimiter, iCompare)
        iArrayUpper = iArrayUpper + 1
    Loop
    ReDim Preserve sArray(iArrayUpper)
    sArray(iArrayUpper) = sString
    Split = sArray
    
End Function

paste this in a module and you should be good to go!

 

[fredk]

Thanks Leslie Yes, I am using 97 - My first thought was that it was 2000 and above - I have "using access 2000" the book - I did not see it in there - that is why I asked -

Appreciate your help!!!!

Fred

 

[lespaul]

no problem, Fred! and thanks for the star!

les

 

[MJD1]

Lespaul,

Is there something missing in the Split function code above. When I paste it I get a Compile error.

thanks
Martin

 

[lespaul]

What's the error? Does it highlight a specific line of code or give you some clue as to what it doesn't like?

The code is directly from: FAQ705-1823

I don't do VBA programming very much so I really can't tell you if there's something wrong with it or not. The

= vbBinaryCompare

in the parameters looks weird to me, but I don't know....

You may want to post something in Forum705.

Leslie

 

[fredk]

fyi - It worked perfectly for me.

Fred

 

[MJD1]

I think I just realized why, my bad. I guess I was very tired. Ill try again a bit later.

thanks guys.