XF 1.1 bounced emails - the nightmare

[snoopy5]

Hi

I send out the first time a huge mass email with XF and got around 1000 bounced email messages into the mailbox of my emailaddress for bouncing messages. So I have now 1000 emails I have to go through to copy the email address of the user and search for them in the ACP.

Now I need to find an efficient and fast way to move all those 1000 users into the Usergroup for email verification/ unconfirmed registration.

How can I do this with a script? If someone has a hack or addon or sql script for this, I would pay for it.

 

[Mouth]

Finding a way to automate (1) is the key part. Can't some email client be scripted to parse out the email addresses of bounced emails ? I suspect Outlook could do it.

Food for thought for a developer ...

1. Emails sent from XF contain a mail header with recipient user ID (eg. "X-XenForo-Recipient-UserID=3521")
2. Cron task to POP/IMAP the defined bounces email account, check unread emails for the above header, and action user account appropriately.

Voila! Sounds simply, in theory I guess?

 

[snoopy5]

There is a topic/post authored by me back in Nov '12 (or thereabouts) asking the same question. Use the search function to find it. In that, Jake (99% sure it was him) responded and gave me the query to use on the db for this.
I exported out of XF into MailChimp, send the email, got a list of bounces and/or unsubscribe emails, loaded them into a temp table in XF database, then used the query from above to step through the temp table (sourced from MailChimp) and set all the appropriate XF users to reconfirm required.

is it this one?

http://xenforo.com/community/threads/cleansing-users-best-action.41008/

Thanks Jake
Jake Bunce said: ↑

Change xf_user.user_state to email_confirm. You can also edit the user state by editing the user account in the Admin CP.​

For anyone else looking to do the same or something similar, my query became...
Code:
UPDATE xf_user
INNER JOIN hard_bouncing
ON xf_user.username = hard_bouncing.username
SET xf_user.email = '', xf_user.user_state = 'email_confirm'

I did not know that this information:

INNER JOIN hard_bouncing
ON xf_user.username = hard_bouncing.username

is available in the xf database?

 

[Mouth]

is it this one?

Yes, good find. I didn't manage to do so in a quick 2min check.

I did not know that this information:
[...]
is available in the xf database?

'hardbouncing' was the name of the temporary table I created in the XF database to contain my bouncing records from MailChimp.
So the query set the XF user to 'email_confirm' when there was a matching username in the data of bouncing email addresses I exported from mailChimp.

 

[snoopy5]

ahhh, o.k., so as long as I have not this information of the bounced emails within teh XF db, I can not do it.

I have now over 1000 emails which came back to my "bounced email address) and in which ech of those emails is the email address of one user.

1. How can I extract this email address out of my outlook emails to create a kind of list of ony emails. So no text anyymore ("..this is a permannet error..." etc.), only 1000 email addresses

2. How can I then change the affected 1000 users in XF to invalid account without going manually on each account and change this?

 

[Slavik]

Hmm, the bounce handling addon seems like a good idea...

Doubt it would be hard to do either?

 

[Mouth]

Hmm, the bounce handling addon seems like a good idea...

http://xenforo.com/community/threads/update-user-account-from-bounced-emails.44174/

 

[Mouth]

1. How can I extract this email address out of my outlook emails to create a kind of list of ony emails. So no text anyymore ("..this is a permannet error..." etc.), only 1000 email addresses
2. How can I then change the affected 1000 users in XF to invalid account without going manually on each account and change this?

Find some method to export/extract the email addresses from your Outlook (or extract/export all the emails to text and then use a text editor to remove everything but email addresses)
Then import them into a database table and proceed as per my above recommendation.

Sorry, all I can think of.