Consulting

Results 1 to 12 of 12

Thread: Returning the Caption Property for active controls in an email body

  1. #1

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

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

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    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 &
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

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

  4. #4
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    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
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  5. #5
    Thanks again for helping me out with this.

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

  6. #6
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    Substitute "show1" and "show2" for X and Z
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

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

  8. #8
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    Substitute the actual Control Names for "Ctrl1,2,3"
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

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

  10. #10
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    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.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  11. #11
    Quote Originally Posted by SamT View Post
    Z = Me.Controls(MyCtrls(i)).Value
    [/code]

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

  12. #12
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    That line assumes a bunch of other code preceding
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •