Email macro doesn't work in Windows task manager - Qlik Community

Anonymous · ‎2017-06-20

I took pieces of other macros I found to create this one that sends to two groups of email addresses set as variables aRecipientsTo and aRecipientsCC. Apparently when it's run, it works through the Windows batch file, but not through the Windows task manager. Not sure if it's a problem with my script or how the task manager runs? This is not my area of expertise so I have no idea what the culprit could be. I pasted the code below and attached it in a .txt as well. Any help or ideas of what could be causing it would be greatly appreciated!

'---begin script 2

Sub GenerateMailReport

'First we are reading from variable the path to a folder where Excel file will be saved:

set v = ActiveDocument.Variables("vPath")

vPath = v.GetContent.String

'Initialize Excel object:

set XLApp = CreateObject("Excel.Application")

'below command hides Excel window. In other words you will not see Excel application showing up, everything will be done in the background

'If you want to see what Excel is doing (during testing phase for example), change it from false to true.

XLApp.Visible = false

'Add a Workbook to Excel file:

set XLDoc = XLApp.Workbooks.Add

'Clear all filters (you may not need this line, remove it if you have pre-defined filters selection in report

'In future posts I will show you how to select filters via macro.

' ActiveDocument.ClearAll True

'Here we are telling Excel into which Worksheet we will paste QV data:

set XLSheet = XLDoc.Worksheets(1)

XLSheet.Activate

'Tell QV which object to copy from (in our case it is a Chart object, which ID = "FOR_SCHEDULE":

set table=ActiveDocument.GetSheetObject("CH23")

'Copy content of this object:

table.CopyTableToClipboard true

'And paste into Excel

XLSheet.Paste

' Cells.WrapText = False

XLApp.Worksheets(XLApp.ActiveSheet.Index).Columns(1).ColumnWidth = 27

XLApp.Worksheets(XLApp.ActiveSheet.Index).Columns(2).ColumnWidth = 100

XLApp.Worksheets(XLApp.ActiveSheet.Index).Columns(3).ColumnWidth = 20

XLApp.Worksheets(XLApp.ActiveSheet.Index).Columns(4).ColumnWidth = 10

XLApp.Worksheets(XLApp.ActiveSheet.Index).Cells.EntireRow.RowHeight = 15

' xlApp.Worksheets(xlApp.ActiveSheet.Index).Cells.EntireRow.AutoFit

'Let's also change Sheet's name to something more fancy:

XLSheet.Name = "Content Completed"

'And select A1 cell, just so that it looks better when opened:

XLSheet.Range("A1").Select

'Now me must save report to previously set destination folder,

XLDoc.SaveAs vPath

'Close Workbook

XLDoc.close

'And finally close whole Excel file

XLApp.Quit

End Sub

'==============================================================

'============== Helper functions defined below ===============

'==============================================================

'Read variable

Public function getVariable(var)

set v = ActiveDocument.Variables(var)

getVariable = v.GetContent.String

end function

'Delete file

Public Function DeleteFile(vfile)

Set obj = CreateObject("Scripting.FileSystemObject") 'Calls the File System Object

obj.DeleteFile(vfile) 'Deletes the file.

End Function

'---end script 2

Sub ExportEmail

ActiveDocument.GetApplication.WaitForIdle

ActiveDocument.Sheets("SH01").Activate

ActiveDocument.GetApplication.WaitForIdle

'ActiveDocument.GetApplication.WaitForIdle 150000

Set obj = ActiveDocument.ActiveSheet.SheetObjects("CH16")

ActiveDocument.GetApplication.WaitForIdle

obj.ExportBitmapToFile "C:\ProgramData\QlikTech\Documents\07ExcelExport\marketing_summary.jpg"

Set obj = ActiveDocument.ActiveSheet.SheetObjects("CH25")

ActiveDocument.GetApplication.WaitForIdle

obj.ExportBitmapToFile "C:\ProgramData\QlikTech\Documents\07ExcelExport\ulds_by_week.jpg"

Set obj = ActiveDocument.ActiveSheet.SheetObjects("CH49")

ActiveDocument.GetApplication.WaitForIdle

obj.ExportBitmapToFile "C:\ProgramData\QlikTech\Documents\07ExcelExport\content_summary.jpg"

Set obj = ActiveDocument.ActiveSheet.SheetObjects("CH21")

ActiveDocument.GetApplication.WaitForIdle

obj.ExportBitmapToFile "C:\ProgramData\QlikTech\Documents\07ExcelExport\content_by_week.jpg"

Dim objEmail

Const cdoSendUsingPort = 2

Const cdoAnonymous = 0

Const cdoBasic = 1

Const cdoNTLM = 2

Const SMTPServer = "aspmx.l.google.com"

Const SMTPPort = 25

Const SMTPTimeout = 60

Set objEmail = CreateObject("CDO.Message")

Set objConf = objEmail.Configuration

Set objFlds = objConf.Fields

With objFlds

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

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

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

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

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

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

.Update

End With

objEmail.To = ActiveDocument.Evaluate("=aRecipientsTo")

objEmail.CC = ActiveDocument.Evaluate("=aRecipientsCC")

objEmail.From = "qlikview@companyname.com"

objEmail.Subject = "Content Marketing Report: Week "& ActiveDocument.Evaluate("=vLastWeek") & " | " & ActiveDocument.Evaluate("=vLastWeekDate") & " - " & ActiveDocument.Evaluate("=vLastWeekDateEnd")

HTML = "<!DOCTYPE HTML PUBLIC ""-//IETF//DTD HTML//EN"">" & chr(13) & chr(10)

HTML = HTML & "<html>"

HTML = HTML & "<head>"

