script to send emails to each address in a sql 2000 view

[willcope]

I need an Active x script to send emails to addresses that are listed in a view then update a related table with getdate() once the email has been sent.

Basically the view will find the employees in a particular department from employeetbl were that department has updated news from the noticestbl. Then we need to loop through each record of the view sending and updating for each.

the view has these fields:

DepartmentID
EmployeeID
EmployeeName
EmailAddress
NewsSubject
NewsFilePath
NewsSigniture

The email needs to be:
sent from = fixed address
sent to = [EmailAddress]
subject = [DepartementID]": "[NewsSubject]
HTML email body = "fixed greating"[EmployeeName]"<br>[NewsFilePath]....

I currently have a DTS running an active x script that emails the whole view in an html table to an email address and I have been trying unsuccessfully to modify this code to meet my needs.

Any help would be greatly appreciated.

Thanks
William Cope

 

[fuzzyocelot]

Does it have to be an ActiveX Script? Can it be a stored procedure? We have a stored procedure that we call to send emails from other stored procedures. This gives us the flexibility of looping through tables and so on.

Here's what we use:

CREATE PROCEDURE [dbo].[uspSendCODSYSMail](  
 @chvSubject varchar(512)=NULL,  
 @chvBody varchar(4090)=NULL,  
 @chvTo1 varchar(256)=NULL,  
 @chvTo2 varchar(256)=NULL,  
 @chvTo3 varchar(256)=NULL,  
 @chvCC1 varchar(256)=NULL,  
 @chvCC2 varchar(256)=NULL,  
 @chvCC3 varchar(256)=NULL,  
 @chvBC1 varchar(256)=NULL,  
 @chvBC2 varchar(256)=NULL,  
 @chvBC3 varchar(256)=NULL,  
 @chvAttachment1 varchar(424)=NULL,  
 @chvAttachment2 varchar(424)=NULL,  
 @chvAttachment3 varchar(424)=NULL,   
 @chvFrom varchar(512)=NULL,
 @bitText bit=0,
 @bitHTML bit=0 
)AS  
   Declare @iMsg int, @hr int, @source varchar(255), @description varchar(500),@output varchar(1000), @s varchar(564)
   IF @chvTo1 IS NULL OR @chvTo1='' RETURN -1
   IF @chvFrom IS NULL RETURN -1
   IF @bitHTML=0 AND @bitText=0 SET @bitText=1
   --References to the CDOSYS objects are at the following MSDN Web site:
   --[URL unfurl="true"]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp[/URL]
   EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
   -- [URL unfurl="true"]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp[/URL]
   EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'[/URL]
   IF @hr<>0 GOTO ERROR_HANDLING
   EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver").Value',[/URL] [b][COLOR=red yellow]'PUT YOUR EMAIL SERVER HERE'   --'smtpgate.mycompany.com'[/color][/b]
   IF @hr<>0 GOTO ERROR_HANDLING
   EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
   IF @hr<>0 GOTO ERROR_HANDLING
   IF @chvTo1 IS NOT NULL  AND @chvTo1<>''
   BEGIN
       SET @s=@chvTo1
       IF @chvTo2 IS NOT NULL SET @s=@s+'; '+@chvTo2  
       IF @chvTo3 IS NOT NULL SET @s=@s+'; ' +@chvTo3  
       EXEC @hr = sp_OASetProperty @iMsg, 'To', @s
   END
   IF @chvCC1 IS NOT NULL  AND @chvCC1<>''
   BEGIN
       SET @s=@chvCC1
       IF @chvCC2 IS NOT NULL SET @s=@s+'; '+@chvCC2  
       IF @chvCC3 IS NOT NULL SET @s=@s+'; '+@chvCC3 
       EXEC @hr = sp_OASetProperty @iMsg, 'CC', @s
   END
   IF @chvBC1 IS NOT NULL AND @chvBC1<>''
   BEGIN
       SET @s=@chvBC1
       IF @chvBC2 IS NOT NULL SET @s=@s+'; '+@chvBC2  
       IF @chvBC3 IS NOT NULL SET @s=@s+'; '+@chvBC3 
       EXEC @hr = sp_OASetProperty @iMsg, 'BCC', @s
   END
   IF @chvFrom IS NOT NULL AND @chvFrom<>'' EXEC @hr = sp_OASetProperty @iMsg, 'From', @chvFrom
   IF @chvSubject IS NOT NULL AND @chvSubject<>'' EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @chvSubject
   IF @chvBody IS NOT NULL AND @chvBody<>'' 
   BEGIN
     IF @bitText=1 EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @chvBody
     IF @bitHTML=1 EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @chvBody
   END
   IF @chvAttachment1 IS NOT NULL AND @chvAttachment1<>'' EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment',NULL, @chvAttachment1
   IF @chvAttachment2 IS NOT NULL AND @chvAttachment1<>'' EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment',NULL, @chvAttachment2
   IF @chvAttachment3 IS NOT NULL AND @chvAttachment1<>'' EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment',NULL, @chvAttachment3
   EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

