9 Replies Latest reply: Jan 17, 2018 5:40 AM by Marcus Sommer RSS

    Macro stop after sending emails of some zones.

    anil mishra

      Dear All ,

      I have a Mail_Alert.QVW  from which we send emails to the customers of East,West,North & South Zones.

      After sending mails of all zones document saved & quit but some times after sending mails of East "E" Zone or South "S" zone or North "N" Zone or West "W" Zone it stops & goes in edit module mode.

       

      I am pesting here the complete code of macro ...Please help me....

       

      '******************************** EMAIL FUNCTIONS *********************************

       

       

       

       

      function sendCDOMail()

        'ActiveDocument.Fields("ZONE").Clear

       

        'set fld=ActiveDocument.GetField("PARENT_ID")

       

        'msgbox("The field has "& fld.GetValueCount(2) &" selected values")

        'fld.GetValueCount(2)-1

      Const cdoBasic = 1 'basic (clear-text) authentication

      Call deleteReports

       

        Set z = ActiveDocument.Fields("ZONE").GetPossibleValues

       

        For j=0 to z.Count-1

        set k = ActiveDocument.Fields("ZONE")

        set kv = k.GetSelectedValues

        if kv.Count<>0 then

       

        kv.RemoveAt 0

       

        End if

        ActiveDocument.Fields("EMAIL").Clear

        ActiveDocument.Fields("PARENT_ID").Clear

       

        ActiveDocument.Fields("ZONE").Select z(j).Text

       

        ActiveDocument.Fields("EMAIL").Clear

        ActiveDocument.Fields("PARENT_ID").Clear

       

        Set Temp = ActiveDocument.Fields("PARENT_ID").GetPossibleValues

       

       

        for i=0 to Temp.Count-1

       

        Dim objEmail

        Dim strMailTo

        'Creat New Message

        Set objEmail = CreateObject("CDO.Message")

        Set objConf = objEmail.Configuration

        Set objFlds = objConf.Fields

        With objFlds

        '---------------------------------------------------------------------

          ' SMTP server details

          .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

          .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.office365.com"

          .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

          .Item("http://schemas.microsoft.com/cdo/configuration/sendusername")="customercare@bilt.com"

          .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword")="cust0mer@c@re"

          .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

          .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60

          .Update

        '---------------------------------------------------------------------

        End With

       

        set f = ActiveDocument.Fields("PARENT_ID")

        set fv = f.GetSelectedValues

        if fv.Count<>0 then

        fv.RemoveAt 0

        End if

        ActiveDocument.Fields("PARENT_ID").Select Temp(i).Text

        set Email=ActiveDocument.Fields("EMAIL").GetPossibleValues

        set obj = ActiveDocument.GetSheetObject("CH01")

        set Parent_name=ActiveDocument.Fields("PARENT_NAME").GetPossibleValues

        obj.ExportBiff "D:\MAIL ALERTS\Opti_Customer_Invoice_Mail_Alert\" & Parent_name.Item(0).Text &"-"& Temp(i).Text & ".xls"

        set mail=ActiveDocument.Fields("MAIL").GetPossibleValues

        set DT=ActiveDocument.Fields("PROCESS_DATE").GetPossibleValues

        set V = ActiveDocument.Fields("Valid").GetPossibleValues

        set BG = ActiveDocument.Fields("BILLING_GROUP_ID").GetPossibleValues

       

        objEmail.From = "customercare@bilt.com"

        objEmail.Sender = "customercare@bilt.com"

        if V(0).Text="Y" then

        objEmail.To =  Email(0).Text

        objEmail.Cc = mail.Item(0).Text

        objEmail.BCc ="sunil.rajpal@bilt.com;prakash.mahapatro@bilt.com;anil.mishra@bilt.com"

        objEmail.Subject = "Invoice Details for the Date of " & DT.Item(0).Text & " to " & Temp(i).Text &"-"&Parent_name.Item(0).Text

        IF BG(0).TEXT="02" OR BG(0).TEXT="12" OR BG(0).TEXT="07" OR BG(0).TEXT="17" THEN

        objEmail.HTMLBody = "Invoice Details for the Date of " & DT.Item(0).Text & " to " & Temp(i).Text &"-"&Parent_name.Item(0).Text  & vbCrLf &"<HTML><BR><BR> <font Size=1>******** This is an auto-generated mail. Please do not reply to this email id. The property in goods pass on to buyer on invoicing and our risks & responsibilities ceases there on For any clarifications or queries, please contact your concerned RSO.*****</Font></HTML>"

        ELSE

        objEmail.HTMLBody = "Invoice Details for the Date of " & DT.Item(0).Text & " to " & Temp(i).Text &"-"&Parent_name.Item(0).Text  & vbCrLf &"<HTML><BR><BR> ******** This is an auto-generated mail. Please do not reply to this email id. For any clarifications or queries, please contact your concerned RSO.  *****</HTML>"

        end if

        objEmail.AddAttachment "D:\MAIL ALERTS\Opti_Customer_Invoice_Mail_Alert\" & Parent_name.Item(0).Text &"-"& Temp(i).Text & ".xls"

        objEmail.Send

        else

        '******************* If E-Mail ID Not Valid Executes Mail To Below ID's *******************

        objEmail.To =  mail.Item(0).Text

        objEmail.Subject = "The Customer : " & Temp(i).Text &"-"&Parent_name.Item(0).Text & " E - Mail Id is Invalid. Please Correct it."

        objEmail.TextBody = "Invoice Details for the Date of " & DT.Item(0).Text & " to " & Temp(i).Text &"-"&Parent_name.Item(0).Text

        objEmail.AddAttachment "D:\MAIL ALERTS\Opti_Customer_Invoice_Mail_Alert\" & Parent_name.Item(0).Text &"-"& Temp(i).Text & ".xls"

        objEmail.Send

        End if

        Set objFlds = Nothing

        Set objConf = Nothing

        Set objEmail = Nothing

        next

      Next

      ActiveDocument.Fields("ZONE").Clear

      ActiveDocument.Fields("EMAIL").Clear

      ActiveDocument.Fields("PARENT_ID").Clear

      ActiveDocument.Save

      ActiveDocument.GetApplication.Quit

      end function

       

       

       

       

      '***************************** END OF EMAIL SECTION ***************************************

       

       

      function deleteReports()

        Set FSOObj = CreateObject("scripting.FileSystemObject")

        IF NOT FSOObj.FolderExists("D:\MAIL ALERTS\Opti_Customer_Invoice_Mail_Alert") then

         FSOObj.CreateFolder("D:\MAIL ALERTS\Opti_Customer_Invoice_Mail_Alert")

        Else

          FSOObj.DeleteFile ("D:\MAIL ALERTS\Opti_Customer_Invoice_Mail_Alert\*.*"), True

        end if

      end function

       

       

      '***************************** Function For Validating E-Mail Address **************************

        • Re: Macro stop after sending emails of some zones.
          Marcus Sommer

          There are multiple reasons possible. For example an invalid mail-address, a missing attachment, errors with the network/storage, too high usage of CPU + RAM and probably many more.

           

          Helpful are often WaitForIdle statements after activating sheets/objects or selecting any values in Qlik. Also sleep statements after a save-statement. Further checking-routines if the mail-adresses are valid, the attachments available und similar things. If this didn't help you will need to add some trace/logging-steps to find the breaking-points and when it happens to find any pattern for the failures.

           

          - Marcus

            • Re: Macro stop after sending emails of some zones.
              anil mishra

              Dear Mr. Marcus.

              Special thanks for immediate response.

              Please explain clearly that where I have to use WaitForIdle statements & how?

              Please mention sleep statements with examples.

              How can I follow trace/logging-steps to find the breaking-points ?

               

              With regards,

              AKM

                • Re: Macro stop after sending emails of some zones.
                  Marcus Sommer

                  - WaitForIdle statements after activating sheets/objects or selecting any values

                     ActiveDocument.WaitForIdle

                   

                  - sleep statements after a actions which export, save, move, copy files within the filesystem or copying, writing in other tools or calling other tools like the CDO or another mailing-tool

                    ActiveDocument.GetApplication.Sleep 1000

                   

                  - tracing meant to use msgbox to return the variable-values and the loop-counter

                   

                  - a logging could be done in a excel- or a text-file with routines like this: Re: Load data from multiple sheets of excel file(dynamic sheet count)

                   

                  - Marcus

                    • Re: Macro stop after sending emails of some zones.
                      anil mishra

                      Again special thanks for proper response but I am unable to understand that in the pasted macro code where I can put the "ActiveDocument.WaitForIdle" or "ActiveDocument.GetApplication.Sleep 1000" & what will be the benefits.

                       

                      My target is to send the complete emails of all Zones without any halt in the execution of macro.

                       

                      I have checked & found that pasted macro code is OK & it executes fine but some times macro execution stop in the middle.

                       

                      I have to find the main root cause that why execution is stopping some times.

                       

                      Please help me step by step that how can find the root cause & where I have to make change in the pasted code.

                       

                      Regards,

                      AKM

                        • Re: Macro stop after sending emails of some zones.
                          Marcus Sommer

                          Examples ...

                           

                          ...

                          ActiveDocument.Fields("PARENT_ID").Select Temp(i).Text

                          ActiveDocument.WaitForIdle

                          ...

                          objEmail.AddAttachment "D:\MAIL ALERTS\Opti_Customer_Invoice_Mail_Alert\" & Parent_name.Item(0).Text &"-"& Temp(i).Text & ".xls"

                          ActiveDocument.GetApplication.Sleep 1000

                          ...

                          FSOObj.DeleteFile ("D:\MAIL ALERTS\Opti_Customer_Invoice_Mail_Alert\*.*"), True

                          ActiveDocument.GetApplication.Sleep 1000

                          ...

                           

                          These methods have the aim to ensure that the following step didn't starts before the previous step has finished. That your code sometimes runs proved that it in general worked but if your server/machine/storage is busy with whatever it might delay some steps ... and could result in random failures.

                           

                          - Marcus

                            • Re: Macro stop after sending emails of some zones.
                              anil mishra

                              Dear Mr. Marcus.

                              A lot of thanks to reply.

                              But..

                              I have to inform you that I have implemented as mentioned by you but when I use ..

                               

                              ActiveDocument.Fields("PARENT_ID").Select Temp(i).Text

                              ActiveDocument.WaitForIdle


                              then macro does not execute and its control go to the edit module mode.


                              And after removing this condition when I use only SLEEP..


                              objEmail.AddAttachment "D:\MAIL ALERTS\Opti_Customer_Invoice_Mail_Alert\" & Parent_name.Item(0).Text &"-"& Temp(i).Text & ".xls"

                              ActiveDocument.GetApplication.Sleep 1000

                              ...

                              FSOObj.DeleteFile ("D:\MAIL ALERTS\Opti_Customer_Invoice_Mail_Alert\*.*"), True

                              ActiveDocument.GetApplication.Sleep 1000


                              then it sop sending emails after sending some emails as mentioned in my earlier question.


                              I know very well that you can finally help me.


                              Please help & provide me permanent solution . I will never forget yours help.


                              Again special thanks & regards,

                              AKM

                                • Re: Macro stop after sending emails of some zones.
                                  Marcus Sommer

                                  This statement wasn't quite correct from a syntax point of view:

                                   

                                  ActiveDocument.WaitForIdle

                                   

                                  because it must be:

                                   

                                  ActiveDocument.GetApplication.WaitForIdle

                                   

                                  By the sleep-statement you might need to increase the waiting time from 1 second (= 1000 milli-seconds) to 3 - 4 seonds. It depends to the kind of file-operation and the resources of your network/storage. I remember that within our first automatic reporting I must set this value to 4 seconds to ensure that the failure-rate related to the file-operations was lesser than about 2%.

                                   

                                  Like above mentioned you might further need various checking-routines - this meant to implement an error-handling which didn't break the routine by an error else repeated it n-times and/or may call some workarounds and giving an error-message to what went wrong. Unfortunately the error-handling within the Qlik vbs-implementation is limited to just On Error Resume Next + On Error Goto 0 but nevertheless a working error-handling could be implemented. For a starting point see here: On Error Statement.

                                   

                                  Are after such measures yet too many failures you will need to apply some kind of logging like mentioned in the link above to get a timestamp when the error occured and which could be used to find any causes/pattern within the windows event-logs.

                                   

                                  - Marcus

                                    • Re: Macro stop after sending emails of some zones.
                                      anil mishra

                                      Dear Mr. Marcus,

                                      Thanks to reply.

                                       

                                      I have implemented as mentioned by you & also increased the sleep time to 6000 milli seconds but notwithstanding it fails some time as mentioned in earlier.

                                       

                                      Please suggest &  provide example so that it can be implemented.

                                       

                                      Regards,

                                      AKM

                                        • Re: Macro stop after sending emails of some zones.
                                          Marcus Sommer

                                          I think the problem is outside of QlikView and VBS by blocking/conflicting the resources by any other process. The range of potential processes could be quite wide, for example the service "windows shadow copies" but also blocking from your security suite or mail-server (too many similar mails in a certain time-frame are in general suspicious). For this you could take a look in the event-logs, security-logs and also ask your IT guys if there are any such settings.

                                           

                                          Quite helpful for this would be to implement like above mentioned an error-handling and a logging - you have already some quite helpful starting points for it within the above provided links. But I couldn't develop it for you - the forum is for help and guidance but not to do the others work.

                                           

                                          - Marcus