SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Email attachment script ?


Email attachment script ?

Author
Message
jude-32296
jude-32296
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 704
I have a sql table that contains 100 email addresses & a word document that needs to be sent to these recipients. Does anyone have a script to read through a table & send an email with a document attachment I can use ?

thx!



Lowell
Lowell
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36787 Visits: 40272
so you want to send 100 individualized emails, each with the same (or different?) attachment?(hint: cursor)
or do you want to send a single email, with 100 To:/CC:/BCC:/ and the same attachment?

and finally, does the attachment exist on the SQL server?

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jude-32296
jude-32296
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 704
I want to send 100 different emails (Notifying customers of a change) & yes, the word document will be on the Sql server.

thx!



Lowell
Lowell
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36787 Visits: 40272
a rough example of multiple recipients from a query, unique emails, same attachment:

declare
@isql varchar(2000),
@email varchar(64)

declare c1 cursor for select distinct email FROM DatabaseName.dbo.SomeTable WHERE CampaignID = 42 and dbo.IsValidEmail(email) = 1
open c1
fetch next from c1 into @email
While @@fetch_status <> -1
begin
declare @body1 varchar(4000)
set @body1 = 'Latest documentation For You. ' + CONVERT( VARCHAR( 20 ), GETDATE(), 113 ) +
' '


EXEC msdb.dbo.sp_send_dbmail @recipients=@email,
@subject = 'Latest documentation ',
@body = @body1,
@body_format = 'HTML',
@file_attachments = 'c:\ServerAttachments\ERP.pdf'

fetch next from c1 into @email
end
close c1
deallocate c1




Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jude-32296
jude-32296
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 704
Now I feel really dumb, but what is "CampaignID" ?



Lowell
Lowell
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36787 Visits: 40272
it's just example WHERE statement for the data; your query which gathers the "right" emails would be very specific to your business logic, and especially the WHERE statement which decides whether someone gets an email or not;

i just wanted to show, as an example, you don't send to every email in a table, there should be logic.
same thing for distinct email, if someone is in the table more than once, you don't want to look unprofessional by sending them multiple emails.

finally, you can see i was using a udf to make sure an email was valid...no sense trying to send to an empty string, or an email that doesn't have the @ symbol in it.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jude-32296
jude-32296
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 704
Great, thank you much!!!!



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search