April 29, 2018

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

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: http://bit.ly/easy_email

Comments are closed.