Automatically send email to users queries in Excel with VBA

How can we create a user-form in Excel that
(1) allows a user to send a query to a department in a company
(2) automatically receive an email at the address entered on the user-form as confirmation
(3) with subject line: Your query has been received and your tracking number is…
(4) and body: Thank you for submitting your query. We will review and resolve your request within 10 days of receipt. Please quote your tracking number in any future correspondence.

First we create a user-form that looks like so:

User form for CRM

Watch the training video (70 MB) below before you study the code of the various user-form’s objects:

You can watch the video on YouTube.

Next we code the user-form on initialization and the 3 command buttons as shown below:

Private Sub UserForm_Initialize()
‘When the form opens the mouse cursor should be in TextBox1
End Sub

Private Sub CommandButton3_Click()
‘On click the command button will close the program and remove it from memory
Unload Me
End Sub

Private Sub CommandButton2_Click()
‘we use a looping process to quickly clear the data from all textboxes for next data entry
Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = “TextBox” Then
ctl.Value = “”
End If
Next ctl
End Sub

The submit or commandbutton1 does the real processing.

  • The tracking number is generated automatically
  • The data is transferred from the user-form to the Excel worksheet in the next blank row automatically and maintains a record of the data
  • Next a new instance of the email client Outlook is opened
  • It sends an email automatically to the user’s email address
  • Finally the email client Outlook is closed automatically

Private Sub CommandButton1_Click()
Dim nextblankrow As Long
Dim mylastrefno As Long
nextblankrow = Sheets(“sheet1”).Range(“A” & Rows.Count).End(xlUp).Offset(1, 0).Row
If IsNumeric(Cells(nextblankrow – 1, 6)) = False Then
mylastrefno = 1
ElseIf IsNumeric(Cells(nextblankrow – 1, 6)) = True Then
mylastrefno = Cells(nextblankrow – 1, 6).Value
mylastrefno = mylastrefno + 1
End If

Cells(nextblankrow, 1).Value = TextBox1.Text
Cells(nextblankrow, 2).Value = TextBox2.Text
Cells(nextblankrow, 3).Value = TextBox3.Text
Cells(nextblankrow, 4).Value = TextBox4.Text
Cells(nextblankrow, 5).Value = TextBox5.Text
Cells(nextblankrow, 6).Value = mylastrefno

Dim myApp As Outlook.Application, myMail As Outlook.MailItem
Set myApp = New Outlook.Application
Set myMail = myApp.CreateItem(olMailItem)
myMail.To = Cells(nextblankrow, 4)

With myMail
.Subject = “your query has been received and your tracking number is ” & Cells(nextblankrow, 6).Value
.Body = “Thank you for submitting your query. ” & vbCrLf & “We will review and resolve your request within 10 days of receipt. ” & vbCrLf & “Please quote your tracking number in any future correspondence.”
End With
Set myApp = Nothing
Set myMail = Nothing
End Sub

In a way we have created a customer relationship management system (CRM).

Download sample file by clicking on the Excel icon: