Parse Non-Delivery Report (NDR) in VB 6.0

In previous section, I introduced how to convert email to HTML page. In this section, I will introduce how to parse Non-delivery report (NDR) in VB 6.0.

Read Receipt

Some e-mail applications, such as Microsoft Office Outlook, employ a read-receipt tracking mechanism. A sender selects the receipt request option prior to sending the message. Upon opening the email, each recipient has the option of notifying the sender that the message was opened and read.

However, there is no guarantee that you will get a read-receipt. Some possible reason are that very few e-mail applications or services support read receipts, or simply because users disable the functionality. Those do support read-receipt aren’t necessarily compatible with or capable of recognizing requests from a different e-mail service or application

Delivery Receipt and FailureReport

It is also called a DSN (delivery service notification), which is a request to the recipient’s email server to send you a notification about the delivery of an email you’ve just sent. The notification takes the form of an email, and will tell you if your delivery succeeded (Delivery Receipt), failed, got delayed (Failure Report).

Parse Report

For many email campaign applications, the very important task is detecting if the email is received by recipient or not. Parsing the delivery report is the common way to get the email status. EAGetMail .NET class provides a built-in function (GetReport) to parse the report. The following sample demonstrates how to parse the delivery-report.

If ReporType is DeliveryReceipt or ReadReceipt, the report probably has only OriginalSender, OriginalRecipient and OriginalMessageID information in the report, it depends on the mail server that generated the report.

Note

Remarks: All of examples in this section are based on first section: A simple VB 6.0 project. To compile and run the following example codes successfully, please click here to learn how to create the test project and add reference to your project.

[VB 6.0 Example - Parse delivery report]

The following example codes demonstrate how to parse delivery report.

Note

To get the full sample projects, please refer to Samples section.

Option Explicit

Private Sub ParseReport(ByVal emlFile As String)

    Const FailureReport = 0
    Const DeliveryReceipt = 1
    Const ReadReceipt = 2
    Const Receipt_Deleted = 3

    Dim oMail As New EAGetMailObjLib.Mail
    oMail.LicenseCode = "TryIt"
    oMail.LoadFile emlFile, False

    If Not oMail.IsReport Then
        MsgBox "this is not a report"
        Exit Sub
    End If

    Dim oReport As EAGetMailObjLib.MailReport
    Set oReport = oMail.GetReport()
    ' Get report type
    Select Case oReport.ReportType
    Case DeliveryReceipt
        MsgBox "This is a deliver receipt"
    Case ReadReceipt
        MsgBox "This is a read receipt"
    Case Receipt_Deleted
        MsgBox "This is a unread receipt, this email was deleted without read!"
    Case Else
        MsgBox "This is a failure report"
    End Select

    ' Get original message information
    MsgBox oReport.OriginalSender
    MsgBox oReport.OriginalRecipient
    MsgBox oReport.OriginalMessageID

    If oReport.ReportType = FailureReport Then
        MsgBox oReport.ErrCode
        MsgBox oReport.ErrDescription
        MsgBox oReport.OriginalSubject
        MsgBox oReport.ReportMTA

        Dim oHeaders As EAGetMailObjLib.HeaderCollection
        Set oHeaders = oReport.OriginalHeaders
        Dim i, nCount As Integer
        nCount = oHeaders.Count
        For i = 0 To nCount - 1
            Dim oHeader As EAGetMailObjLib.HeaderItem
            Set oHeader = oHeaders.Item(i)
            MsgBox oHeader.HeaderKey & ": " & oHeader.HeaderValue
        Next
    End If

End Sub

Private Sub Command1_Click()
    Dim curpath As String
    Dim mailbox As String
    Dim oTools As New EAGetMailObjLib.Tools

    ' Create a folder named "inbox" under current directory
    ' to save the email retrieved.
    curpath = App.Path
    mailbox = curpath & "\inbox"
    oTools.CreateFolder mailbox

    Dim files
    Dim i As Long

    ' Get all *.eml files in specified folder and parse it one by one.
    files = oTools.GetFiles(mailbox & "\*.eml")
    For i = LBound(files) To UBound(files)
        ParseReport files(i)
    Next

    Exit Sub
ErrorHandle:
    MsgBox Err.Description
End Sub

Parse Non-Delivery Report (NDR) using EAGetMail Service

To retrieve and parse Failure Report (NDR), you should monitor your sender mailbox. Here I will introduce how to use EAGetMail Service to monitor a mailbox and retrieve non-delivery report and insert it to SQL server on a regular basis.

Install EAGetMail Service

To use EAGetMail Service, you need to download EAGetMail Service and install it on your machine at first.

Create SQL table to store report

Then create a table in your SQL database like this:

CREATE TABLE [dbo].[Failure_Report](
    [reportid] [int] IDENTITY(1,1) NOT NULL,
    [address] [nvarchar](255) NOT NULL,
    [error_code] [nchar](10) NOT NULL,
    [error_desc] [nchar](255) NOT NULL,
    [error_datetime] [datetime] NOT NULL
) ON [PRIMARY]

GO

Create vbscript to process report

Create a vbscript named “parse_reports.vbs” like this:

Const FailureReport = 0 'Specifies that the email is a failure delivery report.
Const DeliveryReceipt = 1 'Specifies that the email is a delivery success report(delivery receipt).
Const ReadReceipt = 2 'Specifies that the email is a read receipt.
Const Receipt_Deleted = 3 'Specifies that the email is an unread receipt.

Dim args, info
Set args = WScript.Arguments

Dim oConn

If args.Length < 1 Then
    info =  "Usage: Parse_Reports.vbs [email folder path]" & Chr(13) & Chr(10)
    info = info & "eg: Parse_Reports.vbs ""c:\my folder""" & Chr(13) & Chr(10)
    WScript.Echo info
    WScript.Quit
End If

Dim oTools, files
Set oTools = CreateObject("EAGetMailObj.Tools")
' get all .eml files from specified folder
files = oTools.GetFiles( args(0) & "\*.eml" )

Dim count, i
count = UBound(files) - LBound(files) + 1
WScript.Echo( "Total " & count & " email(s)." )

Set oConn = CreateObject("ADODB.Connection")
Dim connStr

' For more connection string
' MS SQL Server 2000
'"Driver={SQL Server};Server=localhost; Database=myDB;Uid=myUser;Pwd=myPassword;"

' MS SQL Server 2005
'"Driver={SQL Server Native Client};Server=localhost; Database=myDB;Uid=myUser;Pwd=myPassword;"

' MS SQL Server 2005 Native Provider
'"Provider=SQLNCLI;Server=localhost; Database=myDB;Uid=myUser;Pwd=myPassword;"

' MS SQL Server 2008
'"Driver={SQL Server Native Client 10.0};Server=localhost; Database=myDB;Uid=myUser;Pwd=myPassword;"

' MS SQL Server 2008 Native Provider
'"Provider=SQLNCLI10;Server=localhost; Database=myDB;Uid=myUser;Pwd=myPassword;"

' MS SQL Server 2012
'"Driver={SQL Server Native Client 11.0};Server=localhost; Database=myDB;Uid=myUser;Pwd=myPassword;"

' MS SQL Server 2012 Native Provider
'"Provider=SQLNCLI11;Server=localhost; Database=myDB;Uid=myUser;Pwd=myPassword;"

' change it to your sql server address, database, user and password
' The server/instance name syntax used in the server option is the same for all SQL Server connection strings.
' e.g.: Server=serveraddress\instancename;
' open database connection

connStr = "Driver={SQL Server Native Client 11.0};Server=localhost; Database=myDB;Uid=myUser;Pwd=myPassword;"
WScript.Echo( "Connecting database ..." )

' Connect database
oConn.Open connStr

Dim nReport
nReport = 0

For i = 0 To count - 1
WScript.Echo( "Process " & i + 1 & ":" & files(i) & " ..." )
If ParseEmail( files(i)) Then
        ' Delete the local report file
    oTools.RemoveFile files(i)
