July 3, 2014

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:

4 thoughts on “Automatically send email to users queries in Excel with VBA

  1. Thankyou sir for posting such an interesting topic.
    My question is. I cant submit my data because of this reason. followed below.
    “Dim myApp As Outlook.Application, myMail As Outlook.MailItem”
    this App outlook cant excecuted, because not found in my VB.
    what can I do.

  2. Dear sir thank you for all wonder training videos particularly on automation to stop duplication. Using these I have set up a customer complaints form on excel, once the form completed data is transferred to a spread sheet for trending and analysis.
    The form contains three fields, complaint number, customer name and email address using these three fields I would like to add a send email button to the excel form to automatically send a complaints acknowledgement email to the customer before the details from the form are transferred to the spreadsheet, at the same time set up an auto reminder to ensure that a follow-up response has been sent to the customer within 4 days. how can I do this?

  3. Dear Sir,

    First and foremost, I would like to thank you for all the training videos, they are indeed very useful and easy to understand. Hats off to you…
    While researching on Outlook and VBA I came across some wonderful videos by you…

    I am not so good in VBA at all but do pick and tweak using the coding on google…

    I had two major requirements concerning MS Outlook and MS Excel using VBA..

    1. I receive certain Email(s) with the same subject from the same person or sometimes different person but with always with the same subject and the email contains an excel table which needs to be saved in one of my file always appended leaving one line…I may receive more than one email in a single day so I want to take action only those emails on whom the action is not being taken…

    Example : I want to save the table in the excel file in a specified sheet along with the date and time the email was sent so that if there are 2 emails sent on the same date then there should be some difference between the emails on the same date.

    2. After the email is saved I would be providing some information to the sender , so example if I receive the email with the table with the details such as Sr No, Emp ID, Emp Name as columns I would be adding two more columns to this table at the end with City, Date of Email Received.
    The values in the City column would be vlookedup using the emp id from the Emp Database..

    Now I need to revert to the sender with the these details to the same respective email…so that the original email body is at the bottom and my new email would have the following matter.

    “Dear Sender,”

    “Please find the City details below for the employee details provided”

    The Excel Table with the Added Column-City”

    “Warm Regards
    “Vaibhav Kini”

    Thanking you in anticipation…

Comments are closed.