topic Re: Set command breaks macro in QlikView

Set command breaks macro

dhborchardt — Fri, 06 Jul 2018 20:31:22 GMT

I have this working but want to pass the subject along as a variable and use it in the subject (emailObj.Subject = varSubject ) when the email gets sent out. If I add the two lines:

'Set varSubject = ActiveDocument.Variables("vSubject").GetContent.String

'emailObj.Subject = varSubject

the email will not get sent out. As soon as I comment them it works again. Any Set command I use will break it and email will not go out.

the vSubject variable is created in the load script. I might have 0, 1 or many emails to send out so I am using the For/Next loop.

Should I be creating the variable in a different manner?

Is there a way to debug the macro. Maybe see the errors?

//>>>>>>>>>>>>>>>>>>>>>> Partial Load Script >>>>>>>>>

LET NumRows=NoOfRows('ContractAlert') ;
FOR i=1 to $(NumRows)
LET vEmailSubject=Peek('Contract',$(i));
LET vSendEmail = SendEmail (vEmailSubject);
NEXT ;

'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> FUNCTION IN MACRO >>>>>>>>>>>>>>

Function SendEmail()
Set emailObj = CreateObject("CDO.Message")
Set emailConfig = emailObj.Configuration

'AS SOON AS I USE ANY SET BEYOND THE TWO LISTED ABOVE THE EMAIL WILL NOT SEND
'Set varSubject = ActiveDocument.Variables("vSubject").GetContent.String

emailConfig.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "10.21.20.72"
emailConfig.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
emailConfig.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

emailConfig.Fields.Update
emailObj.From = 123@456.COM
emailObj.To = 123@456.COM
emailObj.Subject = "test" 'THIS WORKS
'emailObj.Subject = varSubject 'THIS DOES NOT WORK
emailObj.TextBody = "Test CDO"
emailObj.Send

End Function

Re: Set command breaks macro

m_woolf — Mon, 09 Jul 2018 12:09:02 GMT

Try:

varSubject = ActiveDocument.Variables("vSubject").GetContent.String

Re: Set command breaks macro

dhborchardt — Mon, 09 Jul 2018 12:55:34 GMT

Thanks for the help. I get a little confused on functions.

I am now getting -- Object required: 'ActiveDocument.Variables(...)'

when I click the test button in the module editor and the email does not get sent when I run the load script.

In the load script I have

LET vSubject=Peek('Contract',$(i));

I am amusing this is the magic that creates the variable that can be used in the macro by taking the value of the contract field? Contract is a field in the table. Is it correct to say that vSubject should now be in memory?

In the vbscript/macro I have

Dim VarSubject

VarSubject = ActiveDocument.Variables("vSubject").GetContent.String

emailObj.Subject = VarSubject

Re: Set command breaks macro

Miguel_Angel_Baeyens — Mon, 09 Jul 2018 14:20:07 GMT

You can see the contents of any variable in Settings > Variable Overview.

If the variable is null or has strange characters, that could be the reason why it's not working. You can also use the Set in the script for a fixed text, instead of a variable, to see whether it works.

EDIT: on a second read, two thoughts:

No, any ActiveDocument property does not exist when reloading the script, for this the document needs to be open after a reload
It could be a typo but the names of the variables are different in the script (vEmailSubject) and in the macro (vSubject)

Re: Set command breaks macro

dhborchardt — Mon, 09 Jul 2018 17:24:09 GMT

Am I on the right track in the load script with? --

LET vSubject=Peek('Contract',$(i));

Will this do what it takes to make it available to a Macro/function called from my load script?

I do not see vSubject in Settings>Variable Overview.

So I cannot use ActiveDocument because it is not available until after the reload is done? Does this mean that all lines be interpreted and you are back to the document OR from LOAD to ; since there can be multiple loads?

How do I make the value available when I am running the macro?

Here is what I need to do. I have it all working except for the subject field.

1. Load script loads from a SQL query. The results show only the records where the 'Contract' due date is current. There might be 0, 1 or many contracts due.

2. The FOR NEXT loop will handle each record (Contract) and send an email to the specific person that needs to act on it.

3. A Function/Macro will be used to send that email.

I have everything working except for being able to use variables in the Subject, Body or Address. If it helps I can supply the simplified code with an in line table. This will take a little time and I wanted to get back as soon as possible.

Re: Set command breaks macro

cwolf — Mon, 09 Jul 2018 20:07:12 GMT

You can pass variables as parameters to the macro.

For example:

Script:

let vSubject=peek(...);

let vAddress=peek(...);

set vBody='Body Text';

let vSendEmail = SendEmail(vSubject,vAddress,vBody);

Macro:

function SendEmail(pSubject,pAddress,pBody)

...

emailObj.To = pAddress

emailObj.Subject = pSubject

emailObj.TextBody = pBody

...

end function

Re: Set command breaks macro

dhborchardt — Tue, 10 Jul 2018 19:10:24 GMT

Thank you for getting me back on track. I did start by trying to do it that way but had my syntax off.

In my FOR/NEXT loop I was using FOR i=1 to $(NumRows) When I should have been using i=0 as PEEK starts at zero. Below I have included enough of the Load and Macro to get someone else started if they want to send separate emails to multiple people.

I have seen a lot of negative opinions on using macros but could not make it work any other way. I did start by trying to use the Alerts. I was not able to get it to send to multiple people each with its specific Contract number. If anyone sees this and wants to enlighten me I would appreciate it.

LOAD SCRIPT

ContractAlert:

LOAD * ,
Today()-RenewContractDate AS Noofdays;
Load * Inline [
Name, UserId, Contract,      RenewContractDate, Email
Bob, bobH00,    56940,    6/20/2018, bob@CFG123.org
Joe, JoeH01,    12342,    6/14/2018, joe@CFG123.org
Sally,    SalH01, 29846, 6/12/2018, sally@CFG123.org

];

LET NumRows=NoOfRows('ContractAlert');
FOR i=0 to $(NumRows)

LET vSubject=peek('Contract',$(i),ContractAlert);
SET vBody='Your Contract needs to be updated';

LET vSendEmail = SendEmail(vSubject,vBody);

NEXT
;

Function/Macro

function SendEmail(pSubject,pBody)

Set emailObj = CreateObject("CDO.Message")
Set emailConfig = emailObj.Configuration

emailConfig.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "10.55.60.76"
emailConfig.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
emailConfig.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing")    = 2
emailConfig.Fields.Update

emailObj.From    = "elvis@cfg123"
emailObj.To      = " elvis@cfg123"
emailObj.Subject = pSubject
emailObj.TextBody = pBody

emailObj.Send

End Function