Need help detecting date formats in Array pulled from Excel

[dar100111]

Hey All,

I have a mass email tool that I made that the user can paste in values from a report that will group the rows according to who they need to get sent in the first column of the excel tool and send off emails with all the rows that pertain to that contact in an html table.  I want to leave it so that I don't have to hardcode certain rows to be dates, just in case the user wants to change their format.  I just want them to be able to paste any column headers (aside from the first due to importance).

Obviously when the date from excel is pulled it's converted to the date format you see below in the "Date" column.  How can I safely detect that a column is a date?  I'm sure there's a date format I could look into after that and use a for loop to convert to a more friendly date format to read?  Any suggestions greatly appreciated. 

The script is working well I just wanted to be able to enhance it a bit.  If anyone else has a use for it, awesome!

If you try it out, you'll need to set your email address to all the contacts on the tool.

 

Example of how the Email looks below where it found "SFO" in multiple rows and grouped together.  It just checks the contact list on tab to check who to send it too.

Hello,

Please advise status on the below shipments and relay any delays that are causing or could cause a shipment to not deliver by the expected delivery date.

Please update any appointments for delivery if already known.

Thanks for your help and have a wonderful day!

 

Contact Invoice Int.  Reference    Date

SFO        12345690    88881516    20140417000000

SFO        12345693    88881519    20140331000000

 

expandcollapse popup

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Icon=..\EIflag.ico
#AutoIt3Wrapper_Outfile=Accounting Bulk Email.exe
#AutoIt3Wrapper_Add_Constants=n
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
; *** Start added by AutoIt3Wrapper ***
#include <OutlookExConstants.au3>
; *** End added by AutoIt3Wrapper ***
#cs ----------------------------------------------------------------------------

 AutoIt Version: 3.3.8.1
 Author:         Daniel Rowe

 Script Function:
    Mass Email Tool

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

; Script Start - Add your code below here
#include <String.au3>
#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>


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

Func _2DArrayInsertRow(ByRef $avArray, $iRow)
    If Not IsArray($avArray) Then Return SetError(1, 0, 0)
    If UBound($avArray, 0) <> 2 Then Return SetError(2, 0, 0)

    ; Add 1 row to the array
    Local $iUBoundRow = UBound($avArray) + 1
    Local $iUBoundCol = UBound($avArray, 2)
    ReDim $avArray[$iUBoundRow][$iUBoundCol]

    ; Move all entries down until the specified position
    For $i = $iUBoundRow - 1 To $iRow + 1 Step -1
        For $j = 0 To $iUBoundCol - 1
            $avArray[$i][$j] = $avArray[$i - 1][$j]
        Next
    Next

    Return $iUBoundRow
EndFunc   ;==>_2DArrayInsertRow

Func _ArrayToHtml2D(Const ByRef $avArray, $attrib = 'border="10"', $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, $p_contact_array)

        $searchcount = UBound($p_array_to_loop) - 1
        $rowcounter = $p_array_to_search[0][0]
        Global $columncounter = $p_array_to_search[0][1]
        ProgressOn("Bulk Email Progress", "Sending Emails", "0%")
    For $i = 1 To $searchcount Step 1
        $podoffice = StringLeft($p_array_to_loop[$i],3)
        $a = int(($i / $searchcount)*100)
        ProgressSet($a, "Sending Email "&$i&" of "&$searchcount&" to "&$podoffice)
        Global $found = _ArrayFindAll($p_array_to_search, $podoffice, 0,0,0,1,1)

                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
                _ArrayDelete($finalarray, 0)
                $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.
                $contactfind = _ArraySearch($p_contact_array, $podoffice, 0, 0, 0, 1)
                ;MsgBox("", "contact list row for "&$podoffice, $contactfind)
                $emailadresses = $p_contact_array[$contactfind][$productcheck];
                $oItem = _OL_ItemCreate($oOutlook, $olMailItem, "", "", "Subject="&$subjectline&" for "&$podoffice, "To="&$emailadresses)
            $oItem.BodyFormat = $olFormatHtml;replace Html with plain for text only
            $oItem.GetInspector
            $sBody = @crlf&@lf&$oItem.HTMLBody;replace with Body for plain
            $oItem.HTMLBody = $emailbody& $arraystring & $sBody &$automatednote
            $oItem.Send;change to send to send Manually
            $arraystring = ""
        Next
        ProgressSet(100, "Emails Sent", "Complete")
        Sleep(2500)
        ProgressOff()
EndFunc
;;;;;;;;;;;;;;;;;;;;;;;;;Start


$template = InputBox("NMC Mass Email", "Select Template"&@lf&@lf&"1.   Shipment Status Request","", "", 200, 180)
If @error = 1 Then Exit
If $template = 1 Then
$subjectline = "Oracle Shipment Status Request"
$productcheck = 3; transcon inbound column in contact array so array will stop on this column
Global $emailbody = "<BR>Hello,<BR><BR>Please advise status on the below shipments and relay any delays that are causing or could cause a shipment to not deliver by the expected delivery date.<BR><BR>Please update any appointments for delivery if already known.<BR><BR>  Thanks for your help and have a wonderful day!<BR><BR>"
$oExcel = _ExcelBookOpen( @ScriptDir & "\Bulk Email Tool.xlsx")
_ExcelSheetActivate($oExcel, "Contacts")
Global $contactarray = _ExcelReadSheetToArray($oExcel, 2, 1, 0, $productcheck)
;_ArrayDisplay($contactarray)
EndIf

;;;;execute results of template select
_ExcelSheetActivate($oExcel,"Info")
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, $contactarray)

Bulk Email Tool.xlsx

Edited April 30, 2014 by dar100111

[mikell]

Maybe _DateIsValid()  ?

#include <Date.au3>
#include <MsgBoxConstants.au3>

$string = "20140417000000"
Local $sDate = StringRegExpReplace($string, '(\d{4})(\d\d)(\d\d)(\d\d)(\d\d)(\d\d)', '$1/$2/$3 $4:$5:$6')
; msgbox(0,"", $sDate)

If _DateIsValid($sDate) Then
    MsgBox($MB_SYSTEMMODAL, "Valid Date", "The specified date is valid.")
Else
    MsgBox($MB_SYSTEMMODAL, "Valid Date", "The specified date is invalid.")
EndIf

[dar100111]

Thanks Mike! I think I can make this work.

Edited April 30, 2014 by dar100111