Jump to content
Sign in to follow this  

Pasting an array into a table in email

Recommended Posts

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.


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

 AutoIt Version:
 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()

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
                    $finalstartrow = $finalstartrow + 1 ;dynamic to insert another row with 2darrayinsert
                $emailarray = _ArrayToClip($finalarray);;item I need to paste into email below.
                $oItem = _OL_ItemCreate($oOutlook, $olMailItem, "", "", "Subject="&$subjectline&" for "&$podoffice)
            $oItem.BodyFormat = $olFormatPlain
            $sBody = $oItem.Body
            $oItem.Body = $emailbody &$emailarray&@lf& $sBody &@lf&$automatednote
$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

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")
#EndRegion ### END Koda GUI section ###
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
        Case $Button1
            $emailbody = ControlGetText("", "", $body)
            $Button1 = 1
Until $Button1 = 1

;;;;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)
Global $resultsarray = _ArrayUnique($1darray);build an array to have 1 row for each unique branch
_CreateEmailfromUniqueArray($resultsarray, $aArray, $emailbody)

Missing POD template.xlsx

Share this post

Link to post
Share on other sites

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

for $col = 0 to ubound($finalarray) -1
$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 = ''
    For $col = 0 To UBound($finalarray) - 1
        $Output &= $finalarray[$row][$col] & @TAB
    $Output &= @CRLF
    $row += 1
Until $row = UBound($finalarray) - 1


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  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Create New...