asked on

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

ASKER CERTIFIED SOLUTION

luthv

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sirbounty

ASKER

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!

sirbounty

ASKER

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

sirbounty

ASKER

Ok - most of it's working - I've got one snag I might need help with...I'll post the updated code shortly...

sirbounty

ASKER

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

luthv

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.