Post reply

Email attachment script ?

  • December 10, 2012 at 1:51 pm

    #285927

    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!

  • December 10, 2012 at 1:54 pm

    #1567166

    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!

  • December 10, 2012 at 1:56 pm

    #1567167

    I want to send 100 different emails (Notifying customers of a change) & yes, the word document will be on the Sql server.

    thx!

  • December 10, 2012 at 2:00 pm

    #1567169

    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!

  • December 10, 2012 at 2:49 pm

    #1567182

    Now I feel really dumb, but what is "CampaignID" ?

  • December 10, 2012 at 3:01 pm

    #1567186

    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!

  • December 10, 2012 at 3:13 pm

    #1567187

    Great, thank you much!!!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply