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