topic Re: Sending Email via VBScript and batch script in QlikView

Sending Email via VBScript and batch script

chris_johnson — Fri, 13 May 2011 10:54:17 GMT

Hi,

I'm trying to figure out if it is possible to do a reload of a qvw file from a batch script and then, using a module on the report, output a straight table to a file and send it via email.

I've managed to create the batch script to reload the report and export the straight table no problem. The issue is the emailing part and trying to get it to work in combination with the batch script.

For some reason, if I reload the report with it open (just from the reload button) it works and the module sends the email. If I close the report and run the batch script then the report reloads (I see the progress pop-up box) but I don't get the email and the command prompt stays open. Below is the VBScript I'm using (by the way, I've been chopping it from various different web sites and posts here so may be a mess!):

sub testy()

dim theDate

dim filename

if len(CStr(Month(Date()))) = 1 then

theDate = CStr(Day(Date())) & "0" & CStr(Month(Date())) & CStr(Year(Date()))

else

theDate = CStr(Day(Date())) & CStr(Month(Date())) & CStr(Year(Date()))

end if

filename = "C:\Users\chris_johnson\Documents\Test " &theDate& ".xls"

call ExportToExcel(filename)

end sub

sub ExportToExcel (filename)

dim o

'dim theDate

'dim filename

Set o = ActiveDocument.GetSheetObject("LA01")

'theDate = CStr(Date())

'filename = "C:\Users\chris_johnson\Documents\Test " &theDate& ".xls"

o.ExportBiff filename

set o = Nothing

call SendEmail(filename)

end sub

sub SendEmail(attachment)

varTo = "myemail@company.com"

varSubject = "Report"

varTextBody = "Please find report attached"

varAttachment = attachment

Dim ObjSendMail

Set ObjSendMail = CreateObject("CDO.Message")

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

ObjSendMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'Send the message using the network (SMTP over the network).

ObjSendMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtpserv"

ObjSendMail.Configuration.Fields.Update

ObjSendMail.To = varTo

ObjSendMail.Subject = varSubject

ObjSendMail.From = "myemail@company.com"

' we are sending a text email.. simply switch the comments around to send an html email instead

'ObjSendMail.HTMLBody = "this is the body"

ObjSendMail.TextBody = varTextBody

ObjSendMail.AddAttachment varAttachment

ObjSendMail.Send

Set ObjSendMail = Nothing

end sub

Can someone help me with trying to understand if (1) this is the right way to go about this and (2) how to change this script in order to get it to work with emailing from batch script.

I've had half a thought that maybe I should remove the emailing part from the VBScript and possibly put it into the batch script.

Thanks,

Chris

Sending Email via VBScript and batch script

chris_johnson — Fri, 13 May 2011 12:41:51 GMT

Ok, so I've ripped a load of the VBScript out to create the email and put it in it's own little .vbs file.

All that's left is a call to the function:

dim wscript

dim sendmail

set sendmail = "C:\Users\chris_johnson\Documents\SendMail.vbs"

set wscript = CreateObject("WScript.Shell")

wscript sendmail myemail@company.com """New Report""" """Hope this works""" filename

Unfortunately, when I 'Check' the code using the module editor it tells me that it expected the end of the statement after the sendmail. I'm not sure what it is that I need to change...

Chris

Sending Email via VBScript and batch script

chris_johnson — Fri, 13 May 2011 13:12:13 GMT

Slightly further along again. I have changed things around and managed to successfully 'Check' the VBScript:

dim wscript

dim sendmail

set sendmail = "C:\Users\chris_johnson\Documents\SendMail.vbs"

set wscript = CreateObject("WScript.Shell")

wscript sendmail &" "& "johnson@uk.belfor.com" &" "& """New Report""" &" "& """Hope this works""" &" "& filename

When I click the 'Test' button however, it errors on the line 'set wscript = "C:\Users...'

The error it gives me is:

Object required: '[string: "C:\Users\chris_johns"]'

Any ideas?

Chris

Sending Email via VBScript and batch script

chris_johnson — Fri, 13 May 2011 13:15:31 GMT

Ah, so if I change it to (removed one of the 'set' commands):

dim wscript

dim sendmail

sendmail = "C:\Users\chris_johnson\Documents\SendMail.vbs"

set wscript = CreateObject("WScript.Shell")

wscript sendmail &" "& "johnson@uk.belfor.com" &" "& """New Report""" &" "& """Hope this works""" &" "& filename

Now I get:

Object doesn't support this property or method: 'wscript'

Hmph

Re: Sending Email via VBScript and batch script

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

Take a look to this thread:

http://community.qlik.com/message/116035#116035