By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,497 Members | 2,127 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,497 IT Pros & Developers. It's quick & easy.

Retrieve List of Bounced Email Addresses from Outlook into Access

100+
P: 124
A user is sending out a mass mailing through Outlook via Access automation. Many of these email addresses are obsolete and the email will bounce back to Outlook as "undeliverable." The user wants to update his list of email addresses in the application so that he avoids the invalid email addresses for future mailings. Is there any way to get a list of the bounced email addresses from Outlook into Access? The user can put all the bounced emails in a folder, if that helps.
Jan 16 '14 #1
Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,597
The following Code will at least give you a listing of all E-Mails that were not deliverable. Other information should be easily extracted by using the Properties of the Outlook Item Object.
Expand|Select|Wrap|Line Numbers
  1. Dim outOutlook As New Outlook.Application
  2. Dim outNamespace As Outlook.NameSpace
  3. Dim myInbox As Outlook.MAPIFolder
  4. 'Dim myDestFolder As Outlook.MAPIFolder
  5. Dim outItems As Outlook.Items
  6. Dim outItem As Object
  7. 'Dim strFolderName As String
  8.  
  9.  
  10. Set outNamespace = outOutlook.GetNamespace("MAPI")
  11. Set myInbox = outNamespace.GetDefaultFolder(olFolderInbox)
  12.  
  13. Set outItems = myInbox.Items
  14.  
  15. DoCmd.Hourglass True
  16.  
  17. 'Retrieve the Undeliverable E-Mail Subjects
  18. For Each outItem In outItems
  19.   If InStr(outItem.Subject, "Delivery Status Notification (Failure)") > 0 Then
  20.     Debug.Print outItem.Subject
  21.   End If
  22. Next
  23.  
  24. DoCmd.Hourglass False
  25.  
  26. Set outOutlook = Nothing
  27. Set outNamespace = Nothing
  28. Set outItem = Nothing
Jan 17 '14 #2

100+
P: 124
ADezii, thanks a megabyte. I guess I would use something like outItem.EmailAddress to get the email out of it. Thanks again.
Jan 17 '14 #3

ADezii
Expert 5K+
P: 8,597
I am not sure if EMailAddress will work in this context, but if it doesn't, the following Logic should provide an easy solution:
  1. Search every E-Mail in the Inbox analyzing its Subject to determine if it was Undeliverable. In my Code example the Search Text Delivery Status Notification (Failure) worked.
  2. For each Undeliverable, extract the E-Mail Address from the Body of the E-Mail. From what I have seen it will be enclosed within the first occurrence of '<' and a closing '>'.
  3. I would actually write these Undeliverable E-Mail Addresses to a Local table where they would be easily accessible.
  4. Do whatever you feel is best and let us know how you make out.
  5. Partial Code example follows:
    Expand|Select|Wrap|Line Numbers
    1. '****************************** CODE INTENTIONALLY OMITTED ******************************
    2. Dim intFirst As Integer         'Location of first '<'
    3. Dim intLast As Integer          'Location of first '>' after '<'
    4. Dim strBody As String           'Will hold Body Text of Undeliverable E-Mail
    5. Dim strUndlvr                   'Holds the actual Undeliverable E-Mail Address
    6.  
    7. intFirst = InStr(s, "<")
    8. intLast = InStr(intFirst + 1, s, ">")
    9.  
    10. For Each outItem In outItems    'All Items in the Inbox
    11.   If InStr(outItem.Subject, "Delivery Status Notification (Failure)") > 0 Then
    12.     strBody = outItem.Body      'Body of Undeliverable E-Mail
    13.       strUndlvr = Mid$(s, (intFirst + 1), (intLast - intFirst) - 1)     'E-Mail Address
    14.   End If
    15. Next
    16. '****************************** CODE INTENTIONALLY OMITTED ******************************
    17.  
Jan 17 '14 #4

100+
P: 124
ADezii, what is the variable "s" in your code sample? I don't see it declared and am not sure where its value is determined. Thanks for the help.
Jan 17 '14 #5

ADezii
Expert 5K+
P: 8,597
My sincere apologies. The Variable s was simply used to simulate Text in the Body of each Undeliverable E-Mail, and to make sure that the Address Extraction Code worked as intended. I am currently at home and do not have Outlook installed and I needed a method to simulate what a typical Body Text may consist of. The proper approach would be:
Expand|Select|Wrap|Line Numbers
  1. '****************************** CODE INTENTIONALLY OMITTED ******************************
  2. Dim intFirst As Integer         'Location of first '<'
  3. Dim intLast As Integer          'Location of first '>' after '<'
  4. Dim strBody As String           'Will hold Body Text of Undeliverable E-Mail
  5. Dim strUndlvr                   'Holds the actual Undeliverable E-Mail Address
  6.  
  7. For Each outItem In outItems    'All Items in the Inbox
  8.   If InStr(outItem.Subject, "Delivery Status Notification (Failure)") > 0 Then
  9.     strBody = outItem.Body      'Body of Undeliverable E-Mail
  10.       intFirst = InStr(strBody, "<")
  11.       intLast = InStr(intFirst + 1, strBody, ">")
  12.         strUndlvr = Mid$(strBody, (intFirst + 1), (intLast - intFirst) - 1)     'E-Mail Address
  13.   End If
  14. Next
  15. '****************************** CODE INTENTIONALLY OMITTED ******************************
Jan 17 '14 #6

Post your reply

Sign in to post your reply or Sign up for a free account.