topic Re: Macro send individual emails QlikView in QlikView

Can I use a macro macro send individual emails QlikView?

johngouws — Wed, 14 Jun 2023 13:02:40 GMT

The objective is to let each recipient receive only their email, not see other recipients as well.

Source Sheet:

CustomerCode	email	friendly_name
CUS001	john@CUS001.com	John
CUS001	fred@CUS001.com	Fred
CUS001	sales@CUS001.com	Paul
CUS002	info@CUS002.com
CUS003	reception@CUS003.com
CUS004	sales@CUS004.com

With the current macro when CUS001 receives the email, the ‘Email To’ shows all the email addresses for Customer CUS001.

Email To: john@CUS001.com; fred@CUS001.com; sales@CUS001.com. The objective is to let each recipient receive only their email.

Below is a part of the macro. It consists of three sections:

(1) DistributeReports, does some checking to run the macro or not, but most importantly, it loops through the Customer Codes.

(2) Reports, just writes the PDF.

(3) Mail_Report, defines the needed parameters and most importantly, loops through the email addresses.

The goal is to send each email Recipient their own email without revealing the distribution list. A thought is to somehow include the email address in the (1) DistributeReports section along with the CustomerCode.

Until now I am unable to get this working. I am hoping someone can shed a idea or two on what I can try.

Thank you.

(1) Sub DistributeReports()

...VBScript

‘’Below section loops through the CustomerCodes:

Set Custs = ActiveDocument.Fields("CustomerCode").GetPossibleValues(200)

For i = 0 to Custs.count -1

ActiveDocument.Fields("CustomerCode").Select Custs.item(i).Text

Reports()

End Sub

sub (2) Reports

..... VBScript

'' Create the PDF file

call Mail_Report

end sub

sub (3) Mail_Report

..... VBScript

'' All the definitions

‘’Below section loops through the email addresses:

set id_TO = ActiveDocument.Fields("emailaddress").GetPossibleValues(200)

val = id_TO.Count

for i=0 to val-1

TO_ADDR = TO_ADDR & id_TO(i).text & ";"

next

'' Send the email

.... VBScript

end sub

Re: Macro send individual emails QlikView

marcus_sommer — Wed, 10 Aug 2022 08:49:32 GMT

It looked as if the multiple recipients per CustomerCode are here concatenated:

for i=0 to val-1
TO_ADDR = TO_ADDR & id_TO(i).text & ";"
next

You may change it to:

for i=0 to val-1
Send the email to id_TO(i).text
next

- Marcus

Re: Macro send individual emails QlikView

johngouws — Wed, 10 Aug 2022 11:18:01 GMT

You are correct, they were being concatenated.
I've implemented your suggestion. There are some minor changes to the code logic, but works perfectly for what I need.

Thank you.

Re: Macro send individual emails QlikView

johngouws — Fri, 12 Aug 2022 11:42:43 GMT

Hello Marcus,

Thanks for the previous answer and apologies for asking directly.

Sending the individual email are working perfectly.

To make the email more ‘personal’ I have a field ("Friendly_Name" which has the persons first name.

In the email body I am currently saying :

objEmail.TextBody = Join( Array( _

"Good Morning " _

, " " _

…

But I would like to have the “Friendly_Name” after Morning.

"Good Morning "& TO_FName _

, " " _

…

When I use the logic as I have below, a person will receive the email multiple times with different first names. I am wonder if there is a way to include the first name in the same logic and the email address? My attempts to include the Friendly_Name in the same for loop are all failing dismally.

‘’Sends to correct individual email address:

set id_TO = ActiveDocument.Fields("emailaddress").GetPossibleValues(200)

val = id_TO.Count

for i=0 to val-1

TO_ADDR = id_TO(i).text

‘’ Send mail

Friendly_Name:

set id_FN = ActiveDocument.Fields("Friendly_Name").GetPossibleValues(200)

val1 = id_FN.Count

for j=0 to val1-1

TO_FName = id_FN(j).text

Thank you, I appreciate any pointers.

Re: Macro send individual emails QlikView

marcus_sommer — Fri, 12 Aug 2022 12:51:19 GMT

Quite often it's not possible - or at least rather complicated - to loop through multiple fields by respecting the relationship between the values. Much easier would it be to load everything needed within a table-box/chart and then to loop through this table. It's not more difficult as looping through fields, see:

Solved: Macro - Loop Through Table - Qlik Community - 367982

- Marcus

Re: Macro send individual emails QlikView

johngouws — Fri, 12 Aug 2022 12:57:50 GMT

Thank you. I am going to look through that.

John