Solved: Re: Qlikview Macro Array Field Select - Qlik Community

Report Inappropriate Content · ‎2016-03-10

Good day Guys

Hope you are doing good, I am battling with getting my Qlikview Macro to work, I have already tried and researched and tried again, really hoping some one can point me in the right direction.

I have a model, with a rating system for dealers, I have attached it to this post.

I am running a few Macro's but two is important, and related to my question.

1. I run a macro to print a pdf report based on certain selection/criteria, this then saves the report to my local pc. This part works, each file is also uniquely named, as per the "unique" field on the ("DealerRating") table. This part is working.

2. My second macro should now go and email, each unique pdf to their related email address on the same table, ("DealerRating")

This is where I battle.

So below I start a loop which selects for each unique value on field "Unique",

I want to at this time declare another variable called "Email" which should contain the selected rows "Email" field value.

As with my actual record set there is 1000's of records, and hard coding is not a option, please help.

My Code so far:

Set val=ActiveDocument.Fields("Unique").GetPossibleValues(20000)
For i = 0 to val.count-1
set Unique = val.Item(i)

ActiveDocument.Fields("Unique").Select Unique.text

set Email = ActiveDocument.Fields("Unique").GetPossibleValues(20000)

objMsg.To = Email ' type to mail id
objMsg.From = "This is the from email" 'type from mail id
objMsg.Subject = "Dealer Rating Reports"
objMsg.HTMLBody = "<html><body> Good Day, Please note the Dealer Rating reports are now available, Please find attached the summary and detail reports. Kind Regards </body></html>

objMsg.AddAttachment "C:\Users\FransJaco\Documents\testnew\"& Unique.text & ".pdf"

Set objMsg.Configuration = msgConf

' Send
objMsg.Send
LogAdd("Mail Sent"& " " & Email.text)
'Msgbox("Email send ok")
Next
Set objMsg = nothing
Set msgConf = nothing
'End sub
rem added endsub jaco
'Next

LogAdd("Emails Sent Success")
End sub

marcus_sommer · ‎2016-03-14

I would rather use a tablebox to loop through like: Re: vb scrip to display name and for mailing might be this helpful: Re: Sending mails macro vbs

- Marcus

View solution in original post

Chanty4u · ‎2016-03-14

chk dis

Useful Qlikview Macros

marcus_sommer · ‎2016-03-14

I would rather use a tablebox to loop through like: Re: vb scrip to display name and for mailing might be this helpful: Re: Sending mails macro vbs

- Marcus

Report Inappropriate Content · ‎2016-03-15

Good day Marcus, Thanks for your help I finally got it working 🙂 I have posted my code below if anyone else ever comes across this problem, I tried to comment where I can for easier reference.

Sub SendGMail ()
LogAdd("Starting Mailing Process")

'CLEAR ACTIVE DOCUMENT SELECTIONS
LogAdd("Clear all selections")
ActiveDocument.ClearAll true
LogAdd("Selections Cleared")

' CREATE ARRAY
Set val=ActiveDocument.Fields("Unique").GetPossibleValues(20000)
For i = 0 to val.count-1
set Unique = val.Item(i)
'select each one that's on the arraylist
ActiveDocument.Fields("Unique").Select Unique.text
LogAdd("Getting Client")

' CLEAR EMAIL CACHE -- Else it will cause multiple attachments to be sent in error
Set objMsg = nothing
Set msgConf = nothing

' RECONFIGURE EMAIL
Set objMsg = CreateObject("CDO.Message")
Set msgConf = CreateObject("CDO.Configuration")

' EMAIL SERVER CONFIGURATION
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "****@gmail.com" 'type your mail id
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "*******" 'Type your acccount Password
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = 1
msgConf.Fields.Update

' CREATE TABLE LOOP TO GET EMAIL ADDRESSES
set table = ActiveDocument.GetSheetObject( "TB01" )

w = table.GetColumnCount
h = table.GetRowCount

set CellMatrix = table.GetCells2(0,0,w,h)

for RowIter=1 to h-1

for ColIter=1 to 1

LogAdd("Getting Email")
LogAdd((CellMatrix(RowIter)(ColIter).Text))
'msgbox(CellMatrix(RowIter)(ColIter).Text)

'START MAILING PROCESS
objMsg.To = (CellMatrix(RowIter)(ColIter).Text)
objMsg.From = "*******@gmail.com"
objMsg.Subject = "Reports"
objMsg.HTMLBody = "<html><body> Good Day, Please note the reports are now available, Please find attached the summary and detail reports. Kind Regards </body></html>"
objMsg.AddAttachment "C:\Users\FransJaco\Documents\testnew\" & Unique.text & ".pdf"
Set objMsg.Configuration = msgConf
'SEND EMAIL
objMsg.Send
LogAdd("Mail Sent"& " " & (CellMatrix(RowIter)(ColIter).Text))

next
LogAdd("Next Email in Table")
next
LogAdd("Next Client in Array")
next

' CLEAR EMAIL CACHE
Set objMsg = nothing
Set msgConf = nothing

' FINISH MAILING PROCESS
LogAdd("Mailing Process Completed")
End sub