I have several checkboxes with names of some of my
collegeaus on a Userform (chkPeter, chkMartin, etc). I
want to used the emailadres that is associated to those
checkboxes. I used the .ControlTip property of the
checkbox to store the emailadres I need.

Now I want to send an email to a collegeau if his checkbox
is checked (chkPeter = True).

From www.rondebruin.nl I already got:

Private Sub Mail_Workbook_Outlook()
'This example send the last saved version of the
Activeworkbook
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail

'Here I need code to get usernames depending on the
checkboxes that are checked:
.To = "

For j = 1 To CheckBoxes.Count '??HOW do I count
number of checked checkboxes??
.Recipients.Add = chk(i).ControlTipText
Next

.CC = ""
.BCC = ""
.Subject = "This is the Subject line - Mail
Workbook"
.Body = "Hi there"
'.Attachments.Add ActiveWorkbook.FullName
.Attachments.Add ActiveWorkbook.Path & "\" &
ActiveWorkbook.Name
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display '.Send 'or use
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

How can I use a loop to :

1. check the name of the checked checkboxes;
2. read the emailaddress?

Thanks in advance!
Martin

Hi Martin

Try this loop
If the value is true add it

Sub test()
Dim ctl As Control
For Each ctl In UserForm1.Controls
If TypeOf ctl Is MSForms.CheckBox Then
If ctl.Value = True Then
'add it
Else
'do nothing
End If
End If
Next
End Sub




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Martin Los" wrote in message ...
I have several checkboxes with names of some of my
collegeaus on a Userform (chkPeter, chkMartin, etc). I
want to used the emailadres that is associated to those
checkboxes. I used the .ControlTip property of the
checkbox to store the emailadres I need.

Now I want to send an email to a collegeau if his checkbox
is checked (chkPeter = True).

From www.rondebruin.nl I already got:

Private Sub Mail_Workbook_Outlook()
'This example send the last saved version of the
Activeworkbook
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail

'Here I need code to get usernames depending on the
checkboxes that are checked:
.To = "

For j = 1 To CheckBoxes.Count '??HOW do I count
number of checked checkboxes??
.Recipients.Add = chk(i).ControlTipText
Next

.CC = ""
.BCC = ""
.Subject = "This is the Subject line - Mail
Workbook"
.Body = "Hi there"
'.Attachments.Add ActiveWorkbook.FullName
.Attachments.Add ActiveWorkbook.Path & "\" &
ActiveWorkbook.Name
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display '.Send 'or use
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

How can I use a loop to :

1. check the name of the checked checkboxes;
2. read the emailaddress?

Thanks in advance!
Martin

That´s just what I needed!

Hartelijk bedankt Ron.

Martin

-----Original Message-----
Hi Martin

Try this loop
If the value is true add it

Sub test()
Dim ctl As Control
For Each ctl In UserForm1.Controls
If TypeOf ctl Is MSForms.CheckBox Then
If ctl.Value = True Then
'add it
Else
'do nothing
End If
End If
Next
End Sub




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Martin Los" wrote
in message ...
I have several checkboxes with names of some of my
collegeaus on a Userform (chkPeter, chkMartin, etc). I
want to used the emailadres that is associated to those
checkboxes. I used the .ControlTip property of the
checkbox to store the emailadres I need.

Now I want to send an email to a collegeau if his
checkbox
is checked (chkPeter = True).

From www.rondebruin.nl I already got:

Private Sub Mail_Workbook_Outlook()
'This example send the last saved version of the
Activeworkbook
'You must add a reference to the Microsoft outlook
Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail

'Here I need code to get usernames depending on the
checkboxes that are checked:
.To = "

For j = 1 To CheckBoxes.Count '??HOW do I count
number of checked checkboxes??
.Recipients.Add = chk(i).ControlTipText
Next

.CC = ""
.BCC = ""
.Subject = "This is the Subject line - Mail
Workbook"
.Body = "Hi there"
'.Attachments.Add ActiveWorkbook.FullName
.Attachments.Add ActiveWorkbook.Path & "\" &
ActiveWorkbook.Name
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display '.Send 'or use
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

How can I use a loop to :

1. check the name of the checked checkboxes;
2. read the emailaddress?

Thanks in advance!
Martin


.