mardi 27 décembre 2016

Excel VBA - Verify if the checkboxes are selected and concatenate respective textboxes's values

I am a beginner in Excel VBA and would like some help, please.

In a customer registration userform I have a "contacts" section, which will be reserved to receive up to 5 contact emails for the customer that is being registered. In this userform I have the following objects:

  • 5 text boxes (txtEmail1, txtEmail2, txtEmail3, ...)
  • 5 checkboxes (ckboxEmail1, ckboxEmail2, ckboxEmail3, ...)
  • 1 commandbutton (bSendEmail)

The emails will be typed in the text boxes and the button will be used to open Outlook Application with the emails already in the "To" field. The checkboxes will be used to define which emails will be included in the "To" field.

Here comes my doubt. How can I verify which checkboxes are checked, to use (concatenate) only their emails from text boxes?

Practical Example 1:

TxtEmail1.value = email1@example.com
TxtEmail2.value = email2@example.com
TxtEmail3.value = empty
TxtEMail4.value = email4@example.com
TxtEMail5.value = empty

CkboxEmail1.value = true
CkboxEmail2.value = true
CkboxEmail3.value = false
CkboxEmail4.value = true
CkboxEmail5.value = false

When I do press the button, the following array must be passed to the ".To" property:

.To = "email1@example.com;email2@example.com;email4@example.com"

Practical example 2:

TxtEmail1.value = email1@example.com
TxtEmail2.value = email2@example.com
TxtEmail3.value = empty
TxtEMail4.value = email4@example.com
TxtEMail5.value = empty

CkboxEmail1.value = false
CkboxEmail2.value = false
CkboxEmail3.value = false
CkboxEmail4.value = true
CkboxEmail5.value = false

When I do press the button, only the 4th email will be passed to the ".To" property, because only its checkbox is true:

.To = "email4@example.com"

Bellow is the code I'm using to open Outlook Application in the bSendEmail Click event.

Private Sub bSendEmail_Click()

Dim outlookApp       As Object
Dim outlookMail      As Object

On Error GoTo error_outlook
Set outlookApp = CreateObject("Outlook.Application")
Set outlookMail = outlookApp.CreateItem(0)
On Error GoTo 0

On Error Resume Next
With outlookMail
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = ""
    .Body = ""
    .Importance = 2
    .Display
End With
On Error GoTo 0

Set outlookMail = Nothing
Set outlookApp = Nothing

Exit Sub

error_outlook:

    MsgBox("some message here...", vbOKOnly + vbExclamation, "ERROR - Outlook")
    Exit Sub

End Sub

Thanks in advance and sorry about the bad English, is not my native language.




Aucun commentaire:

Enregistrer un commentaire