Use userform textbox to get user text input

nicnad · Apr 5th 2012

Hi,

I am not too familiar with userform.

I have the following code to automatically send email.

Code

Sub sendmail()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    UserForm1.TextBox1.Value = "Hi, " & vbNewLine & vbNewLine & _
                        "This is a text sample " & vbNewLine & vbNewLine & _
                        "Please modify it to fit your need." & vbNewLine & vbNewLine & _
                        "Thanks."
    UserForm1.Show
    On Error GoTo cleanup
    For Each cell In Columns("d").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And _
           LCase(Cells(cell.Row, "e").Value) = "yes" Then
           If Not Application.Match(cell.Value, cell.EntireColumn, 0) <> cell.Row Then
            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = cell.Value
                .Subject = "test"
                .Body = TextBox1.Value
                'You can add files also like this
                .Attachments.Add (filepathstring)
                .Send  'Or use Display
            End With
            On Error GoTo 0
            Set OutMail = Nothing
        End If
        End If
    Next cell
cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

Display More

The code look for an email in column d and a yes in column e to send an email. The thing is that I would want to have a userform with a textbox appears so the user can choose the content of his email. I am not too sure what to do after showing the user for. Do I just hide it or I need more code to get the input in it? Also I am not sure if this is the best way to do it. Suggestions are welcome.

Thank you for your help!

nicnad · Apr 5th 2012

Re: Use userform textbox to get user text input

To be more precise,

I have a userform with a textboxt and a commandbutton.

I am currently trying code like this :

Code

public text_email as string
Private Sub commandbutton1_Click()
text_email = UserForm1.TextBox1.Value
Unload Me
End Sub

And then having in the previous code :

Code

.Body = text_email

But it is not working. It is like as soon as I unload the userform, the variable text_email gets set to nothing. Hope you understand what I am trying to do.

Thank you for your help and time

trunten · Apr 5th 2012

Re: Use userform textbox to get user text input

Code

Private Sub commandbutton1_Click()
    Me.hide
End Sub

then

Code

.Body = Userform1.TextBox1.Value
Unload UserForm1

jproffer · Apr 5th 2012

Re: Use userform textbox to get user text input

Public variables don't hold their value forever. You could create a worksheet and hide it, then make a cell in that worksheet equal to the variable value before you unload the userform. Then on next run, grab the value from that cell back to the textbox.

nicnad · Apr 5th 2012

Re: Use userform textbox to get user text input

Quote from trunten;603347

Code

Private Sub commandbutton1_Click()
    Me.hide
End Sub

then

Code

.Body = Userform1.TextBox1.Value
Unload UserForm1

Exactly what I was looking for. Thank you for your quick reply!

Use userform textbox to get user text input

Participate now!

Share