• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 192
  • Last Modified:

Outlook Rule or VBA for Editing Excel Spreadsheet Based on Undeliverables

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!
  • 2
1 Solution
David LeeCommented:
Hi, FunkiNATEr.

I might be able to help with this.  Can you give me the layout of the spreadsheet?
FunkiNATErAuthor Commented:
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.
David LeeCommented:

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now