ERROR_HANDLING:
   IF @hr <>0
     select @hr
     BEGIN
       EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
       IF @hr = 0
         BEGIN
           SELECT @output = '  Source: ' + @source
           PRINT  @output
           SELECT @output = '  Description: ' + @description
           PRINT  @output
         END
       ELSE
         BEGIN
           PRINT '  sp_OAGetErrorInfo failed.'
           RETURN
         END
     END

-- Do some error handling after each step if you have to.
-- Clean up the objects created.
   EXEC @hr = sp_OADestroy @iMsg

And here's how to execute it. Of course, you can use variables in place of the strings. You also don't have to include all of the To's, CC's, or BCC's.

EXECUTE uspSendCODSYSMail  @chvSubject='This is my subject line',@chvBody='Here is the body',@chvFrom='FromMe,
@chvTo1=@chvSendTo1,@chvTo2=@chvSendTo2,@chvTo3=@chvSendTo3,
@chvCC1=@chvSendCC1,@chvCC2=@chvSendCC2,@chvCC3=@chvSendCC3,
@chvBC1=@chvSendBCC1,@chvBC2=@chvSendBCC2,@chvBC3=@chvSendBCC3,
@bitHTML=1

You can also use html tags within the body if you want to.

Hope that helps!

 

[AlexCuse]

If you have a lot to send, I think the activeX route might be faster. Basically what you will want to do, is read your view into a recordset, and loop through this recordset to set up and send each email.

If you post the activeX you have maybe it would be easier for someone to help?

Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[willcope]

Alex,

Below is the active x I currently am running to get the whole view in an email. I found this code as an example in a resource and simply modified it slightly for my use.

If I could get it to send individual emails and update the date field it would save me a tremendous amount of time.

Thanks again for your help.

Function Main()
Dim strQuery
Dim strTableResult
Dim bMail
Dim oConn
Dim rstChk
Dim cntChk


'check if there are any records
set oConn = CreateObject("ADODB.Connection")
set rstChk = CreateObject("ADODB.Recordset")
   
'open connection
   oConn.Open "Driver={SQL Server};Server=***************;Database=*******;DSN=;UID=******;PWD=**"

'count records
rstChk.Open "select count(*) FROM [*******].dbo.[******]" , oConn  
rstChk.MoveFirst
cntChk = rstChk(rstChk.Fields.Item(0).Name).Value
set oConn = Nothing
set rstChk = Nothing

'use conditional IF logic to determine whether or not remainder of task will execute
if cntChk > 0 Then
        'go through entire process to send email

            'specify query, build HTML Table (string)  from query results
        strQuery = "SELECT * FROM [********].dbo.[*******]" 

        strTableResult = TableString(strQuery)
       
    

            'call the SendMail function and pass the string containing query used to build HTML table
            bMail = SendMail(strTableResult)
    
        'if the message was sent successfully then return success else return failure
            If bMail = True Then
                 Main = DTSTaskExecResult_Success
            Else
                  Main = DTSTaskExecResult_Failure
            End If
Else
    'Exit task, report success
    Main = DTSTaskExecResult_Success
End If
End Function





'**********************************************************
'Purpose: To send an e-mail message
'Inputs: String containing HTML table
'Ouputs: boolean value

