Send Numbered Message Automatically through Outlook

Auto acknowledge receipt of mail with a unique tracking number: How to send out a numbered message automatically through Outlook using VBA. A user receives orders via emails from his business partners. He wishes to place the orders messages in a specific folder and then automatically send out a numbered message or reply to the sender using Outlook 2016. You will notice in the following video that we create certain rules and a script to achieve the automation:

You can view this video on YouTube.

Here’s the complete VBA code to auto acknowledge receipt of mail with a unique tracking number via Outlook 2016:

Sub NumberedReply(olItem As Outlook.MailItem)
Dim olOutMail As MailItem
Dim xlApp As Object
Dim xlWB As Object
Dim xlSheet As Object
Dim iLastRow As Long
Dim bXstarted As Boolean
Dim iNumber As Integer

Const strPath As String = “C:\myEmails\myLog.xlsx”
Const strMessage As String = “Your order has been received. Message reference: DKT/”

On Error Resume Next
Set xlApp = GetObject(, “Excel.Application”)
If Err <> 0 Then
Application.StatusBar = “Please wait while Excel log is opened … ”
Set xlApp = CreateObject(“Excel.Application”)
bXstarted = True
End If
On Error GoTo 0
Set xlWB = xlApp.Workbooks.Open(strPath)
Set xlSheet = xlWB.Sheets(“Sheet1”)

iLastRow = xlSheet.Range(“A” & xlSheet.Rows.Count).End(-4162).Row ‘-4162 XlDirection Enumeration

If xlSheet.Range(“A1”) = “” Then
xlSheet.Range(“A1”) = olItem.Sender
xlSheet.Range(“B1”) = 1
iNumber = 1
xlSheet.Range(“A” & iLastRow + 1) = olItem.Sender
xlSheet.Range(“B” & iLastRow + 1) = xlSheet.Range(“B” & iLastRow) + 1
iNumber = Val(xlSheet.Range(“B” & iLastRow + 1))
End If

Set olOutMail = Application.CreateItem(0)
With olOutMail
.To = olItem.SenderEmailAddress
.Subject = “RE: ” & olItem.Subject
.Body = strMessage & Format(iNumber, “000000”)
End With
xlWB.Close SaveChanges:=True
If bXstarted Then xlApp.Quit
Set olOutMail = Nothing
Set xlSheet = Nothing
Set xlWB = Nothing
Set xlApp = Nothing
End Sub

Further reading:

XlDirection Enumeration

Run a script rule autoreply using a template

Auto acknowledge receipt of mail with a unique tracking number

Published by

Dinesh Kumar Takyar

Welcome to! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: For a structured Excel VBA training course online you can visit:

One thought on “Send Numbered Message Automatically through Outlook”

  1. Hi there Send Numbered Message Automatically through Outlook – Excel VBA Training Online

    Find your audience, build a relationship and sell your products.
    Reach your business goals with AWeber’s email marketing and automation platform.
    With AWeber, you get all the email marketing tools you need to create and send beautiful and engaging emails.
    For a behind-the-scenes look at how you can use AWeber.
    Sign up free to our Test Drive email series:

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.