Read excel & output to new email

[3mustgetbeers]

Hi all,

I have an excel spreadsheet consisting of 3 columns with the headers being "Employee Name" "Line manager name" & "Leave Date". These are in A1, B1 & C1 respectively.

I am trying to email each line manager to say something along the lines of;

“$Employee has left the company on $Leave_Date. In order to delete there is account we require a delete user form. This can be found here. Please complete the form and return it to the IS Service Desk and we will remove the account. “

Would it be possible (and a good approach) to read each column to its own array, then call them in separate emails – i.e. 1 email per line manager? If so, how do I go about telling _ExcelReadArray() to stop at the last blank cell in the column? And more importantly, how do I split this array down into single cells to use in seperate emails??

Thanks in advance for replies,

3mgb

Edited March 8, 2012 by 3mustgetbeers

[water]

Something like this:

#include <excel.au3>
$oExcel = _ExcelBookOpen("C:temptest.xls", 0, True)
For $iLine = 1
    Global $aArray = _ExcelReadArray($oExcel, $iLine, 1, 3, 0)
    If $aArray[0] = "" Then Exit ; Empty line detected - exit
    ; Send mail: $aArray[0] = Employee name, $aArray[1] = Line manager name, $aArray[2] = leave date
Next
_ExcelBookClose($oExcel, 0, 0)

How do you want to send the mail? Outlook, SMTP ...?

[3mustgetbeers]

Thanks water!

An email needs to be sent, via Outlook, to every line manager in the spreadsheet; so if there are 7 leavers this month; all 7 line managers will receive an email containing their direct report & leave date

[water]

To send an email using Outlook I would suggest my OutlookEX UDF, function _OL_Wrapper_SendMail makes sending an email much simpler.

I would suggest to sort the Excel file by line manager before extracting the data and send all leaves in 1 single message to the manager.

[3mustgetbeers]

I may be being a bit supid, but I keep getting a syntax error line "For $iLine = 1"

Am I missing something?

[water]

Here is an enhanced version:

#include <excel.au3>
#include <OutlookEx.au3>
$oExcel = _ExcelBookOpen("C:temptest.xls", 0, True)
$oOL = _OL_Open()
Global $iLine = 1
While 1
    Global $aArray = _ExcelReadArray($oExcel, $iLine, 1, 3, 0)
    If $aArray[0] = "" Then Exit ; Empty line detected - exit
    $iLine += 1
    _OL_Wrapper_SendMail($oOL, $aArray[1], "", "", "Employee leave", "Employee " & $aArray[0] & " has left the company on " & $aArray[2])
WEnd
_ExcelBookClose($oExcel, 0, 0)
_OL_Close($oOL)

[3mustgetbeers]

water

That works fantastic, thanks very much!

3mgb

[water]