Sign in to follow this  
Followers 0
dar100111

Formatting an HTML email with table with Outlook help.

2 posts in this topic

#1 ·  Posted (edited)

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.

#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 by dar100111

Share this post


Link to post
Share on other sites



Solved it sorry.  just switched format to HTMLBody in

 $sBody = $oItem.Body
 

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