'!!!!!!!!!functions
' AML_sendEmail
' AML_stripSpecialChars
' AML_PrintToExcel
' AML_CheckOutputFile
' AML_writeToFile

sub AML_writeToFile(fileName, fileText)
  Const ForAppending = 8

 Set fso = CreateObject("Scripting.FileSystemObject") 
  Set objTextFile = fso.OpenTextFile (fileName, ForAppending, True)
  objTextFile.WriteLine(fileText)
  objTextFile.Close
end sub

' * = * = * = * = * = * = * = * = * = * = * = * = * = * = *
' * AML_CheckOutputFile - check to see if a file exists
' *           checkType parameter determines if file
' *           should be deleted or just reported on
' *   Parms:
' *       outFile   -> file to check
' *     checkType -> 0 = Check to see if file exists, if so delete it
' *             1 = Check to see if file exists, if NOT, then
' *             keep checking in loop until it exists
' *     waitTime  -> Only used with checkType 1, amount of time to keep
' *          checking for the file before quiting.
' * = * = * = * = * = * = * = * = * = * = * = * = * = * = *
sub AML_checkOutputFile(outFile, checkType, waitTime)
      Set fileTest = CreateObject("Scripting.FileSystemObject")
      currentStatus = fileTest.FileExists (outfile)
if checkType = 0 then
  If currentStatus then
    set filetodelete = fileTest.GetFile(outfile)
    filetodelete.Delete
  end if
else
   waitvar = 0
   do while currentStatus = false
      ' ** let QV sleep for 1 seconds **
      ActiveDocument.GetApplication.Sleep 1000
      currentStatus = fileTest.FileExists (outfile)    
      waitvar = waitvar + 1
      if waitvar > waitTime then
        exit do
      end if
   loop
end if   
      set fileTest = nothing
end sub
'=========================================
'- END Check Output File
'=========================================

'=========================================
'- S E N D   E M A I L
'=========================================
rem * = * = * = * = * = * = * = * = * = * = * = * = * = * = *
rem * AML_sendEmail 
rem *    Parms
rem *   fileAttachment - full path + filename to be attached --> eg. "c:\mydir\myfile.pdf"
rem *   emailAddress - email address to send to.
rem * = * = * = * = * = * = * = * = * = * = * = * = * = * = *
sub AML_sendEmail(fileAttachment, emailAddress)
  Set objEmail = CreateObject("CDO.Message")  

  objEmail.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2  
  objEmail.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"  
  objEmail.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465  
  objEmail.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 
  objEmail.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
  objEmail.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusername")= "" 
  objEmail.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "" 
  objEmail.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True  
  objEmail.Configuration.Fields.Update
  
  objEmail.From = "SendingFromEmail@myurl.com"  
  objEmail.To = emailAddress  
  '-------------------------------------
  '- Check to see if subject and body vars exist, if not create them
  If NOT AML_VariableExists("vEmailSubject") then
  call AML_CreateVariable("vEmailSubject", "Default Email Subject",0)
  end if
  If NOT AML_VariableExists("vEmailBody") then
    call AML_CreateVariable("vEmailBody", "Default Email Body",0)
  end if
'-------------------------
  set v = ActiveDocument.Variables("vEmailSubject")
  objEmail.Subject = v.GetContent.String
  set v = ActiveDocument.Variables("vEmailBody")  
  objEmail.Textbody = v.GetContent.String  
  objEmail.AddAttachment fileAttachment
on error resume next
  objEmail.Send  
  Set objEmail = nothing  
  'Write to the Log file
  call AML_writeToFile("AnalytixEmailLog" & Month(now()) & Year(now()) & ".txt", emailAddress & " -- " & fileAttachment)
end sub
'=========================================
'- END Send Email
'=========================================

'=========================================
'- S T R I P   S P E C I A L   C H A R S
'=========================================
function AML_stripSpecialChars (strIn)
Dim objRegExpr 

  'Create an instance of the regexp object 
  Set objRegExpr = New regexp
  'Set Regular Expression Properties
  objRegExpr.Pattern = "[\?\>\.\,\@\!\#\$\%\^\&\*\(\)\-\+\/\\]"
  objRegExpr.Global = True 
  objRegExpr.IgnoreCase = True
  
  AML_stripSpecialChars = objRegExpr.Replace(strIn, "")
