Read Excel Cells - Send email depending on whats in there

[13lack13lade]

Hi Guys,

So what i'm trying to accomplish:

I have a spreadsheet with a bunch of info on it, in the first column it has Area Codes, e.g AU for AUS, NL for Netherlands etc...

What i want to do, is have autoit read the excel file and define which email addresses to send the spreadsheet to depending on what area codes are in the file.

To make it even trickier (duno if it has an effect on autoit or not) but the data is Filtered which means the first row will not always be the same.. so it needs to only read the data showing aka filtered data.

Then to take it 1 step further the area codes are broken up into 4 teams - Asia, Europe, Aus, South Pacific, each team consists of a number of area codes.. eg TH = Thailand which falls under the Asia Team so would go to their email address.

What i want to do is Only send the file to teams who's area codes are effected... Some times this means emailing to only 1 team, sometimes all 4.

Also seeing as the Regions are made up of multiple Area Codes i want it to only add the email address once, for example if i had TH(thailand) & SG (singapore) i would only attach the asia team email address once.

I already have a way to send it out i litterally just need to figure out how to get autoit to 'read the filtered data in excel' and then attach the email addresses according to what area codes are in the filtered data.

Any help would be greatly appreciated!!!!

[NewPlaza]

This talks about filtering -> '?do=embed' frameborder='0' data-embedContent>>

Maybe it can help along the way.

[13lack13lade]

Thanks NewPlaza, not too sure if that will help though - What i was meaning is the spreadsheet when finished & ready for emailing, is filtered.

I need Autoit to read the Column A of the Filtered data and then decide which email addresses to send it to depending on the what area codes or 'data' is in column A.

Im just not sure how AutoIt works with excel's hidden rows (or filtered data)..

Edited August 28, 2013 by 13lack13lade

[kylomas]

13lack13lade,

Can you attach an example of the spreadsheet?

Also, run the following code.  Look at the resulting 2D array.  Is this representative of how your targets are organized?

#include <array.au3>

local $aTeams[25][10], $str = ''

$str &= 'Asia,Thailand,10240,10300,18180' & @lf
$str &= 'Asia,Vietnam,270000,250000' & @lf
$str &= 'Asia,Laos,01160,01190,01110' & @lf
$str &= 'Europe,Great Britain,12345,12453,12456' & @lf
$str &= 'Europe,Spain,99001,99002,99003,99121,99088' & @lf
$str &= 'Europe,France,82123,82901,82999,82111,82001,82818,82007,82299' & @lf
$str &= 'Europe,Ireland,76901' & @lf
$str &= 'Europe,Andorra,00901' & @lf
$str &= 'Europe,Northern Cypress,69123,69124,69125' & @lf
$str &= 'Australia,Melbourne,14111,14903' & @lf
$str &= 'Australia,Sydney,18222,18225,18201,18299,18454' & @lf
$str &= 'Australia,Devonport,16001' & @lf
$str &= 'Australia,City of Stirling,14901' & @lf
$str &= 'South Pacific,Guam,32001,32002,32003' & @lf
$str &= 'South Pacific,Tonga,17923,17921,17989,17399' & @lf
$str &= 'South Pacific,Fiji,23919' & @lf

; populate 2D array

$a1 = stringsplit($str,@lf,2)
for $1= 0 to ubound($a1) - 1
    $a2 = stringsplit($a1[$1],',',2)
    for $2 = 0 to ubound($a2) - 1
        $aTeams[$1][$2] = $a2[$2]
    Next
next

_arraydisplay($aTeams)

kylomas

edit: Is this related to >this post?

Edited August 28, 2013 by kylomas

[NewPlaza]

If I'm correct in that if the excel file was saved with filtering already applied then when you re-open the file it should still be filtered.

[NewPlaza]

#include <Excel.au3>

Local $sFilePath = @ScriptDir & "\Test.xls" ;This file should already exist
Local $oExcel = _ExcelBookOpen($sFilePath, 1, True); * $fVisible = 1;  Can be 0 after testing is done & code works as expected.......

If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist - Shame on you!")
    Exit
EndIf


$sCellValue = _ExcelReadCell($oExcel, 1, 1); * Rows & Columns
MsgBox(0, "THE CELL VALUE IS", $sCellValue)

Simple code to open a excel file and read row1,column1.

Do some If...Then.. statements to find out where you need to send (email).

Edited August 28, 2013 by NewPlaza

[13lack13lade]

New Plaza - Thats correct however how does autoit read the opened excel file.. for example the data is filtered yes... but i cant write a code to read column A as it will also read the unfiltered data correct??

Similar klyomas... ill attach the report. - Ill leave it saved as the completed product so you can see what im talking about... Column A = Area code... 

So i need autoit to read the showing data on "Todays Data" tab and then im guessing using If statements? Determine which teams need to be sent an email with the attached file.

Im thinking it will be like this... on the showing data...

If A = TH or AE or SG then add Team Asia to recipient,

Elseif A = AU then add Team Aus to recipient,

elseif A = FJ, code,code then add team South Pac to recipient,

Else if A = list of codes for europe then add europe to recipients

That way if there is any codes on there that fall under that team, then the team will receieve an email... However i need it to group up as there might be mulitple lines of TH for example and i only want it to add the Team Asia email address once... 

Hope that makes sense?

p_ebefffrm.xlsx

Edited August 28, 2013 by 13lack13lade

[kylomas]

Are you saying that there is only 1 email address per team?  That makes this very simple.  I cannot open the spreadsheet till I install the compatability pack (about 1 hour to download).

[13lack13lade]

4 Teams, 1 email address per team, If any of the teams Area codes show on the report then they need to be notified to correct the error. So its only 1 email to send to potentially 4 email addresses.

