PDA

View Full Version : Returning the Caption Property for active controls in an email body



trishgyrl
01-31-2018, 03:33 PM
Hello all,

Looking at the picture I attached, I am trying to have all data entered in the form returned in the body of an email.

How can I get the caption of any checkboxes selected returned into the body of my email?

I used the code below but, it returned the caption on the frame instead of the caption of the active control in the frame.

I hope what I'm trying to ask that makes sense...


If Me.Frame1.ActiveControl.Caption = "Yes" And frameNewExis.Visible = True And frameNewExis.ActiveControl.Caption = "Existing" Then
strBody = "<BODY style=" & "font face=calibri" & "font size=11" & ">" & "<b><i>Please refer to the details of the request below:</i></b>" & _
"<br><br>" & _
"********************************************" & _
"<br><br>" & _
"<b>" & Me.lblDate & ": " & "</b>" & Me.txtDate.Value & _
"<br><br>" & _
"<b>" & Me.lblBusinessNeed & ": " & "</b>" & Me.txtBusinessNeed & _
"<br><br>" & _
"<b>" & Me.lblProduct & ": " & "</b>" & Me.txtProduct & _
"<br><br>" & _
"<b>" & Me.lblCopyFrom & ": " & "</b>" & Me.txtCopyFrom & _
"<br><br>" & _
"<b>" & Me.lblExtend & ": " & "</b>" & Me.txtExtend & _
"<br><br>" & _
"<b>" & Me.lblDeal & ": " & "</b>" & Me.frameNewExis.ActiveControl.Caption & _
"<br><br>" & _
"<b>" & Me.frameDealType.Caption & ": " & Me.frameDealType.ActiveControl.Caption & _
"<br><br>" & _
"<b>" & Me.Label1 & ": " & "</b>" & Me.TextBox1 & _
"<br><br>" & _
"********************************************" & "</BODY>"


End If


The part that says: "...Me.frameDealType.ActiveControl.Caption..." didn't turn out as I expected.

Also, if anyone knows a MUCH simpler way to convert inputted values in the form to the body of an email...please share! :)

Thanks in advance.

SamT
01-31-2018, 04:24 PM
The ActiveControl is the Ctrl that has the Focus, IOW, the last Ctrl that got "Clicked," or otherwise selected. Generally that would be a ConmmandButton.

You might try
Me.frameDealType.Controls(ActiveControl).Caption

You might need to

'Module level Variable
Private ActCtlCap As String
Private Sub frameDealType_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ActCtlCap = Me.frameDealType.Controls(ActiveControl).Caption
End Sub
Then later

"<b>" & Me.frameDealType.Caption & ": " & ActCtlCap &

trishgyrl
02-01-2018, 04:06 PM
What if I would like to show the caption and/or value of every control on the form into the email body?

I'm having trouble...thanks in advance. I tried something like below but, how would I have the body of the email include everything found in the loops?


Sub Send_Mail(myfile As String) Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim strTo As String
Dim strSubject As String
Dim strBody As String
Dim sig$

Dim ctl2 As Control
Dim ctl3 As Control
Dim show1 As String
Dim show2 As String


'strSubject = Me.Caption

For Each ctl2 In Me.frameDealType.Controls
For Each ctl3 In Me.frameDealType.Controls


If TypeName(ctl2) = "TextBox" Then
If ctl2.Value <> "" Then
show1 = ctl2.Value
End If
End If


If TypeName(ctl3) = "CheckBox" Or TypeName(ctl3) = "OptionButton" Then
If ctl3.Value = True Then
show2 = ctl3.Caption
End If
End If

Next ctl3
Next ctl2

SamT
02-01-2018, 05:11 PM
All UserForm Controls have a Tag Property that can hold any String value

First, with all Controls I want to see in the EMail, I would add the name of the respective Label to its Tag.

For Each Ctl in Me.Controls
If Ctl.Tag <> "" Then
X = Me.Controls(Ctl.Tag).Caption
Z = Ctl.Value
Next

Collections contain Controls in some arbitrary order. I want my Email to be in a particular order.Create your own Collection and add the Controls in the desired order and use the first snippet above

Dim MyControls as Collection

With MyControls
.Add Me.Ctrl1
.add Me.Ctrl2
.add Me.Ctrl3
etc
End With
'
'
For Each Ctl In MyControls
X = Me.Controls(Ctl.Tag).Caption
Z = Ctl.Value
Next

Alternately,

Dim MyCtrls as Variant
MyCtrls = Array("Ctl1Name", "Ctl2Name", Ctl3Name", etc) 'In desired order

For i = Lbound(MyCtrls) to Ubound(MyCtrls)
X = Me.Controls(Me.Controls(MyCtrls(i)).Tag).Caption
Z = Me.Controls(MyCtrls(i)).Value
Next

Even Better, IMO. Tags not needed

Dim Captions As Variant
Captions = Array("Caption1", "Caption2", Etc) 'In desired order
Dim MyCtrls as Variant
MyCtrls = Array("Ctl1Name", "Ctl2Name", Ctl3Name", etc) 'In desired order

For i = Lbound(MyCtrls) to Ubound(MyCtrls)
X = MyCaptions(i)
Z = Me.Controls(MyCtrls(i)).Value
Next

trishgyrl
02-06-2018, 12:57 PM
Thanks again for helping me out with this.

How would I incorporate the results of the last code you suggested into the email body?

SamT
02-06-2018, 01:17 PM
Substitute "show1" and "show2" for X and Z

trishgyrl
02-06-2018, 02:50 PM
I am getting an error 'method or data member not found' when using .add me.ctrl1

With MyControls
.Add Me.Ctrl1
.add Me.Ctrl2
.add Me.Ctrl3
etc
End With

I didn't understand what the array logic was going to do so, I opted for the 1st and 2nd

SamT
02-06-2018, 02:58 PM
Substitute the actual Control Names for "Ctrl1,2,3"

trishgyrl
02-06-2018, 03:03 PM
Sorry for not understanding so quickly...

So, I would add the names of each control name in the form? So I could have a long list, right?


Is there a way to say for each control add the name to the array without listing them all out?

Or, is there a way to say for each control add the value or caption (whichever applies) to the body of an email (variable named strBody)? I will continue to append each one to the body of the email...

that might be asking for much lol

SamT
02-07-2018, 01:38 AM
Is there a way to say for each control add the name to the array without listing them all out? Easier to just Type or C&P, unless you have tons of controls to add



With a Collection...Check Control Properties and add the ones that match
For Each...
If Property = ??? Then
MyControls.Add

The Problem is that you want to involve two controls, a Label and a ???. Try adding the Label Caption to the ??? Control Tag. Then, to add to a Collection, you can...
If Len(???.Tag) > 0 Then Add

This again places your Email text in an arbitrary order.

trishgyrl
02-15-2018, 10:26 PM
Z = Me.Controls(MyCtrls(i)).Value
[/code]


I am getting a "type mismatch" error with the line of code above.

SamT
02-16-2018, 08:18 AM
That line assumes a bunch of other code preceding