Automate email w/ attachment in Macro (module) using wscript / vbscript

Tue, 07 Jul 2009 22:02:15 GMT

Using QV 8.5, I am trying to send out an email with an attachment from a macro in a .qvw application (ReportGenerator.qvw). (see vbscript below)

ReportGenerator.qvw currently creates and saves an Excel file using an 'OnOpen' macro, which is initiated automatically via an External Program Task in Publisher (essentially a batch file: qv.exe /NoSecurity \\Path\ReportGenerator.qvw).

How can I include the appopriate microsoft libraries (as needed) to execute the following wscript in that same macro in the ReportGenerator.qvw?

----------------- Script to send email --------------------------

'This script will generate an email with a file attachment. Four Arguments must be included in
'the execution string:
' Argument 0 - To email address or distribution list. Multiple addys can be separated with a comma (,)
' Argument 1 - Subject (enclosed in triple double quotes -no I'm not kidding- if there are spaces in the Subject)
' """Your Report is Attached"""
' Argument 2 - Body of the email (again, enclosed in triple double quotes)
' Argument 3 - The full path of the attachment
'
'Example Execution String (minus the beginning tick ('):
'wscript E:\...\Email_With_Attachment.vbs name@domain.com """Subject of Email""" """Body of Email""" \\Path\Report.xls

Set args=wscript.Arguments

varTo = args(0)
varSubject = args(1)
varTextBody = args(2)
varAttachment = args(3)

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") = "smtp...." <<<< Insert SMTP server here
ObjSendMail.Configuration.Fields.Update

ObjSendMail.To = varTo
ObjSendMail.Subject = varSubject
ObjSendMail.From = "name@domain.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

'In addition to what you see here there are plenty of properties you can add to these examples.
'Here are a few examples.

'Carbon Copy
'ObjSendMail.CC = "someone@someone.net"

'Blind Carbon Copy
'ObjSendMail.BCC = "someone@someone.net"

Automate email w/ attachment in Macro (module) using wscript / vbscript

stephencredmond — Tue, 07 Jul 2009 22:17:22 GMT

Hi,

As I understand it, the CDO libraries are server based - they need to run on a server.

On a client, you would have to get a COM based SMTP control to call from VBS to send emails. You can't do it through Outlook anymore because it will pop up the security requestor to confirm that the external application is allowed send emails.

Stephen

Automate email w/ attachment in Macro (module) using wscript / vbscript

Thu, 16 Jul 2009 04:21:37 GMT

Eugene had a great, complete response here: http://community.qlik.com/forums/p/16563/70442.aspx#70442

I did some samurai coding with his help, and it worked.

topic Automate email w/ attachment in Macro (module) using wscript / vbscript in QlikView

Automate email w/ attachment in Macro (module) using wscript / vbscript

Automate email w/ attachment in Macro (module) using wscript / vbscript

Automate email w/ attachment in Macro (module) using wscript / vbscript