Sign in to follow this  
Followers 0
Michalis

Repeat script reading from the next Excel row

4 posts in this topic

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.

#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")

Share this post


Link to post
Share on other sites



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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0