Pass field value to Module/Macro

[dhborchardt]

Need to send the value of the field [Contract] to a function in the Module editor where VBScript will send an email. I have tried to get it to work in many ways and the example below is but one. The email part works but the Subject shows blank when the email is delivered. I need to fill in 'Subject' with the value of [Contract]. Once I get this to work I will need to do the same for text body.

Should I be using Sub instead of Function? I tried but could not get the email to send with SUB.

Load Script

ContractAlert:
  Load * Inline [
Name, UserId, Contract,    RenewContractDate,  Email
Bob, BOBM12,    56940,    6/20/2018, Bobm@CFG123.com
];

LET xCon = Contract;
LET vSendEmail = SendEmail(xCon);

Module VBScript

Function SendEmail(xCon)
  Set emailObj      = CreateObject("CDO.Message")
emailObj.From    = " Bobm@CFG123.com "
emailObj.To      = " Bobm@CFG123.com "
emailObj.Subject  = xCon
emailObj.TextBody = "Test Send Email"
 
Set emailConfig = emailObj.Configuration

emailConfig.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "10.52.43.34"
emailConfig.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport"

[vupen]

Hi Dale

Assuming you have trapped the value of the Email Subject in a variable named "vEmailSubject", you can read this variable in the Macro and use this value for the emailObj.Subject:

set v = ActiveDocument.Variables("vEmailSubject")

emailObj.Subject = v.GetContent.String

To trap the Contract field value into the variable, you can use either Peek(If the table has only 1 row) or LookUp(table has multiple rows and you know the user ID/key field value whose row you want to read).

If you want a return value, a Function is used). For ex., you may want to notify if the email was successfully sent).

If you want to send an email and you do not care if the processing is successful or not, you may use a Sub.

Hope this helps.

[dhborchardt]

Thank you for the direction.

There might be none, one or more emails that will need to go out depending on due date. I am assuming I can use peek if I am handling only one row at a time with For/Next?

I attempted to trap the 'Contact' field', not sure if I did it correct. I added the two line you gave me to the module but if either of them are run the email will not send. Is my syntax correct? See below new lines are in this color

Is there a way to test to see if the variable made it to the module? I try to run msgbox but it does not seem to run from the module.

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=1 to $(NumRows)
LET vEmailSubject=Peek('Contract',$(i));
LET vSendEmail = SendEmail (vEmailSubject);

NEXT ;

Module VBScript

Function SendEmail(vEmailSubject)

set v = ActiveDocument.Variables("vEmailSubject")
Set emailObj      = CreateObject("CDO.Message")
emailObj.From    = "Bob@cfg123.org"
emailObj.To      = "Bob@cfg123.org"
emailObj.Subject = v.GetContent.String

1. emailObj.TextBody = "Test CDO"
   
   Set emailConfig = emailObj.Configuration
   
   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"

1. emailObj.Send
   
   End Function