Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
The objective is to let each recipient receive only their email, not see other recipients as well.
Source Sheet:
CustomerCode |
|
friendly_name |
CUS001 |
John |
|
CUS001 |
Fred |
|
CUS001 |
Paul |
|
CUS002 |
||
CUS003 |
||
CUS004 |
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
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
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
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.
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.
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
Thank you. I am going to look through that.
John