Jump to content
Sign in to follow this  
3mustgetbeers

Read excel & output to new email

Recommended Posts

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 by 3mustgetbeers

Share this post


Link to post
Share on other sites
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 ...?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
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

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
3mustgetbeers

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

Am I missing something?

Share this post


Link to post
Share on other sites
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)
  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
3mustgetbeers

water

That works fantastic, thanks very much!

3mgb

Share this post


Link to post
Share on other sites
water
:oops:

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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  

×