Can I use a macro macro send individual emails QlikView?

[johngouws]

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()

                Next

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

[marcus_sommer · Accepted solution]

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

[johngouws]

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. 

[johngouws]

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

            Next

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

                Next

Thank you, I appreciate any pointers.

[marcus_sommer]

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

[johngouws]

Thank you. I am going to look through that. 

John