+ Reply to Thread
Results 1 to 1 of 1

Extracting multiple addresses from single email body cell (Outlook Mail > Excel)

  1. #1
    Registered User
    Join Date
    09-13-2015
    Location
    USA
    MS-Off Ver
    Win 7 Enterprise
    Posts
    1

    Extracting multiple addresses from single email body cell (Outlook Mail > Excel)

    Hello--I'm new and hope you can help me.

    I need to compile a list of bounced email addresses. I set up folders in Outlook Mail for the various bounce reasons. I right-clicked the folder I wanted, selected the body as the custom field data I wanted, and extracted it as a .csv file to Excel. I can extract the first bad email from each list, but I don't see whether it's pulling all of them. Here's what I cobbled together in the way of Excel code from other places online:

    Column A = Body
    Column B = Bounced emails flagged by mailto:
    Column C = Column B without the maito: (finished product)

    To extract the bad email(s) from each body, I use this in B2:

    =MID(A2,FIND("mailto:",A2)+1,FIND(">",A2)-FIND("<",A2)-1)

    To remove the mailto: command, I use this in C2:
    =SUBSTITUTE(B2"mailto:","")

    To remove long strings of non-human-readable code as "=VALUE!" in Column A, I use this:

    Home > Find & Select > Go To Special > Blanks > OK
    right-click in empty cell
    Delete > Entire Row > OK
    rinse and repeat as needed


    BUT...

    I think this may only be picking up the first bad address in messages containing two or more bad addys.

    Is Outlook Mail not exporting the full message body? Or is there a little piece of code I'm missing--perhaps a way to concatenate?

    I can't post the actual data, but here's a made-up example of the problem:

    Undeliverable: Important E-mail For You
    From: postmaster@foo.com
    Sent: 1/1/2015
    To: me@bar.com

    Delivery has failed to these recipient or groups:

    santa@foo.com
    Your message wasn't delivered due to a permission or security issue. It may have been rejected by a moderator, the address may only accept e-mail from certain senders, or another restriction may be preventing delivery.

    ladygaga@foo.com
    Your message wasn't delivered due to a permission or security issue. It may have been rejected by a moderator, the address may only accept e-mail from certain senders, or another restriction may be preventing delivery.

    purplesquirrel@foo.com
    Your message wasn't delivered due to a permission or security issue. It may have been rejected by a moderator, the address may only accept e-mail from certain senders, or another restriction may be preventing delivery.


    (and in the body of the message, the tags <mailto:santa@foo.com> etc. appear.)

    So all of the above would be in a single Excel cell, and I want to extract not only "santa@foo.com", but also "ladygaga@foo.com" and "purplesquirrel@foo.com" -- preferably into separate cells, even if they were in the same row as the body message.

    Any ideas or suggestions greatly appreciated. Thank you.
    Last edited by BeachBudget; 09-13-2015 at 05:15 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Mail using VBA with the picture displaying in email body in MS outlook 2010
    By venikumar in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-11-2015, 06:45 AM
  2. vba pull data from outlook body of email through email or subject of mail into excel
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-01-2014, 04:37 AM
  3. send email through excel with different cell range in the body of the mail
    By melody10 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-18-2013, 12:41 AM
  4. [SOLVED] Excel can send mail with multiple cells info as email body and subject, befr 3 day of due
    By ExcelUser2707 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-19-2013, 06:49 AM
  5. [SOLVED] Excel can send mail with multiple cells info as email body and subject, befr 3 day of due
    By ExcelUser2707 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-13-2013, 04:29 AM
  6. Macro for extracting content of outlook mail body (contains table)
    By anchuri_chaitanya in forum Outlook Programming / VBA / Macros
    Replies: 0
    Last Post: 07-14-2011, 03:29 AM
  7. Alt+Print Screen then Opens new Outlook Mail Message and Pastes into Body of Email
    By Alex Pipes in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-26-2010, 06:53 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1