Formatting an HTML email with table with Outlook help.

[dar100111]

Hey All,

I make more progress on this script everyday and have had trouble figuring out how to get a clean format to put a table into an email.

Has anyone had experience with Outlook in creating an HTML email that will insert the html table in the middle of a string? 

I may not be formatting it correctly but the email spits off with the orange at the bottom of my post as the body with all the tags but not sure exactly what my problem might be.

The script is at the bottom and excel tool I use to pull the info.

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 $arraystring = ""
Global $oOutlook = _OL_Open()
Global $automatednote = @lf&"****PLEASE NOTE THIS EMAIL IS AUTOGENERATED FROM ORACLE NMC****"

Func _ArrayToHtml2D(Const ByRef $avArray, $attrib = 'border="1"', $iStart = 0, $iEnd = 0)
 If Not IsArray($avArray) Then Return SetError(1, 0, "")
 If UBound($avArray, 0) <> 2 Then Return SetError(2, 0, "")

 Local $sResult, $iUBound = UBound($avArray) - 1
 Local $row, $sDelimCol = "</td>" & @CRLF, $sDelimRow = '</tr>' & @CRLF

 ; Bounds checking
 If $iEnd < 1 Or $iEnd > $iUBound Then $iEnd = $iUBound
 If $iStart < 0 Then $iStart = 0
 If $iStart > $iEnd Then Return SetError(3, 0, "")

 $sResult = '<table ' & $attrib & '>' & @CRLF

 ; Combine
 For $i = $iStart To $iEnd ; rows
    $row = '<tr>' & @CRLF
    For $j = 0 To UBound($avArray,2) - 1 ; columns
        $row &= '<td>' & $avArray[$i][$j] & $sDelimCol
    Next
    $sResult &= $row & $sDelimRow
 Next

 Return $sResult & '</table>' & @CRLF
EndFunc


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
                $arraystring = _ArrayToHtml2D($finalarray)
                ;For $f = 1 to UBound($finalarray) - 1
                ;   ;MsgBox("", "", $f)
                ;   For $co = 1 to $columncounter
                ;       $arraystring = $arraystring & $finalarray[$f][$co]& @TAB&@TAB
                ;   Next
                ;   $arraystring = $arraystring&@LF&@lf
                ;Next
                $emailarray = _ArrayToClip($finalarray);;item I need to paste into email below.
                $oItem = _OL_ItemCreate($oOutlook, $olMailItem, "", "", "Subject="&$subjectline&" for "&$podoffice)
            $oItem.BodyFormat = $olFormatHtml;replace Html with plain for text only
            $oItem.GetInspector
            $sBody = $oItem.Body
            $oItem.Body = $emailbody &@LF&$arraystring&@lf& $sBody &@lf&$automatednote
            $oItem.Display
            $arraystring = ""
        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.xlsm")
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)

Please update POD's into expo for following shipments

<table border="1">
<tr>
<td>1</td>
<td>3</td>
<td></td>
<td></td>
</tr>
<tr>
<td></td>
<td>POD office</td>
<td>Sales Order</td>
<td>Service Complete</td>
</tr>
<tr>
<td></td>
<td>PHX</td>
<td>313158-1-4</td>
<td>12/13/16</td>
</tr>
<tr>
<td></td>
<td>PHX</td>
<td>313158-1-5</td>
<td>12/13/17</td>
</tr>
<tr>
<td></td>
<td>PHX</td>
<td>313158-1-6</td>
<td>12/13/18</td>
</tr>
</table>

 
 
 
Daniel Rowe
Network Analyst, Oracle Network Management Center

 
 
 
 

****PLEASE NOTE THIS EMAIL IS AUTOGENERATED FROM ORACLE NMC****

 

Missing POD template.xlsx

Edited December 17, 2013 by dar100111

[dar100111]

Solved it sorry.  just switched format to HTMLBody in

 $sBody = $oItem.Body