Set a userform text box value as a variable used in e-mailing

[pwebley]

I have a userform that has several textboxes to send an email. I would like the textbox value to be declared as a variable that can then be referenced in the email code except I don't know how to do it. Here is what I have so far:

    Dim TOrngeAddresses As Range, TOrngeCell As Range, TOstrRecipients As String
    Dim CCrngeAddresses As Range, CCrngeCell As Range, CCstrRecipients As String
    Dim BCCrngeAddresses As Range, BCCrngeCell As Range, BCCstrRecipients As String
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    Dim Flds As Variant

    'Find Emails of multiple people sending as "to"
    Set TOrngeAddresses = UserForm2.TextBox1
    For Each TOrngeCell In TOrngeAddresses.Cells
    TOstrRecipients = TOstrRecipients & "; " & TOrngeCell.Value
    Next
    
    'Find Emails of multiple people sending as "CC"
    Set CCrngeAddresses = TextBox2
    For Each CCrngeCell In CCrngeAddresses.Cells
    CCstrRecipients = CCstrRecipients & "; " & CCrngeCell.Value
    Next
    
    'Find Emails of multiple people sending as "BCC"
    Set BCCrngeAddresses = TextBox3
    For Each BCCrngeCell In BCCrngeAddresses.Cells
    BCCstrRecipients = BCCstrRecipients & "; " & BCCrngeCell.Value
    Next

 

[Ruddles]

I wasn't sure exactly what you meant here, but you seem to be using the values of your userform textboxes quite happily in your code so I don't think you're asking "how do I use the value of a textbox in my code". (If it is, then it's simply Userform2.Textbox2.Value or just Userform2.Textbox2.)

So your code would be something like:-

    'Find Emails of multiple people sending as "to"
    TOstrRecipients = UserForm2.TextBox1
    
    'Find Emails of multiple people sending as "CC"
    CCstrRecipients = UserForm2.TextBox2
    
    'Find Emails of multiple people sending as "BCC"
    BCCstrRecipients = UserForm2.TextBox3

Or are you asking whether the user can type in a value in a textbox and your program will declare a variable whose name is equal to the textbox value?

If so, no, you can't use the value of a textbox to declare a variable. The value of a textbox is only known to VBA after the code starts executing, however VBA creates all its variables during compilation, before the code starts to execute, and therefore before the value of a textbox is available to it.

Does that answer your question or have I got hold of the wrong end of the stick? Or the wrong stick altogether?

 

[pwebley]

you are going in the right direction. I now have this as my code to declare the textboxes as the variable to be used but this is not working with or without the ".value" :

Dim TOrngeAddresses As Range, TOrngeCell As Range, TOstrRecipients As String
    Dim CCrngeAddresses As Range, CCrngeCell As Range, CCstrRecipients As String
    Dim BCCrngeAddresses As Range, BCCrngeCell As Range, BCCstrRecipients As String
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    Dim Flds As Variant

    'Find Emails of multiple people sending as "to"
    Set TOrngeAddresses = UserForm2.TextBox1.Value
    For Each TOrngeCell In TOrngeAddresses.Cells
    TOstrRecipients = TOstrRecipients & "; " & TOrngeCell.Value
    Next
    
    'Find Emails of multiple people sending as "CC"
    Set CCrngeAddresses = UserForm2.TextBox2
    For Each CCrngeCell In CCrngeAddresses.Cells
    CCstrRecipients = CCstrRecipients & "; " & CCrngeCell.Value
    Next
    
    'Find Emails of multiple people sending as "BCC"
    Set BCCrngeAddresses = UserForm2.TextBox3
    For Each BCCrngeCell In BCCrngeAddresses.Cells
    BCCstrRecipients = BCCstrRecipients & "; " & BCCrngeCell.Value

 

[Ruddles]

What's the user entering into the textboxes: the address of a range of cells which contains the addresses to be used, or the actual addresses themselves?

 

[pwebley]

They will enter the e-mail addresses in the text boxes.

 

[Ruddles]

When you write this:-

Set [COLOR=red]TOrngeAddresses[/COLOR] = UserForm2.TextBox1.Value

you're setting a range variable to the contents of the textbox, as if the user is typing Range("A1") into it and A1 has the email addresses in it. You need to set a string variable equal to the value of the textbox.

Like so:-

TOstrRecipients = UserForm2.TextBox1

Step through the code and examine the values of the variables as the code is executed and you'll be able to see what's happening.