Export email address from SQL into format for emailing

Dear Experts,

I have the following SQL: SELECT T0.[E_Mail] FROM OCRD T0 WHERE T0.[CardType] = 'C'
This picks up emails addresses, some of the fields are blank and there seems to be some duplicate email addresses, although I guess I could use a distinct statement after the select. Is it possible to export all of these email address into a format that can used to send a new email in outlook?

Kevin
kevin1983Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
I would start with:

SELECT DISTINCT T0.[E_Mail]
FROM OCRD T0
WHERE T0.[CardType] = 'C' AND T0.[E_Mail] IS NOT NULL

"Is it possible to export all of these email address into a format that can used to send a new email in outlook?"

Not sure what you mean.  Do you mean you want to concatenate all of the emails into a single string, separated with semi-colons, which you can then use as the To: address in a single email?
0
kevin1983Author Commented:
Thanks, yes I think your suggestion sounds like a solution on what i'm trying to do.
0
Dale FyeCommented:
Kevin,

How are you trying to do this?  Are you trying to write a macro (VBA code) within Outlook to do this?

There are a variety of concatenation functions that have been written that would facilitate this, but it is just as simple to open the recordset as indicated above, and loop through the recordset, appending the email address to a string.  Code is minimal.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

kevin1983Author Commented:
trying to do all within SQL management studio 2008 if possible, not VBA. Then was thinking of just copying the output of the SQL query results and paste them into the To email address field within a new email in outlook 2010.

Your suggestion of looping through the record set  / appending the email address to a string sounds like a good idea.
0
Dale FyeCommented:
Not sure how to do that within SQL Management Studio.  If you don't get any other responses, you might want to click the "Request attention" button at the bottom right corner of your original post.
0
kevin1983Author Commented:
ok, the below adds the colon after each email address which looks closer to what I need but there is white spaces between each email address and the colon.

SELECT DISTINCT T0.[E_Mail], ';'
FROM OCRD T0
WHERE T0.[CardType] = 'C' AND T0.[E_Mail] IS NOT NULL
0
Vadim RappCommented:
Assuming that you have Microsoft Office, your best solution is to use Word's feature called "Mail merge". There's wizard where you will be able to specify your SQL statement, and that will produce emails. More info here.

Without Word, I would write an SQL script along the following lines:
declare c cursor local fast_forward for
SELECT T0.[E_Mail] FROM OCRD T0 WHERE T0.[CardType] = 'C'

declare @msg varchar(5000), @email varchar(100)
select @msg='<your email body here>'

open c
fetch c into @email

while @@fetch_status=0 begin
   exec master..xp_sendmail @email,@msg,@subject='<your subject here',@no_output=true
   print 'sent to ' + @email
   fetch c into @email
end
close c
deallocate c

Open in new window

The above is for sql server 2000; in more modern sql server you'll probably use sp_dbmail.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
If you don't have many rows of data, you can open your table or view in SQL Management Studio, select all, copy then paste to excel.  You can also export using SQL Management Studio to multiple formats.

From there you can import to outlook.  

It has been a while since I have been on a pc but I thought the newer version of word no longer have the mail merge feature?   If you are going to send out a mass mail, you may run into issues with your ISP.  I would suggest getting a third party SMTP service and send through that.

I use both http://mandrill.com/ and http://sendgrid.com/  I like them both and starting to use mandrill more then sendgrid for smaller accounts only because you get 11,000 emails for free.  I am more confident though when paying the $80 bucks with sendgrid which includes white listing the deliverability could be better.
0
Mark WillsTopic AdvisorCommented:
well, if wanting a list of email addresses as a concatenated string (separated by ';') you can also try :

select T0.[e_mail]+' ; '
from ocrd T0
where T0.[cardtype] = 'c'
and T0.[e_mail] is not null
group by T0.[e_mail]
for xml path('')

Open in new window

Now, that will produce a single line output (as an XML string) which can be easily copied.

You would probably want a bit more testing on the [e_mail] column for at least a reasonable length and that it has the '@' sign (and so on).

e.g.
-- create a test table so we can play without affecting the table OCRD

create table #ee_tst_ocrd (e_mail varchar(255), cardtype char(1))

-- now populate some with test data

insert #ee_tst_ocrd values (NULL,'C')
insert #ee_tst_ocrd values ('fred_flinstone@bedrock.co.ad','D')
insert #ee_tst_ocrd values ('fred_flinstone@bedrock.co.bc','C')
insert #ee_tst_ocrd values ('fred_flinstone@bedrock.co.bc','C')
insert #ee_tst_ocrd values ('jack@thebeanstalk.com','C')
insert #ee_tst_ocrd values ('jack_and_jill@upthehill.com','C')
insert #ee_tst_ocrd values ('jack_and_jill_upthehill.com','C')
insert #ee_tst_ocrd values ('fred','C')

-- now let's try to get a string of just the legitimate email addresses from above

select T0.[e_mail]+' ; '
from #ee_tst_ocrd T0
where T0.[cardtype] = 'c'
and T0.[e_mail] is not null
and len(T0.[e_mail]) > 5
and T0.[e_mail] like '%@%.%'
group by T0.[e_mail]
for xml path('')

-- and that returns...

-- fred_flinstone@bedrock.co.bc ; jack@thebeanstalk.com ; jack_and_jill@upthehill.com ; 

--The extra trailing ';' doesn't worry us at all it will be ignored in the email program

Open in new window


It is also possible to send e-mails from SQL Server... But that is a different question :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vadim RappCommented:
I think, long list of recipients is really bad idea: (1) likely to get blocked by ISP or by antispam filters of the recipients; (2) everyone will see all others' emails, in fact privacy violation .

Putting them in bcc: greatly increases the chance to be blocked by antispam, it's one indicator that actually does score in heuristic spam evaluation.

The best is to send everyone an individual email.
0
Mark WillsTopic AdvisorCommented:
@vadimrapp1 while I am inclined to agree with you, the question asked for a pretty straight forward "list". We don't know how long that list might be. We don't know if that list is going to be used somehow else (maybe setting up mail groups).

I am not so sure about privacy violation (it depends on the nature of the group in question), as a generality it is true enough to be a concern.

But you do raise interesting points, which should be carefully considered.

In reality, I would be more inclined to do as you also suggest and let SQL generate the entire email and send out individually. But as I said above, that really is a different question IMO.
0
Vadim RappCommented:
> that really is a different question IMO.

assuming the knowledge that Outlook is not the only tool that can send email

:-)
0
kevin1983Author Commented:
Thanks all experts for your suggestions, especially vadimrapp1 and Mark, plenty to think about here.

I prefer the suggestion Mark suggested and seems more the type of answer I was looking for but vadimrapp1 mail merge approach sounds like a good option to.

Kevin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.