Returning the Caption Property for active controls in an email body

Printable View

01-31-2018, 03:33 PM
trishgyrl

1 Attachment(s)

Returning the Caption Property for active controls in an email body

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...

Code:

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" & ">" & "Please refer to the details of the request below:" & _ " " & _ "********************************************" & _ " " & _ "" & Me.lblDate & ": " & "" & Me.txtDate.Value & _ " " & _ "" & Me.lblBusinessNeed & ": " & "" & Me.txtBusinessNeed & _ " " & _ "" & Me.lblProduct & ": " & "" & Me.txtProduct & _ " " & _ "" & Me.lblCopyFrom & ": " & "" & Me.txtCopyFrom & _ " " & _ "" & Me.lblExtend & ": " & "" & Me.txtExtend & _ " " & _ "" & Me.lblDeal & ": " & "" & Me.frameNewExis.ActiveControl.Caption & _ " " & _ "" & Me.frameDealType.Caption & ": " & Me.frameDealType.ActiveControl.Caption & _ " " & _ "" & Me.Label1 & ": " & "" & Me.TextBox1 & _ " " & _ "********************************************" & "</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.
01-31-2018, 04:24 PM
SamT

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

Code:

Me.frameDealType.Controls(ActiveControl).Caption

You might need to

Code:

'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

Code:

"" & Me.frameDealType.Caption & ": " & ActCtlCap &
02-01-2018, 04:06 PM
trishgyrl

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?

Code:

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
02-01-2018, 05:11 PM
SamT

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.

Code:

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

Code:

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,

Code:

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

Code:

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
02-06-2018, 12:57 PM
trishgyrl

Thanks again for helping me out with this.

How would I incorporate the results of the last code you suggested into the email body?
02-06-2018, 01:17 PM
SamT

Substitute "show1" and "show2" for X and Z
02-06-2018, 02:50 PM
trishgyrl

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
02-06-2018, 02:58 PM
SamT

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

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
02-07-2018, 01:38 AM
SamT

Quote:

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.
02-15-2018, 10:26 PM
trishgyrl

Quote:

Originally Posted by SamT

Z = Me.Controls(MyCtrls(i)).Value
[/code]

I am getting a "type mismatch" error with the line of code above.
02-16-2018, 08:18 AM
SamT

That line assumes a bunch of other code preceding