Hi. I'm a true novice in programming and we have to make our own program with excel vba. The concept is a queue system display (similar to that on hospitals, billing offices) which notifies the customers when their number is near. My idea is:
1. A userform that let's customer sign up for a priority number at a given limit. (it sends email after the form is submitted)
2. A display userform that drops a number inside the textbox when clicked 'next'.
3. A function that sends automated emails to customers when their priority number is e.g., 3 numbers near.
Im done with 1 and 2. But my major problem is 3. I wanted to create a conditional logic like "If textbox value is 6 then send email to customer with priority #9." So it should link the priority# cell range in the spreadsheet to the corresponding email address and sends the email.
I hope you get it. Badly need your help. These are my codes:
Userform 1
Userform 2
1. A userform that let's customer sign up for a priority number at a given limit. (it sends email after the form is submitted)
2. A display userform that drops a number inside the textbox when clicked 'next'.
3. A function that sends automated emails to customers when their priority number is e.g., 3 numbers near.
Im done with 1 and 2. But my major problem is 3. I wanted to create a conditional logic like "If textbox value is 6 then send email to customer with priority #9." So it should link the priority# cell range in the spreadsheet to the corresponding email address and sends the email.
I hope you get it. Badly need your help. These are my codes:
Userform 1
Code:
[FONT=Verdana]Private Sub cmdSubmit_Click()[/FONT][/FONT]
Dim nextblankrow As Long
Dim mylastrow As Long
nextblankrow = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
If IsNumeric(Cells(nextblankrow - 1, 6)) = False Then
mylastrow = 1
ElseIf IsNumeric(Cells(nextblankrow - 1, 6)) = True Then
mylastrow = Cells(nextblankrow - 1, 6).Value
mylastrow = mylastrow + 1
End If
Cells(nextblankrow, 1).Value = tbFirstName.Text
Cells(nextblankrow, 2).Value = tbLastName.Text
Cells(nextblankrow, 3).Value = tbDepartment.Text
Cells(nextblankrow, 4).Value = tbQuery.Text
Cells(nextblankrow, 5).Value = tbEmail.Text
Cells(nextblankrow, 6).Value = mylastrow
Cells(nextblankrow, 7).Value = tbDate.Text
MsgBox "Your Priority Number is " & Cells(nextblankrow, 6).Value
Dim myApp As Outlook.Application, myMail As Outlook.MailItem
Set myApp = New Outlook.Application
Set myMail = myApp.CreateItem(olMailItem)
myMail.To = Cells(nextblankrow, 5)
With myMail
.Subject = "AMAZON SERVICE CENTRE"
.Body = Cells(nextblankrow, 1).Value & " " & Cells(nextblankrow, 2).Value & "," & vbCrLf & vbCrLf & "YOUR PRIORITY NUMBER IS " & Cells(nextblankrow, 6).Value & vbCrLf & vbCrLf & "Thank you for choosing Amazon! " & "The operation hours will start at 9:30 AM. " & "Your number will be called shortly after an estimated time of " & Cells(nextblankrow, 6).Value * 10 & " minutes from the specified schedule. " & "You may choose to wait or come back in our Service Centre! " & vbCrLf & "Please refer to your number upon inquiry."
.Send
End With
Set myApp = Nothing
Set myMail = Nothing
Cells.Columns.AutoFit
End Sub
Private Sub cmdClear_Click()
Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Then
ctl.Value = ""
End If
Next ctl
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub UserForm_Activate()
Dim CM As Boolean
Do
If CM = True Then Exit Sub
tbClock = Format(Now, "hh:mm")
DoEvents
Loop
End Sub
Private Sub UserForm_Initialize()
tbFirstName.SetFocus
Me.tbDate = Date
End Sub
[FONT=arial]Userform 2
Code:
[FONT=Verdana]Private Sub cmdNext_Click()[/FONT][/FONT]
Dim x, y As Integer
x = tbDisplay.Value
y = 1
tbDisplay.Value = tbDisplay.Value + y
End Sub
Private Sub UserForm_Initialize()
tbDisplay.Value = 0
End Sub
[FONT=arial]