Repeat script reading from the next Excel row

[Michalis]

Hi everybody, this is my first script and Im still learning the autoit coding. I need an advise on the following script which reads from an Excel file and paste each row to a form (notepad for simplicity), then prints to pdf and then emailing. It is a very bad code indeed, however I manage to make it work. I must have the delay above 100 to work in all windows and a lot of sleeps. So if you know where should I look and study to make it more stable or you have any suggestions on any conceptual improvements of this script in order to became more reliable I would appreciate it. Watch out the loop do not finish for some reason. Thank you in advance.

expandcollapse popup

#include <array.au3>
#include <Excel.au3>

HotKeySet("{ESC}", "Terminate")
Func Terminate()
    Exit 0
EndFunc
Opt("SendKeyDelay", 80)
Local $sFilePath1 = @ScriptDir & "\colum.xls" ;This file should already exist with 3 colums and 10 rows
Local $oExcel = _ExcelBookOpen($sFilePath1)
$aArray= _ExcelReadSheetToArray($oExcel,1,1,3,3,True) ; $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 10, $iColCnt = 3, $iColShift = True as excel
$rows = 3
Opt("WinTitleMatchMode", 1)
Local $loop = 0
Do
For $i = 1 to $aArray[0][0]  ; if you change 1 it will start reading from that.
$sR1 = $aArray[$i][0] ; name
$sR2 = $aArray[$i][1] ; message
$sR3 = $aArray[$i][2] ; email
Run("notepad.exe")
WinWaitActive("Untitled - Notepad")
send($sR1)
send("{ENTER}")
send($sR2)
send("{ENTER}")
send($sR3)
send("{ENTER}")
; from here actions are taken
send("^p")
sleep(100)
WinActivate("Print")
ControlSend("Print", "", "[CLASS:Button; INSTANCE:13]", "{ENTER}")
sleep(100)
local $sAppTitle0 = ( WinGetTitle("[active]"))
ControlSend($sAppTitle0, "", "[CLASS:Edit; INSTANCE:1]", $sR1) ;file name
send("{ENTER}"); save file to default pdf printer an to default folder
sleep(100)
local $sAppTitle = ( WinGetTitle("")) ; pdf program
WinWaitActive($sAppTitle)
send("^e"); shortcut for send email
sleep(350)
local $sAppTitle1 = ( WinGetTitle("[active]")); outlook window
WinWaitActive("Untitled - Message (HTML) ", "")
ControlSend("Untitled - Message (HTML) ", "", "[CLASS:RichEdit20WPT; INSTANCE:1]", $sR3 & ";"); email address
sleep(530)
ControlSend("Untitled - Message (HTML) ", "", "[CLASS:RichEdit20WPT; INSTANCE:4]", $sR1 & " - this is text after variable"); subject
sleep(130)
send("!s") ; send
send("^q") ; quit
ProcessClose("Notepad.exe")
Next
;after that point it should start repeating the process from the second row until $iRowCnt = 10
    $loop = $loop + 1
Until $loop = $rows


_ExcelBookClose($oExcel) ; close excel
ProcessClose("Excel.exe") ; close excel
MsgBox(0,"Total ","Total Email sent" &": "& $i - 1) ;ProcessClose("notepad.exe") ; close notepad ;send("!n")

[water]

Welcome to Autoit and the forum!

Looks like you want to use Outlook to send the email. Which version of Outlook do you run?

You could try my OutlookEX UDF to create and send the mail without automating the GUI.

[Michalis]

Thank you for you help. I used the script on both Outlook 2007 and 2010. With small differences on ControlSend commands and it works the same. I will study your recommendation on OutlookEX and I will reply with my experience.

[water]

Easiest way to send an email using my OutlookEX UDF is with function _OL_Wrapper_SendMail.