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

  • Thread starter Thread starter PHil
  • Start date Start date
P

PHil

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!

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
 
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'
 
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 said:
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 said:
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!

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[/QUOTE][/QUOTE]
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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!

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[/QUOTE][/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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'

:

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!

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[/QUOTE][/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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)

:

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'

:

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!

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[/QUOTE][/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
just to clarify : i am with access 2003 (if it helps)

PHil said:
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 said:
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 said:
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


:

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)

:

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'

:

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!

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[/QUOTE][/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
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 said:
just to clarify : i am with access 2003 (if it helps)

PHil said:
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 said:
Many many thanks! You are the king :)

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

I will try that

Greetings
PHil

:

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


:

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)

:

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'

:

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!

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[/QUOTE][/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top