dynamically create email text...

I've got some code that allows me to dynamically create an email note.  It's a response form to our users when requesting more quota space.  The subject and body are both text boxes to be previewed.

I'd like it so that every time the user changes the one text box, that the body information gets updated automatically.
I'm using control arrays, and in that same sub I test another field to ensure that an "A" precedes the value entered.

So, the text boxes populate the subject/body, but I also have option buttons that do as well.
I need to accumulate the logic all into one I suppose so that no matter which is chosen it will update accordingly.

Here's my code:


Private Sub Option1_Click(Index As Integer)
    Dim objFSO As FileSystemObject, strSrcPath As String
    Set objFSO = New FileSystemObject
        If txtInfo(2).Text <> "" Then
            If Text1.Text = "" Then Text1.Text = "Your request for additional storage capacity on "
            Select Case Index
                Case 0 'Option "User"
                    Text1.Text = Text1.Text & "your home share (H: drive) has been "
                Case 1 'Option "Business"
                    Text1.Text = Trim(Text1.Text) & " business share " & Trim(txtInfo(2).Text) & " has been "
                Case 2 'Option "Approved"
                    Text1.Text = Text1.Text & "approved, effective immediately, for an increase of " & txtInfo(4).Text & "."
                Case 3 'Option "Denied"
                    Text1.Text = Text1.Text & "denied." & vbCrLf & vbCrLf & "Reason: "
            End Select
        Else
            MsgBox "Please enter the share name information"
            For x = 0 To 1
                Option1(x).Value = False
            Next x
            Exit Sub
        End If
End Sub

Private Sub txtInfo_Change(Index As Integer)
    If txtInfo(3).Text = "" Then txtInfo(3).Text = Date
    txtInfo(Index).Text = UCase(txtInfo(Index).Text)
    txtInfo(Index).SelStart = Len(txtInfo(Index).Text)
    fieldUpdate
    Select Case Index
        Case 0
            If txtInfo(0).Text = "" Then
                Text2.Text = ""
                Exit Sub
            End If
        Case 1
            If InStr(1, txtInfo(1).Text, "A") = 0 And InStr(1, txtInfo(1).Text, "N") = 0 Then
                txtInfo(1).Text = "A" & txtInfo(1).Text
            End If
        Case 2
            For x = 0 To 1
                If Option1(x).Value = True Then
                    Option1(x).Value = False
                End If
            Next x
    End Select
End Sub

Sub fieldUpdate()
    Dim strDynText As String
    strDynText = "Ticket # %1% referencing share increase request for %2%"
    strDynText = Replace(strDynText, "%1%", "IM" & txtInfo(0).Text)
    strDynText = Replace(strDynText, "%2%", Trim(txtInfo(2).Text))
    Text2.Text = strDynText
End Sub

Private Sub txtInfo_KeyPress(Index As Integer, KeyAscii As Integer)
    If Index = 0 And (KeyAscii <> 8 And KeyAscii < 47 Or KeyAscii > 58) Then KeyAscii = 0
End Sub
LVL 67
sirbountyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

luthvCommented:
How about adopting C style string formatting, note that I don't make any changes to txtInfo_Change event, except for the FormatString function, so your original behavior for txtInfo(1) is still the same.
Although I've found out that if you have type a letter in the textbox and then press backspace you can not change the first letter, it will always be A, if that is your intention then it's fine, but it would create a problem if it's not intend to be that way.

Option Explicit

Private Const APPROVED_MSG As String = "Your request for additional storage capacity on your %0% share (H: drive) has been approved, effective immediately, for an increase of %1%"
Private Const DENIED_MSG As String = "Your request for additional storage capacity on your %0% share (H: drive) has been denied"
Private Const TICKET_MSG As String = "Ticket # %1% referencing share increase request for %2%"

Private Sub Option1_Click(Index As Integer)
    UpdateInfo
End Sub

Private Sub txtInfo_KeyPress(Index As Integer, KeyAscii As Integer)
    If Index = 0 And (KeyAscii <> 8 And KeyAscii < 47 Or KeyAscii > 58) Then KeyAscii = 0
End Sub

Private Sub txtInfo_Change(Index As Integer)
    Dim x As Long
    If txtInfo(3).Text = "" Then txtInfo(3).Text = Date
    txtInfo(Index).Text = UCase(txtInfo(Index).Text)
    txtInfo(Index).SelStart = Len(txtInfo(Index).Text)
    'fieldUpdate
   
    Text2.Text = FormatString("Ticket # %0% referencing share increase request for %1%", _
        "IM" & txtInfo(0).Text, Trim(txtInfo(2).Text))
   
    Select Case Index
        Case 0
            If txtInfo(0).Text = "" Then
                Text2.Text = ""
                Exit Sub
            End If
        Case 1
            If InStr(1, txtInfo(1).Text, "A") = 0 And InStr(1, txtInfo(1).Text, "N") = 0 Then
                txtInfo(1).Text = "A" & txtInfo(1).Text
            End If
        Case 2
            For x = 0 To 1
                If Option1(x).Value = True Then
                    Option1(x).Value = False
                End If
            Next x
    End Select
End Sub

'use C style string format
Private Function FormatString(ByVal textToFormat As String, ParamArray values()) As String
    Dim i As Long
    For i = LBound(values) To UBound(values)
        textToFormat = Replace(textToFormat, "%" & i & "%", values(i))
    Next
    FormatString = textToFormat