HTML = HTML & "<meta http-equiv=""Content-Marketing"" content=""text/html; charset=iso-8859-1"">"

HTML = HTML & "<title>Automated Email - Content Marketing</title>"

HTML = HTML & "</head>"

HTML = HTML & "<body style=""margin:0;font-family:Helvetica, Arial, sans-serif;font-size:14px;color:#000000 align =""center"">"

'centered link to dashboard:

HTML = HTML & "<table width=""700"" border=""0"" cellspacing=""0"" cellpadding=""0"">"

HTML = HTML & "<tr>"

HTML = HTML & "<td align = ""center"" style=""font-family:Helvetica, Arial, sans-serif;font-size:14px;color:#000000""><a href=""https://reports.collegedegrees.com/QvAJAXZfc/opendoc.htm?document=04accesspoint_marketing%5Ccontent%..."" style=""font-family:Helvetica, Arial, sans-serif;font-size:18px;color:#3b9cd6;text-decoration:none"">Click here to visit the Content Marketing Dashboard</a></td>"

HTML = HTML & "</tr>"

HTML = HTML & "<tr>"

HTML = HTML & "<td> </td>"

HTML = HTML & "</tr>"

'marketing summary

HTML = HTML & "<tr>"

HTML = HTML & "<td align=""center""><span style=""font-family:Helvetica, Arial, sans-serif;font-weight:bold;font-size:18px;color:#000000"">Marketing Summary YTD </span></td>"

HTML = HTML & "</tr>"

HTML = HTML & "<tr>"

HTML = HTML & "<td align = ""center""><img src=""cid:marketing_summary.jpg"" border=""0"" style=""display:block"" alt=""Marketing Summary YTD"" title=""Marketing Summary YTD""></td>"

HTML = HTML & "</tr>"

HTML = HTML & "<tr>"

HTML = HTML & "<td> </td>"

HTML = HTML & "</tr>"

'ulds by week

HTML = HTML & "<tr>"

HTML = HTML & "<td align=""center""><span style=""font-family:Helvetica, Arial, sans-serif;font-weight:bold;font-size:18px;color:#000000"">ULDs Earned by Week </span></td>"

HTML = HTML & "</tr>"

HTML = HTML & "<tr>"

HTML = HTML & "<td align = ""center""><img src=""cid:ulds_by_week.jpg"" border=""0"" style=""display:block"" alt=""ULDs by Week"" title=""ULDs by Week""></td>"

HTML = HTML & "</tr>"

HTML = HTML & "<tr>"

HTML = HTML & "<td> </td>"

HTML = HTML & "</tr>"

HTML = HTML & "<tr>"

HTML = HTML & "<td> </td>"

HTML = HTML & "</tr>"

'content summary

HTML = HTML & "<tr>"

HTML = HTML & "<td align=""center""><span style=""font-family:Helvetica, Arial, sans-serif;font-weight:bold;font-size:18px;color:#000000"">Content Summary YTD </span></td>"

HTML = HTML & "</tr>"

HTML = HTML & "<tr>"

HTML = HTML & "<td align=""center""><img src=""cid:content_summary.jpg"" border=""0"" style=""display:block"" alt=""Content Summary"" title=""Content Summary""></td>"

HTML = HTML & "</tr>"

HTML = HTML & "<tr>"

HTML = HTML & "<td> </td>"

HTML = HTML & "</tr>"

'content by week

HTML = HTML & "<tr>"

HTML = HTML & "<td align=""center""><span style=""font-family:Helvetica, Arial, sans-serif;font-weight:bold;font-size:18px;color:#000000"">Content Completed by Week </span></td>"

HTML = HTML & "</tr>"

HTML = HTML & "<tr>"

HTML = HTML & "<td align = ""center""><img src=""cid:content_by_week.jpg"" border=""0"" style=""display:block"" alt=""Content by Week"" title=""Content by Week""></td>"

HTML = HTML & "</tr>"

HTML = HTML & "</table>"

HTML = HTML & "</body>"

HTML = HTML & "</html>"

Set objBP = objEmail.AddRelatedBodyPart("C:\ProgramData\QlikTech\Documents\07ExcelExport\marketing_summary.jpg", "marketing_summary.jpg", CdoReferenceTypeName)

objBP.Fields.Item("urn:schemas:mailheader:Content-ID") = "<marketing_summary.jpg>"

Set objBP = objEmail.AddRelatedBodyPart("C:\ProgramData\QlikTech\Documents\07ExcelExport\ulds_by_week.jpg", "ulds_by_week.jpg", CdoReferenceTypeName)

objBP.Fields.Item("urn:schemas:mailheader:Content-ID") = "<ulds_by_week.jpg>"

Set objBP = objEmail.AddRelatedBodyPart("C:\ProgramData\QlikTech\Documents\07ExcelExport\content_summary.jpg", "content_summary.jpg", CdoReferenceTypeName)

objBP.Fields.Item("urn:schemas:mailheader:Content-ID") = "<content_summary.jpg>"

Set objBP = objEmail.AddRelatedBodyPart("C:\ProgramData\QlikTech\Documents\07ExcelExport\content_by_week.jpg", "content_by_week.jpg", CdoReferenceTypeName)

objBP.Fields.Item("urn:schemas:mailheader:Content-ID") = "<content_by_week.jpg>"

objBP.Fields.Update

'End If

objEmail.HTMLBody = HTML

objEmail.AddAttachment getVariable("vPath")

objEmail.Send

Set objFlds = Nothing

Set objConf = Nothing

Set objEmail = Nothing

ActiveDocument.Sheets("SH01").Activate

ActiveDocument.GetApplication.WaitForIdle

ActiveDocument.Save

ActiveDocument.GetApplication.Quit

End Sub