RE: VBA : how to concatene a list of mails from a form

Tech-Archive recommends: Speed Up your PC by fixing your registry

ok so I progress

question 1 : yes

question 2 : i have to keep fields(0) and replace 0 by the number of the
column where my field Email is

question 3 : i didnt move the button

NEW QUESTION : i have got more than on screen ... after checking it appears
that my query has no condition (but it is like this in the data property of
the sub screen) I have to recreate the jointure and it should be ok


"PHil" wrote:


just to clarify : i am with access 2003 (if it helps)

"PHil" wrote:

SORRY but got little probs :(

question 1

should I keep the [] for my Query ?

question 2

it actually stops here (at the rs.Email) ... should I keep the (0) ? I tried
both but no success

question 3

now the button is in the top of the sub form, maybe I should put in the main
form ?

MANY THANKS
stRecipient = stRecipient & rs.Email & ";"




"PHil" wrote:

Many many thanks! You are the king :)

I come from Cobol programming and my vba knowledge is poor :)

I will try that

Greetings
PHil

"Dennis" wrote:

You need to loop round the recordset that is equivalent to the query that
provides your list of students for the lesson. Adapt the code below to suit
(Field 0 is the E-Mail address)

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM [Your Query]")
rs.MoveFirst
While Not rs.EOF
stRecipient = stRecipient & rs.Fields(0) & ";"
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
Set db = Nothing


"PHil" wrote:

Thanks for your quickly answer!

Okay I am not working with a listbox (so I understand better :)

I have a main screen which is about a lesson

and then a sub one with all students that will participate at this lesson

each student has an email

I made the sending procedure to Outlook (meeting request) for one people
but now I want to have one procedure for the whole lesson (sending one
meeting request to all students)

so i need a "code" that will go through my repeat lines and pick the email
and add it to the previous one

the meeting request is working

I just need the recursive thing (concatene the emails in one field from a
"sub" screen made of repetitive lines)

Hope I am clearer :)

Thanks in advance for your precious help

(hope I dont have to use database and that I can "play" with what is on the
screen)

"Dennis" wrote:

What do you mean by a screen with several lines ??
The code you have is for a listbox, it loops through and checks which items
are selected in the listbox and adds them to your recipient string.
If you make your 'several lines' into a listbox control and allow multi
selections then your code will work. If you cannot use a listbox then you
will have to explain in more detail your 'several lines'

"PHil" wrote:

Hi all,

Here is my prob.

I have a screen with several lines (dynamic, could be more or less, but
minimum = 1)

Each line contains a user with his email

I need to take his email of every line and concatene it in a field

I pick the following code but I am not able to adapt it to my purpose.

Can somebody help me ?

The name of the field is Me![Email]

I want the stRecipient to have all emails separated by ";"

(dont know how to manipulate field on repetitive screen)

Here is the "stolen" and adapted code. It stops a the second line (dont know
what is this .ItemsSelect.Count ....). vItm is declared as variant
stRecipient as String

With Me![Email]
If .ItemsSelected.Count > 0 Then
For vItm = 0 To .ListCount - 1
If .Selected(vItm) = True Then
stRecipient = stRecipient & .ItemData(vItm) & ";"
End If
Next vItm
MsgBox stRecipient
Else
MsgBox "Il n'y a personne à qui envoyer d'invitation."
Exit Sub
End If
End With
.

Quantcast