It seems fairly straight forward in my head however im a noob with autoit so dont know how to go about writing it and its just confusing me.. im still learning the basics lol  

Mate, i am more than willing to wait an hour for any help! it is greatly appreciated! thank you!

[NewPlaza]

Hmmm.. How about this??

#include <Excel.au3>

Local $sFilePath = @ScriptDir & "\TEST.xls" ;This file should already exist
Local $oExcel = _ExcelBookOpen($sFilePath, 1, True); * $fVisible = 1

If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist - Shame on you!")
    Exit
EndIf

Local $sCellValue, $RowCount = 2, $Recipients

While 1
    $RowCount = $RowCount + 1
    $sCellValue = _ExcelReadCell($oExcel, $RowCount, 1)
    If $sCellValue = "" Then ExitLoop
    If $sCellValue = "TH" Or $sCellValue = "AE" Or $sCellValue = "SG" Then
        If StringInStr($Recipients, "TeamAsia@Something.com") = 0 Then $Recipients = $Recipients & "TeamAsia@Something.com,"; * I assume comma to seperate email addresses.
    EndIf
    If $sCellValue = "AU" Then
        If StringInStr($Recipients, "TeamAus@Something.com") = 0 Then $Recipients = $Recipients & "TeamAus@Something.com,"; * I assume comma to seperate email addresses.
    EndIf
WEnd

MsgBox(0, "EMAIL ADDRESS ARE", $Recipients)

Oh yeah.. I was wrong. Even if filter is on and/or was saved filter autoit still sees all lines not just the filtered.

Edited August 28, 2013 by NewPlaza

[kylomas]

13lack13lade,

I can get it to read only the non-hidden rows like this:

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

Local $sFilePath1 = @ScriptDir & "\p_ebefffrm.xlsx"
Local $oExcel = _ExcelBookOpen($sFilePath1,0)

If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist - Shame on you!")
    Exit
EndIf

Local $sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)

; Extract integer last row and col
$sLastCell = StringRegExp($sLastCell, "\A[^0-9]*(\d+)[^0-9]*(\d+)\Z", 3)
Local $iLastRow = $sLastCell[0]
Local $iLastColumn = $sLastCell[1]

ConsoleWrite($iLastRow & ' ' & $iLastColumn & @LF)

for $1 = 2 to $iLastRow
    if $oExcel.Sheets("Todays Data").Rows($1).Hidden = True then
    Else
        ConsoleWrite($oExcel.Activesheet.Cells($1,1).Value & @LF)
    EndIf
next

but it looks like we need a cross reference of the codes in column A to team name.

kylomas

[water]

To only work with the visible cells of a filtered range you either need to check the "hidden" attribute of the cell or row or intersect the filtered and unfiltered range and only process the resulting range.

[13lack13lade]

NewPlaza - i had a feeling it would do that.. thats just how excel works unfortunately.. ~ Thank you for your script i think i will be able to use some of it along with Kylomas's to create what im after!

kylomas - oh wow that is amazing   thank you for that!!! this is definately a great starting point!

Do you mean the cross reference of codes needs to be in the excel spreadsheet or another excel spreadsheet somewhere? - or is there a way to cross reference the codes within auto it?

I am getting a list together ATM so that i will know specifically which codes = which region

[kylomas]

13lack13lade,

The first row in your spreadsheet has these values:

IE
IE
IE
IE
NL
TH
ZA
ZA

This looks like it need to be xrefed to a team somehow.  If you can show a table (spreadsheet) of these values to team name then the code will be easy!

kylomas

edit: unless I am misunderstanding you and you want to send an email to each unique value from the above list?

Edited August 28, 2013 by kylomas

[13lack13lade]

Nope you understand correctly... they are country codes(not sure how many, quite a few i would imagine) and fall under the 4 regions/teams: Asia (so would have all the asia country codes) Europe(european and african country codes) Australia (just australia) South Pacific (all the islands such as Fiji, Samoa and New Zealand etc)

The data showing on the spreadsheet relates to errors in our system and there for needs to be fixed so it needs to be emailed to the appropriate team.

So it will only ever be 1 email sent, with the possibility of up to 4 Recipients (each team) just depending on which Country Codes are returned to console.

Will come up with a table and have it here first thing tomorrow morning!

Thank you so much for your help and patience ~ you make it look easy 

[kylomas]

No problem and Good Night (2AM my time).

For REAL Excel help, water is the man!

[water]

Me? You are sure?

I'm just getting my feet wet with Excel. But if my rewriting of the Excel UDF lets me become a "guru" like for Outlook, AD and Word, it will still take a few years to be able to answer all your Excel related questions

[13lack13lade]

I have quickly thrown this together from the codes i got out of our system, Still finalizing which codes fall under which team but ive got quite a few there hopefully enough to get a test/template going...

sorry it took so long, had to manually write down each code and destination lol -.-"

DestinationBreakDown.xlsx

[kylomas]

Cool, the "proof of concept" is ready to go...just needed your data.

edit: If you are just now organizing this data, how was this done in the past?

Edited August 29, 2013 by kylomas

[13lack13lade]

There is a list somewhere but alot of the stuff like what im doing here is going from manual process to automating everything... with people moving on and changing roles etc the whereabouts of the files/information has been lost...

I believe that the person who manually used to do it just knew the codes from memory (have tried to see if she had a list but no luck finding one)

You are both Guru Gods and i shall worship thee   

 

The list i posted up is correct, the other country codes aren't classed as our region and belong to another region and wont even show up on the reports that come through so no need to have those values incorporated at all.

Edited August 29, 2013 by 13lack13lade