Function SendMail(strTableResult)
    Dim objMessage
            On Error Resume Next
                Dim myMail
   
        'intialize cdo message object
        Set myMail=CreateObject("CDO.Message")

       'define 
        myMail.Subject="*********"  'variable
        myMail.From="****@******.com" 'variable
        myMail.To= "*****@*****.com" 'variable
    
    'body of the email
        myMail.HTMLBody= "<br><br>"& chr(13) & chr(13)& chr(13) & chr(13) & _
        strTableResult &"<br><br>"& chr(13) & chr(13) &chr(13) &chr(13) &chr(13) &chr(13) &chr(13) &chr(13) &chr(13) &  _
        "Evaluate and Notify Accordingly" & Chr(13) & Chr(13) & "auto generated message"
    
    'establish settings for sending of CDO message
        myMail.Configuration.Fields.Item _
            ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")=2[/URL]
        'Name or IP of remote SMTP server
        myMail.Configuration.Fields.Item _
            ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] _
            ="mail10.EmpireDataTech.com"
        'Server port
        myMail.Configuration.Fields.Item _
            ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] _
            =25 
        myMail.Configuration.Fields.Update
        
    'send and clean up
        myMail.Send
        set myMail=nothing
 
'return boolean to calling function
            If ErrorCount <> 0 Then
              SendMail = False
            Else
              SendMail = True
            End If

End Function

'**********************************************************

'Purpose: To build an HTML table string from a query
'Inputs: String with query name (Note - modify this to allow passing of connection string)
'Ouputs: string containing code to build HTML table

Function TableString(strQuery)

    Dim rstResult
    Dim oConn
    Dim strTable

    'Initialize connection and recordset
    set oConn = CreateObject("ADODB.Connection")
    set rstResult = CreateObject("ADODB.Recordset")
    
    'open connection
   oConn.Open "Driver={SQL Server};Server=*******;Database=*****;DSN=;UID=*****;PWD=*****"

    
    'fill recordset
    rstResult.Open strQuery, oConn
    
    'initialize HTML table string (adjust width to fit  query)    
    strTable = "<table border = 1 width = 500> <tr>"
    
    'move to first row in recordset
    rstResult.MoveFirst

    'Add columns and headers to the table
    For Index = 0 to rstResult.Fields.Count -1
        strTable = strTable &"<td bgcolor = 'blue'><font color = 'white'>" & _
        rstResult.Fields.Item(Index).Name &  "</font></td>"
    Next
    
    'close row?
    strTable = strTable & "</tr>"

    'loop through recordset, populating each row of table
    While (Not rstResult.EOF)
        strTable = strTable & "<tr>"
            'loop through recordset columns and add one value at a time
            For Index = 0 to rstResult.Fields.Count - 1
                strTable = strTable & "<td>" & rstResult(rstResult.Fields.Item(Index).Name).Value & _
                "<br></td>"
            Next
        strTable = strTable & "</tr>"
        rstResult.MoveNext
    Wend

      'clean up and to dispose of any objects the script creates.


      set oConn = Nothing
      set rstResult = Nothing
      
          
    'close out HTML string
    strTable = strTable & "</table>"
    
    'return HTML string
TableString = strTable

End Function

 

[AlexCuse]

Hey, that looks familiar

What you will need to do is create another recordset that will contain the email addresses to send to. Also, include a primary key value for the table containing the email addresses if you are planning to do updates for the last email date.

You can then modify the bMail function to accept an email address as a variable.

Once you have your filled recordset, you can loop through it, calling bMail for each individual email address, and before moving to the next row you can send an update query to edit the date/time info on your email table.

Does this make sense?

Hope it helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[willcope]

Alex,

What you have described makes sense from my limited perspective. However I am really clueless as to how to implement it. I was hoping someone else was doing this out there but maybe not.

The date to be updated is in the employees tbl so the index is EmployeeID.

Thanks
Will

 

[AlexCuse]

I don't have a ton of time at the moment, but here is how you can change the send mail function to accept an address as a parameter:

Function SendMail(strTableResult[b],strEmailAdd[/b])
    Dim objMessage
            On Error Resume Next
                Dim myMail
   
        'intialize cdo message object
        Set myMail=CreateObject("CDO.Message")

       'define 
        myMail.Subject="*********"  'variable
        myMail.From=[b]strEmailAdd[/b]'variable
        myMail.To= "*****@*****.com" 'variable

Then within your loop, you can call it like this:

'first, fill a recordset with your email addresses and emploee ID's
'make sure to have a connection open to your database as well
'then:

Do While NOT rs.EOF 
    bMail = SendMail(strTableResult, rs("EmailAdd")
    conn.Execute("update employees set DateColumn = getdate() where EmployeeID = " & rs("EmployeeID"))
    rs.MoveNext
Loop

You also will need to tweak the error handling a little bit (because you'll be attemtping to send many emails not just one) but this should get you started.

Hope it helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom