Jump to content

Recommended Posts

Posted (edited)

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
Posted

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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Posted

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

Posted

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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Posted

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)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Posted
:oops:

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...