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:
Run a script rule autoreply using a template
Auto acknowledge receipt of mail with a unique tracking number
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: http://bit.ly/easy_email