2 Replies Latest reply: Aug 12, 2011 10:38 AM by Igor.Valle RSS

    Visual Basic Script and loop with set analysis

      Hi all,


      first of all thanks in advance for your reply.


      I have this problem, i made (using something of already existing) a script that export a single document in excel, and than it send a mail with attachment. ( It's works  : D )


      But I have same problems when i try to change and improve my script.


      In particular: at the moment i want to iterate in same field ("LOCATION", there are 15 locations), set an other field with a set analysis like this:


      ActiveDocument.GetField("POST_DATE").Select _

      ActiveDocument.Evaluate("=max({<LOCATION = {'TRIAL'}>}POST_DATE)")


      the problem is that when i set second field (POST_DATE), first selection in LOCATION disappear, so my export become wrong  (see code): (


      Second problem is that I would like to reuse the file filePath = "C:\Test.xls" and i want write all exports in different sheets, it's possible using variable to naming sheets?

      Also for this set analysis:

      ActiveDocument.Evaluate("=max({<LOCATION = {'TRIAL'}>}POST_DATE)") i need to substitute {'TRIAL'} with some variables...


      Thanks in advance for any suggests




      This code works:


      Sub SendGMail()


      ' Object creation

      Set objMsg = CreateObject("CDO.Message")

      Set msgConf = CreateObject("CDO.Configuration")


      ' Server Configuration

      msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

      msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"

      msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465

      msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

      msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "username@gmail.com"

      msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"

      msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = 1



          'inizio script creazione excel può essere fatto a parte

          'aggiunta delle righe per cancellare il file se esiste

          Dim fs, filespec

          filespec = "C:\Test.xls"

          Set fs = CreateObject("Scripting.FileSystemObject")

          If fs.FileExists(filespec) = True Then

          fs.DeleteFile filespec


          Set fs = Nothing

          End If


           'Set the path where the excel will be saved

           filePath = "C:\Test.xls"


           'Create the Excel spreadsheet

           Set excelFile = CreateObject("Excel.Application")

           excelFile.Visible = true

           'Create the WorkBook

           Set curWorkBook = excelFile.WorkBooks.Add

           'Create the Sheet

           Set curSheet = curWorkBook.WorkSheets(1)



          ActiveDocument.GetField("LOCATION").Select _






          ActiveDocument.GetField("POST_DATE").Select _

          ActiveDocument.Evaluate("=max({<LOCATION = {'TRIAL'}>}POST_DATE)")



           'Get the chart we want to export

           Set tableToExport = ActiveDocument.GetSheetObject("CH05")



           Set chartProperties = tableToExport.GetProperties

           tableToExport.CopyTableToClipboard true


           'Get the caption

           chartCaption = tableToExport.GetCaption.Name.v

           'MsgBox chartCaption


           'Set the first cell with the caption

           curSheet.Range("A1") = chartCaption

           curSheet.Paste curSheet.Range("A2")

           excelFile.Visible = true


           'Save the file and quit excel

           curWorkBook.SaveAs filePath





           Set curWorkBook = nothing

           Set excelFile = nothing


           'fine script creazione excel



      ' Email

      objMsg.To = "to@destination.it"

      objMsg.From = "PresidentObama@usa.us"

      objMsg.Subject = "Ordini creati con delivery date < submit date"

      objMsg.HTMLBody =  "Estrazione relativa al " & Date() & " : ordini con deliveryDate < submit date "

      objMsg.Sender = "Igor Valle"

      objMsg.AddAttachment "C:\Test.xls"


      Set objMsg.Configuration = msgConf


      ' Send



      ' Clear

      Set objMsg = nothing

      Set msgConf = nothing


      End Sub