topic Re: Sending mails macro vbs in QlikView

Sending mails macro vbs

chematos — Thu, 03 Jan 2013 12:56:16 GMT

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 — Thu, 03 Jan 2013 15:18:24 GMT

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

chematos — Thu, 03 Jan 2013 15:46:15 GMT

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 — Thu, 03 Jan 2013 16:01:41 GMT

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

chematos — Thu, 03 Jan 2013 17:03:04 GMT

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"

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 — Fri, 04 Jan 2013 08:28:48 GMT

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

chematos — Mon, 07 Jan 2013 13:12:52 GMT

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!