Click here to Skip to main content
15,668,842 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
See more:
 CnnStr="Provider=SQLOLEDB.1;Password=xxx;Persist Security Info=True;User ID=cpmsuser;Initial Catalog=aaUAT;Data Source=10.22.512.53"

 set con=CreateObJect("Adodb.Connection")
 set Rs=createObject("Adodb.Recordset")

 Dim myMail
 Dim strBody
      Dim Mailforperiod

     Set obj_fso = CreateObject("Scripting.FileSystemObject")
 con.Open CnnStr

 MyQuery="select  replace([brknm],' ','_')+'.xls' file_name,* from broker_july with(nolock) where isnull(sent,0)=0  and isnull(category,'')='A'"
 Rs.Open MyQuery, con

 while not(Rs.Eof)

   Set objCDO = CreateObject("CDO.Message")
    schema = ""
   With objCDO


       .From     = "XYZ Company LTD.<>"

       '.Subject  = "Brokerage statement for the month of Mar 14"
       .Subject  = "Agency Commission statement for the month of "& Mailforperiod

        strMailBody =""
           strMailBody ="To"&vbLf

           strMailBody =strMailBody &""&trim(rs("nm"))&","&vbLf&vbLf&vbLf
           'strMailBody =strMailBody &"Enclosed is the revised Brokerage statement for the month of Mar 14. The amount payable for Mar 14 is Rs."&rs("Commamt")   &vbLf&vbLf
           strMailBody =strMailBody &"Enclosed is the Agency Commission statement for the month of "& Mailforperiod &". The amount payable for "& Mailforperiod &" is Rs."&rs("Commamt")   &vbLf&vbLf

       'strMailBody =strMailBody &"Policywise statement have also been enclosed for your reference. You are requested to raise a brokerage invoice along with service tax for brokerage disbursement and submit to our Sales manager or at our nearest branch office."  &vbLf&vbLf
       strMailBody =strMailBody &"Policywise statement have also been enclosed for your reference."'Commission has been disbursed via Cheque/NEFT "&trim(rs("chqno"))&" on 29-Mar-2014."  &vbLf&vbLf

           strMailBody =strMailBody &"Incase of any query please feel free to contact our Sales Manager servicing you."&vbLf&vbLf
           strMailBody =strMailBody &"Note: As a company policy, we are releasing brokerage only on premium cheque realization."&vbLf&vbLf
           strMailBody =strMailBody &"It is a system generated mail, please do not reply."&vbLf&vbLf&vbLf

       strMailBody =strMailBody &"Regards"&vbLf&vbLf&vbLf
       strMailBody =strMailBody &"XYZ Team"&vbLf&vbLf
       strMailBody =strMailBody & "This email and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies and the original message. Any unauthorized review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this email is strictly prohibited and may be unlawful. The recipient acknowledges that XYZ General Insurance Company Limited, is unable to exercise control or ensure or guarantee the integrity of/over the contents of the information contained in email transmissions and further acknowledges that any views expressed in this message are those of the individual sender and no binding nature of the message shall be implied or assumed unless the sender does so expressly with due authority of XYZ General Insurance Company Limited."
       .textBody =strMailBody

       With .Configuration.Fields
           .Item (schema & "sendusing") = 2
           .Item (schema & "smtpserver") =""
           .Item (schema & "smtpserverport") = 25
           .Item (schema & "smtpauthenticate") = cdoBasic

       End With

               If obj_fso.fileExists("\\\mahesh_scan\Mail\BrkFile\"&trim(rs("file_name")) ) then

                        .AddAttachment "\\\mahesh_scan\Mail\BrkFile\"&trim(rs("file_name"))
                       ' msgbox "hi"
               end if

       ' msgbox "hi1"
    End With

    If obj_fso.fileExists("\\\mahesh_scan\Mail\BrkFile\"&trim(rs("file_name")) ) then
str="Update broker_july set  senton=getdate(),  sent=1 where  id="&rs("id")
   end if


       msgbox "Send"
Updated 22-Oct-21 0:41am

1 solution

The equivalent for the ADO.Connection and ADO.Recordset is to use

A Beginner's Tutorial for Understanding ADO.NET[^]

Rather than filesystemobject, to check if a file exists use File.Exists[^]

Rather than CDO, to send an email message use System.Net.Mail.MailMessage[^]

Now just re-write the script using the components above.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900