End If
WScript.Echo( "" )
Next
oConn.Close
WScript.Echo("Close Data Connection")
WScript.Echo( "Total " & nReport & " failure report(s)")
WScript.Echo( "End" )

Function ParseEmail( fileName )
    Dim oMail
    Set oMail = CreateObject("EAGetMailObj.Mail")
    oMail.LicenseCode = "TryIt"
    oMail.LoadFile fileName, True

    If Not oMail.IsReport Then
        WScript.Echo( "Not a report or receipt!" )
        ParseEmail = False
        Exit Function
    End If


    Dim oReport
    Set oReport = oMail.GetReport()

    'We only process failure report
    If oReport.ReportType <> FailureReport Then
        WScript.Echo( "Not a failure report!" )
        ParseEmail = False
        Exit Function
    End If

    Dim address, errorCode, errorDesc
    address = oReport.OriginalRecipient
    errorCode = oReport.ErrCode
    errorDesc = oReport.ErrDescription

    WScript.Echo( "OriginalRecipient: " & address )
    WScript.Echo( "ErrorCode: " & errorCode )
    WScript.Echo( "ErrorDesc: " & errorDesc )

    If Len(errorDesc) > 250 Then
        errorDesc = Mid( errorDesc, 1, 250 )
    End If

    ' Insert result to database
    Dim sql
    sql = "INSERT INTO [dbo].[Failure_Report]" & _
        " ([address] " & _
        " ,[error_code] " & _
        " ,[error_desc] " & _
        " ,[error_datetime]) " & _
    " VALUES " & _
        " (?, ?, ? , GETDATE())"

    Dim oCommand
    Set oCommand = CreateObject("ADODB.Command")
    oCommand.CommandText = sql
    oCommand.CommandType = 1 'adCmdText
    oCommand.Prepared = true
    oCommand.ActiveConnection = oConn

    Dim oParameter
    Set oParameter = oCommand.CreateParameter( "address",  8, 1, , address )
    oCommand.Parameters.Append oParameter

    Set oParameter = oCommand.CreateParameter( "error_code", 8, 1, , errorCode )
    oCommand.Parameters.Append oParameter

    Set oParameter = oCommand.CreateParameter( "error_desc", 8, 1, , errorDesc )
    oCommand.Parameters.Append oParameter

    oCommand.Execute

    nReport = nReport + 1
    ParseEmail = True

End Function

Set a schedule to check email

Finally, open EAGetMail Service Manager -> Mail Pull Configuration -> New:

  • Input your sender mailbox account information.
  • Create a folder named “inbox” on your machine, this folder is used to store .EML file.
  • Input the folder full path to “Save email file(s) to specified local folder:”;
  • Input vbscript full path [SPACE] folder full path to: “Run specified application after download is finished”.

For example: If your vbscript full path is d:\parse_reports.vbs and your folder is d:\inbox, then input: "d:\parse_reports.vbs" "d:\inbox"

With above setting, EAGetMail Service checks mailbox every 15 minutes and once there is non-delivery report, it will invoke parse_reports.exe to process non-delivery report and insert it to database like this:

Important

If you have “Leave a copy of message on mail server” unchecked, EAGetMail Service will delete all emails in your mailbox after the emails were retrieved to local folder. If your mailbox is only used to retrieve non-delivery report, then I recommend you have “Leave a copy of message on mail server” unchecked to get better performance.

Debug your application

You can run your application directly under DOS prompt without EAGetMail Service. If there is any error, you can debug and fix it.

cscript "d:\parse_reports.vbs" "d:\inbox"

EAGetMail Service is a common solution to process email on a regular basis, you can use above solution to download and process normal emails as well. You just need to change/extend the codes in parse_reports.exe

Common SQL Driver Download

If SQL Server is installed on a remote server, and you don’t have SQL driver installed on local machine, then you need to download and install corresponding driver on local machine.

Next Section

At next section I will introduce how to manage folders with IMAP4/Exchange Web Service (EWS)/WebDAV protocol.

Appendix

Comments

If you have any comments or questions about above example codes, please click here to add your comments.