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

Visual Basic Script and loop with set analysis

Igor.Valle

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

msgConf.Fields.Update

 

    '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.Evaluate("TRIAL")

 

     ActiveDocument.GetApplication.WaitForIdle

 

 

    ActiveDocument.GetField("POST_DATE").Select _

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

 

     ActiveDocument.GetApplication.WaitForIdle

     '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

     curWorkBook.Close

     excelFile.Quit

 

     'Cleanup

     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

objMsg.Send

 

' Clear

Set objMsg = nothing

Set msgConf = nothing

 

End Sub