end function
'=========================================
'- END Strip Special Chars
'=========================================

'==============================================
'- P R I N T   T O   E X C E L   &   E M A I L
'==============================================
rem * = * = * = * = * = * = * = * = * = * = * = * = * = * = *
rem * PrintToExcel 
rem *    Parms
rem *   printToVar - Name of Variable which hold the Directory
rem *          to store the created files in 
rem *   chartNameToExport - Name of the chart to export.  This is 
rem *           the Object ID found on the General Tab
rem *             of a chart's properties.
rem *   groupField - Field to slice/dice data on.  Example, group field
rem *       of SalesRep, an excel file would be created for
rem *         each rep with only his data. 
rem *         Pass "" to disable loop/reduce and instead export whole chart.
rem *   outputEmailName -Field that contains the email addresses to email to.  
rem *           Pass "" to disable email sending.
rem * = * = * = * = * = * = * = * = * = * = * = * = * = * = *
Sub AML_PrintToExcel(printToVar, chartNameToExport, groupField, outputEmailName)
' Create a Log File --- NOT IMPLEMENTED YET

' Set the filepath to save to
       set v = ActiveDocument.Variables(printToVar)
       filePath = v.GetContent.String
       '--Make sure filePath ends in a backslash
       IF RIGHT(filePath,1)<> "\" then
          filePath = filePath & "\"
       END IF

' ==== Set the ActiveDocument =======
'   == Current Doc ====
     Set Doc = ActiveDocument

' Get the object to export
     Set chart =  ActiveDocument.GetSheetObject(chartNameToExport)
     'Get the Chart properties so we can get the Chart text
     Set p = chart.GetProperties
       chartTitle = p.GraphLayout.WindowTitle.v

' Get the Selected(possible) values of the group by field unless 
'   NO groupby field passed in.  In that case just export whole chart.
     IF groupField = "" THEN
      groupCount = 1
     ELSE
        Set ValueList = Doc.Fields(groupField).GetPossibleValues
        groupCount = ValueList.Count
     END IF
' === Loop through the values from above printing   ==========
' === an excel file of the chart for each Value     ==========
   For i = 0 to groupCount - 1
     IF groupField = "" THEN
      outFileChartNameEnd = "FULL"
     ELSE
        Doc.Fields(groupField).Clear 'Must clear first of if only one value selected the .Select would act as a toggle
      Doc.Fields(groupField).Select ValueList.Item(i).Text
      outFileChartNameEnd = AML_stripSpecialChars(ValueList.Item(i).Text)
   END IF
     outputFile = filePath & chartTitle & "-" & outFileChartNameEnd & ".XLS"
' If file exists delete before trying to create      
       call AML_checkOutputFile(outputFile, 0, 0)      
'-----------------------------------------------       
' Can print a report with the line below       
'    Doc.PrintReport "RP01", "QlikViewPDF", false
'-----------------------------------------------
'   Export Chart to outputFile
       chart.ExportBiff outputFile

' Don't move on until output file has been created       
       call AML_checkOutputFile(outputFile, 1, 1200)

'------------------------------------------------
'Update Log File -- outputFile created
'------------------------------------------------
' Get the email to send to      
     IF outputEmailName <> "" THEN
      set EmailField = ActiveDocument.Fields(outputEmailName).GetPossibleValues   
      if EmailField.Count > 0 then
        for emailCounter = 0 to EmailField.Count - 1
          emailAddress = EmailField.Item(emailCounter).Text
        ' Send email (pass outputfile to attach and email address to send to) 
          call AML_sendEmail(outputFile, emailAddress)
          next
      end if       
    END IF
   Next

  'When done clear group by field to bring the state of our selections back to what it was before printing.
 IF groupField <> "" THEN  
   Doc.Fields(groupField).Clear
 END IF
'------------------------------
'Update Log File to DONE
'------------------------------
End Sub 
'=========================================
'- END Export to Excel
'=========================================