Sign in to follow this  
Followers 0
dar100111

Pasting an array into a table in email

4 posts in this topic

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!

#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

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by markyrocks

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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")

AutoIt Absolute Beginners    Require a serial    Pause Script    Video Tutorials by Morthawt   ipify 

Monkey's are, like, natures humans.

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  
Followers 0