Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
End Function
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
Try:
varSubject = ActiveDocument.Variables("vSubject").GetContent.String
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
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:
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.
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
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