6 Replies Latest reply: Jan 7, 2013 8:12 AM by Jose Tos RSS

    Sending mails macro vbs

    Jose Tos

      I have the next issue:

       

      I'm able to send mails with a macro using customer smtp server through Qlikview Desktop, I have a windows task to reload my document and a trigger that executes my macro after reload, I know this works because part of my macro exports to excel a few tables, the problem is sending mails part of the macro, that doesn´t work and I don't know why, the user that executes the task has mail account of the customer SMTP server.

       

      I don´t know why is working fine through Qlikview Desktop and  not working through windows task....

       

      Any help, please??

        • Re: Sending mails macro vbs
          Marcus Sommer

          I have the same problem had. Problem seems to be that that CDO.Message requires run with priority and not as background process. Change the task parameter from /r  to  /l in your batch file.

           

          REM /r = open+reload+save+close as background process  /l = open+reload+save with GUI

          "D:\QlikView_Client\QV.exe" /l "D:\YOURFILE.qvw"

           

          Marcus

            • Re: Sending mails macro vbs
              Jose Tos

              Hi Marcus, I've tried your solution but the task doesn´t finish ...

               

              /r = open+reload+save+close

              /l = open+reload+save     and close??? Could be that the problem??

               

              Thank you      

                • Re: Sending mails macro vbs
                  Marcus Sommer

                  I make finish after save+export+send in the end of send-routine per

                   

                  ActiveDocument.closedoc

                  ActiveDocument.GetApplication.Quit

                   

                  Send mails works now?

                   

                  Marcus

                    • Re: Sending mails macro vbs
                      Jose Tos

                      I don´t think so because the task remain in execution indefinitely ...

                      I'm waiting feedback of my customer, I have no access to the mail account.

                       

                      This is my code:

                       

                      sub ExportarAgendas

                       

                      ' ///////////////////export code //////////////'

                      ....

                      '/////////// end export code////////////////'

                       

                      ' Below mailing code

                       

                      Set objMsg = CreateObject("CDO.Message")

                      Set msgConf = CreateObject("CDO.Configuration")

                       

                       

                      ' Server Configuration

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

                      msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "servername"

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

                      msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 0

                      msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = ""

                      msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = ""

                      msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = 0

                      msgConf.Fields.Update

                       

                       

                      MailToVar = ActiveDocument.Variables("To").GetContent.string

                      objMsg.To = MailToVar 

                      MailFromVar= ActiveDocument.Variables("From").GetContent. String

                      objMsg.From =  MailFromVar

                       

                       

                      objMsg.Subject = "blablablabla"

                      objMsg.HTMLBody = "blablablabla"

                      objMsg.Sender = " "

                      objMsg.AddAttachment "Path\Document.xls"

                      objMsg.AddAttachment "Path\Document.xls"

                      objMsg.AddAttachment "Path\Document.xls"

                      Set objMsg.Configuration = msgConf

                       

                       

                      ' Send

                      objMsg.Send

                       

                       

                      'msgbox "enviado"

                      '

                      Set objMsg = nothing

                      Set msgConf = nothing

                       

                       

                      ActiveDocument.closedoc

                      ActiveDocument.GetApplication.Quit

                       

                      end sub

                        • Re: Sending mails macro vbs
                          Marcus Sommer

                          I use this code for sending mails an I use this not from the data application, but from a control application, which run per loop through a lot of data applications and execute various reloads+export+send.

                           

                          ActiveDocument.closedoc (original newdoc.closedoc) --> is here wrong, then it made to closed the data application and without ActiveDocument the quit-order will failed

                           

                           

                          Set objEmail = CreateObject("CDO.Message") 'Sending mail

                           

                          'This section provides the configuration information for the remote SMTP server.

                          config = "http://schemas.microsoft.com/cdo/configuration/"

                          objEmail.Configuration.Fields.Item(config & "sendusing") = 2 'http://msdn.microsoft.com/en-us/library/exchange/ms873037%28v=exchg.65%29.aspx

                          objEmail.Configuration.Fields.Item(config & "smtpserver") = " smtpserver " 'Name or IP of Remote SMTP Server

                          'objEmail.Configuration.Fields.Item (config & "smtpauthenticate") = 1 'Clear Text - Type of authentication, NONE, Basic (Base64 encoded), NTLM

                          'objEmail.Configuration.Fields.Item(config & "sendusername") = "youruserid" 'Your UserID on the SMTP server

                          'objEmail.Configuration.Fields.Item(config & "sendpassword") = "yourpassword" 'Your password on the SMTP server

                          objEmail.Configuration.Fields.Item(config & "smtpserverport") = 25 'Server port (typically 25)

                          objEmail.Configuration.Fields.Item(config & "smtpusessl") = False 'Use SSL for the connection (False or True)

                          objEmail.Configuration.Fields.Item(config & "smtpconnectiontimeout") = 60 'Connection Timeout in seconds (the maximum time CDO will try to establish a connection to the SMTP server)

                          objEmail.Configuration.Fields.Update

                          'End remote SMTP server configuration section                           

                                                              

                          objEmail.From = "me@me.com"

                          objEmail.To = " me@me.com "

                          objEmail.Subject = "xxxx"

                          objEmail.TextBody = "xxx"

                          objEmail.AddAttachment Path&File

                          objEmail.Send

                           

                          Set objEmail = Nothing

                          ActiveDocument.GetApplication.Quit

                           

                           

                          Marcus

                            • Re: Sending mails macro vbs
                              Jose Tos

                              Hi Marcus, thanks for your help, it was very usefull.

                               

                              Finally, I could send mails, but the reason why I couldn´t was because the execution in background resets security of the module, so I needed to allow access to the system in module security and local security.

                              If you don´t allow access in modul security, local security is reset to safe mode, but if you do, local security is saved.

                               

                              Regards and thank you!