Outlook Rule or VBA for Editing Excel Spreadsheet Based on Undeliverables

Posted on 2009-04-28
Last Modified: 2012-05-06
How can I go about creating a rule in Outlook that processes an "undeliverable" message and opens an Excel spreadsheet I have created with all possible e-mail addresses I have sent an e-mail to, locates the row which matches the bad e-mail address, and changes the value of a couple cells in that row?  Please let me know if this can be accomplished and if you need more information.  

I do not have any code yet for this because I do not even know where to start.  Thanks!
Question by:FunkiNATEr
    LVL 76

    Expert Comment

    by:David Lee
    Hi, FunkiNATEr.

    I might be able to help with this.  Can you give me the layout of the spreadsheet?

    Author Comment

    Just as an example...

    Columns would be something like "e-mail address", "undeliverable attempts", "disable" ... there are many more, but the script would only be concerned with editing these columns.  E-mail address column would have the e-mail address, formatted as text.  The second column is going to be number that starts out at 0 and increments by one every time and undeliverable is received, and the third would be a "yes/no" or some other sort of field that would change, based on the number of attemps made.  Hope this helps.

    I also think it would be good have Outlook throw all undeliverable messages into a subfolder and loop through them first to create an array of e-mail addresses, then open Excel to make all the changes at once instead of upon the arrival of each message.
    LVL 76

    Accepted Solution


    Apologies for being slow.  Here's the code for doing this.  Follow these instructions to use this.

    1.  Start Outlook
    2.  Click Tools > Macro > Visual Basic Editor
    3.  If not already expanded, expand Microsoft Office Outlook Objects
    4.  If not already expanded, expand Modules
    5.  Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert > Module.
    6.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
    7.  Edit the code as needed.  I included comments wherever something needs to or can change
    8.  Click the diskette icon on the toolbar to save the changes
    9.  Close the VB Editor
    10. Create a rule that fires for non-delivery reports
    11. Set the rule's action to "run a script"
    12. Select this script as the script to run
    Sub ProcessNDR(Item As Outlook.MailItem)
        'Change Sheet1 on the following line to the name of the sheet your data is on'
        Const SHEETNAME = "[Sheet1$]"
        Dim arrLines As Variant, _
            varLine As Variant, _
            intAttempts As Integer, _
            strSQL As String, _
            adoCon As Object, _
            adoRS As Object
        arrLines = Split(Item.Body, vbCrLf)
        For Each varLine In arrLines
            If InStr(1, varLine, "@") Then
                varLine = RTrim(LTrim(varLine))
                Set adoCon = CreateObject("ADODB.Connection")
                With adoCon
                    .Provider = "Microsoft.Jet.OLEDB.4.0"
                    'Change the file name and path on the following line
                    .ConnectionString = "Data Source=C:\eeTesting\NDR.xls;Extended Properties=Excel 8.0;"
                End With
                Set adoRS = adoCon.Execute("SELECT * FROM " & SHEETNAME & " WHERE Email='" & varLine & "'")
                If adoRS.BOF Or adoRS.EOF Then
                    MsgBox "No match"
                    intAttempts = adoRS.Fields("Attempts").Value + 1
                    strSQL = "UPDATE " & SHEETNAME & " SET Attempts=" & intAttempts & " WHERE Email='" & varLine & "'"
                    adoCon.Execute strSQL
                End If
                Set adoRS = Nothing
                Set adoCon = Nothing
                Exit For
            End If
    End Sub

    Open in new window


    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now