End Function

Private Sub UpdateInfo()
    Dim x As Long
    Dim shareType As String
    Dim msgtoFormat As String
   
    If Option1(0).Value = True Then
        shareType = "home"
    Else
        shareType = "business"
    End If
    If Option1(2).Value Then
        msgtoFormat = APPROVED_MSG
    Else
        msgtoFormat = DENIED_MSG
    End If

    If txtInfo(2).Text <> "" Then
        Text1.Text = FormatString(msgtoFormat, shareType, txtInfo(4).Text)
    Else
        MsgBox "Please enter the share name information"
        For x = 0 To 1
            Option1(x).Value = False
        Next x
    End If
End Sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sirbountyAuthor Commented:
luthv - thanx for stopping by!  I was almost to the point of deleting this one..
Yes, the constant "A" is how it's intended...

I'll check out this code tomorrow when I'm back at work.
Thank you!
sirbountyAuthor Commented:
I think I see where you're going with this, so I may be able to work with it.
The problems that I'm having at this point are
1) If the fields are filled in, and User (share) is true, but the approve/deny has not been selected yet, this code chooses to call it as 'denied' and subsequently fills in the body with that info.  Not a 'big' deal, since it will be corrected once Approve is clicked...but is there a way to have it pend a decision until one or the other are true?  I think I can fix this myself tho...
2) I think the code is geared towards dynamically updating the subject line - not the body...  I just changed the size and it remained the same in the body - stepping through it, it's checking the subject line...
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

sirbountyAuthor Commented:
Ok - most of it's working - I've got one snag I might need help with...I'll post the updated code shortly...
sirbountyAuthor Commented:
Changed the constants...I need either
Your request...blah...on your "home share (H: drive) has been...
or
your request...blah...on your business share has been.... (no drive specification)

I also changed some of the control names, so it'll look a bit different to you...

Private Const APPROVED_MSG As String = "Your request for additional storage capacity on your %0% share has been approved, effective immediately, for an increase of %1%"
Private Const DENIED_MSG As String = "Your request for additional storage capacity on your %0% share has been denied"
Private Const TICKET_MSG As String = "Ticket # %1% referencing share increase request for %2%"

Private Sub optData_Click(Index As Integer)
    UpdateInfo
End Sub

Private Sub txtInfo_Change(Index As Integer)
    Dim strSql As String, x As Long
    If txtInfo(3).Text = "" Then txtInfo(3).Text = Date
    If Not blnNewTicket Then
        txtInfo(Index).Text = UCase(txtInfo(Index).Text)
        txtSubject.Text = FormatString("Ticket # %0% referencing share increase request for %1%", "IM" & txtInfo(0).Text, Trim(txtInfo(2).Text))
        UpdateInfo
        Select Case Index
            Case 0
                If txtInfo(0).Text = "" Then
                    txtSubject.Text = ""
                    Exit Sub
                End If
            Case 1
                txtInfo(Index).SelStart = Len(txtInfo(Index).Text)
                If InStr(1, txtInfo(1).Text, "A") = 0 And InStr(1, txtInfo(1).Text, "N") = 0 Then
                    txtInfo(1).Text = "A" & txtInfo(1).Text
                End If
            Case 2
                If txtInfo(2).Text = Right(txtInfo(1), 6) Then txtInfo(2).Text = txtInfo(1).Text
                For x = 0 To 1
                    If optData(x).Value = True Then
                        optData(x).Value = False
                    End If
                Next x
        End Select
    End If
End Sub

Sub fieldUpdate()
    Dim strDynText As String
    strDynText = "Ticket # %1% referencing share increase request for %2%"
    strDynText = Replace(strDynText, "%1%", "IM" & txtInfo(0).Text)
    strDynText = Replace(strDynText, "%2%", Trim(txtInfo(2).Text))
    txtSubject.Text = strDynText
End Sub

Private Function FormatString(ByVal textToFormat As String, ParamArray values()) As String
    Dim i As Long
    For i = LBound(values) To UBound(values)
        textToFormat = Replace(textToFormat, "%" & i & "%", values(i))
    Next
    FormatString = textToFormat
End Function

Private Sub UpdateInfo()
    Dim x As Long
    Dim shareType As String
    Dim msgtoFormat As String
   
    If optData(0).Value = True Then
        shareType = "home"
    Else
        shareType = "business"
    End If
    If optData(2).Value = True Then
        msgtoForm
    For x = 0 Toat = APPROVED_MSG
    ElseIf optData(3).Value = True Then
        msgtoFormat = DENIED_MSG
    End If

    If txtInfo(2).Text <> "" Then
        txtBody.Text = FormatString(msgtoFormat, shareType, txtInfo(4).Text)
    Else
        MsgBox "Please enter the share name information"
        For x = 0 To 1
            optData(x).Value = False
        Next x
    End If
End Sub
luthvCommented:
You might want to clean up the code a bit.
It seems that my posted code has an unused constant TICKET_MSG, so it would be best to replace the string in txtInfo_Change to use the constant instead. And change the TICKET_MSG constant to use starting index of %0% not %1%.
Also you have an unused sub (fieldUpdate) so you might want to remove that also.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.