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
Else
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”)
‘.Display
.Send
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