Query to get e-mail addresses for contacts and leads

[sbantz]

Is there an easy way to extract e-mail addresses for all Contacts and Leads? The way I have been doing it is rather cumbersome in that I have to change the view to show 5000 records and then do an export of Contacts and open the CSV file in Excel to delete the columns I don't need/want. Then, I do this again for Leads.

I want to have a single column list of e-mail addresses so that I can upload this to ConstantContact.com for our newsletters. I should mention that I do have Zucker Reports installed if that gives me a good starting point.

Thanks in advance.

[andopes]

Hi sbantz

You can run this query on phpMyAdmin

(
SELECT 'Leads' AS module, TRIM( CONCAT( IFNULL( l.first_name, '' ) , ' ', IFNULL( l.last_name, '' ) ) ) AS name, ea.email_address AS email
FROM leads AS l
INNER JOIN email_addr_bean_rel AS eabr ON l.id = eabr.bean_id
AND eabr.bean_module = 'Leads'
AND eabr.deleted =0
INNER JOIN email_addresses AS ea ON eabr.email_address_id = ea.id
AND ea.deleted =0
)
UNION (

SELECT 'Contacts' AS module, TRIM( CONCAT( IFNULL( c.first_name, '' ) , ' ', IFNULL( c.last_name, '' ) ) ) AS name, ea.email_address AS email
FROM contacts AS c
INNER JOIN email_addr_bean_rel AS eabr ON c.id = eabr.bean_id
AND eabr.bean_module = 'Leads'
AND eabr.deleted =0
INNER JOIN email_addresses AS ea ON eabr.email_address_id = ea.id
AND ea.deleted =0
)

Regards