Pasting an array into a table in email

[dar100111]

Hey All!

I've been playing around with making a unique array for all instances in an excel sheet to email out to certain users and I'm trying to find a way to take my unique array which is $emailarray in this script and have it go into the body of the email like a table.  I know I can't paste the array straight in there but does anyone have any ideas?  I attached the sheet if you wanted to get an idea of what it's supposed to accomplish.

Thanks!

expandcollapse popup

#cs ----------------------------------------------------------------------------

 AutoIt Version: 3.3.8.1
 Author:         Daniel Rowe

 Script Function:
    Mass Email Tool

#ce ----------------------------------------------------------------------------

; Script Start - Add your code below here
#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <INet.au3>
#include <excel.au3>
#include <array.au3>
#include <OutlookEX.au3>
#include <_2DArrayInsertRow.au3>

Global $oOutlook = _OL_Open()
Global $automatednote = @lf&"****PLEASE NOTE THIS EMAIL IS AUTOGENERATED FROM ORACLE NMC****"

Func _CreateEmailfromUniqueArray($p_array_to_loop, $p_array_to_search, $p_emailbody)

        $searchcount = UBound($p_array_to_loop) - 1
        $rowcounter = $p_array_to_search[0][0]
        Global $columncounter = $p_array_to_search[0][1]

    For $i = 1 To $searchcount Step 1
        $podoffice = StringLeft($p_array_to_loop[$i],3)
        ;MsgBox("", "", $podoffice)
        Global $found = _ArrayFindAll($p_array_to_search, $podoffice, 0,0,0,1,1)
        ;_ArrayDisplay($found);;not showing up yet
                Global $finalarray = _ExcelReadSheetToArray($oExcel, 1, 1, 1, $columncounter)
                Global $finalstartrow = 2
                Global $finalstartcolumn = 1
                For $r = 1 to UBound($found) Step 1;add rows to final unique array depending on unique instances
                    _2DArrayInsertRow($finalarray, $finalstartrow)
                        For $c = 1 To $columncounter Step 1 ;assign array indeces with variable from original search array
                            $data = $p_array_to_search[$found[$r - 1]][$c]
                            $finalarray[$finalstartrow][$c] = $data
                        Next
                    ;_ArrayDisplay($finalarray)
                    $finalstartrow = $finalstartrow + 1 ;dynamic to insert another row with 2darrayinsert
                Next
                $emailarray = _ArrayToClip($finalarray);;item I need to paste into email below.
                $oItem = _OL_ItemCreate($oOutlook, $olMailItem, "", "", "Subject="&$subjectline&" for "&$podoffice)
            $oItem.BodyFormat = $olFormatPlain
            $oItem.GetInspector
            $sBody = $oItem.Body
            $oItem.Body = $emailbody &$emailarray&@lf& $sBody &@lf&$automatednote
            $oItem.Display
    Next
EndFunc
;;;;;;;;;;;;;;;;;;;;;;;;;Start
$missingpodsubject = "Missing Oracle POD's"
$template = InputBox("Mass Email", "Select Template"&@lf&@lf&"1.   Missing POD's"&@lf&"2.   ..."&@lf&"3.    ..."&@lf&"4.    ..."&@lf&"5.   Custom Template","", "", 200, 220)
If @error = 1 Then Exit
If $template = 1 Then
$subjectline = $missingpodsubject
$emailbody = "Please update POD's into expo for following shipments"&@lf&@lf
EndIf

If $template = 2 Then Exit

If $template = 3 Then Exit

If $template = 4 Then Exit

If $template = 5 then
#Region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Enter Email Body", 610, 438, 192, 124)
$Label1 = GUICtrlCreateLabel("Enter Email Body Below", 56, 16, 497, 41, BitOR($SS_CENTER,$SS_CENTERIMAGE,$SS_SUNKEN))
GUICtrlSetFont(-1, 24, 400, 0, "MS Sans Serif")
$body = GUICtrlCreateEdit("", 24, 110, 561, 240)
$Button1 = GUICtrlCreateButton("Finish", 216, 368, 185, 57)
GUICtrlSetFont(-1, 12, 400, 0, "MS Sans Serif")
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###
Do
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1
            $emailbody = ControlGetText("", "", $body)
            $Button1 = 1
    EndSwitch
Until $Button1 = 1
EndIf


;;;;execute results of template select
Global $oExcel = _ExcelBookOpen( @ScriptDir & "\Missing POD template.xlsx")
Global $aArray = _ExcelReadSheetToArray($oExcel);This will contail all the info to send to the branches
Global $1darray = _ExcelReadArray($oExcel, 2, 1,UBound($aArray) - 2, 1)
;_ArrayDisplay($aArray)
;_ArrayDisplay($1darray)
Global $resultsarray = _ArrayUnique($1darray);build an array to have 1 row for each unique branch
;_ArrayDisplay($resultsarray)
_CreateEmailfromUniqueArray($resultsarray, $aArray, $emailbody)

Missing POD template.xlsx

[markyrocks]

Idk I found this '?do=embed' frameborder='0' data-embedContent>>  I see that you have the data in the clipboard already.  if that doesn't work youll need to use a for statement

do
for $col = 0 to ubound($finalarray) -1
send($finalarray[$row][$col])
next
$row += 1
until $row = ubound($finalarray)-1

something like that..... to make rows and columns there needs to be tabs and line breaks added in there

also from reading here http://www.autoitscript.com/autoit3/docs/libfunctions/_ArrayToClip.htm

looks like from the documentation $emailarray will only be equal to 1 or 0 it doesnt save the data to an array just to the clipboard.  least that's what i gather 

Edited December 17, 2013 by markyrocks

[dar100111]

Hey Marky!

Ok I did that and it looks to be working good.  I just need to find a way to format with my tabs between the columns but I should be able to check string length on the data to do that.

Thanks for idea!

[JohnOne]

Or similarly...

$Output = ''
Do
    For $col = 0 To UBound($finalarray) - 1
        $Output &= $finalarray[$row][$col] & @TAB
        ;Send($finalarray[$row][$col])
    Next
    $Output &= @CRLF
    $row += 1
Until $row = UBound($finalarray) - 1

ClipPut($Output)
Send("^v")