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

 AutoIt Version: 3.3.14.1
 Author:         Armin Wiesmüller

 Script Function:
	providing functions for mass creating and sending personalized mails

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

#include <Excel.au3>
#include <File.au3>
#include <Array.au3>
#include <IE.au3>
#include <MsgBoxConstants.au3>
#include <Word.au3>
#include <AutoItConstants.au3>
#include "Source\OutlookEX.au3"
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;Const $wdFormatPDF = 17

; Register the default Word.au3 COM Error Handler
;_WordErrorHandlerRegister()

; ###############################################################################
; ### T # E # S # T # - # A # R # E # A ### 5 # 1 ##### A # N # F # A # N # G ###
; ###############################################################################

; Überlegungen
; Anzahl Spalten = Anzahl Search and Replaces + 1
; Anzahl Zeilen = Anzahl Adressaten + 1

$adressatenliste = _pmailexcelreadfile(@ScriptDir&"\liste.xlsx", "A1:A10")
$searchandreplacearray = _pmailexcelreadfile(@ScriptDir&"\liste.xlsx", "B1:C10")
;_ArrayDisplay($adressatenliste)
;_ArrayDisplay($searchandreplacearray)

$j=0
While $j <= 8
	;Local $aGrid[][] = [[$searchandreplacearray[0][0], [$searchandreplacearray[0][1]]], [$searchandreplacearray[$j+1][0], $searchandreplacearray[$j+1][1]]]
	Local $aGrid[2][2]
    $aGrid[0][0] = $searchandreplacearray[0][0]
    $aGrid[0][1] = $searchandreplacearray[$j+1][0]
    $aGrid[1][0] = $searchandreplacearray[0][1]
    $aGrid[1][1] = $searchandreplacearray[$j+1][1]
    FileCopy(@ScriptDir&"\Einladung_Test.docx", @TempDir&"\PMail\Einladung_Test.docx", $FC_OVERWRITE + $FC_CREATEPATH)
	FileCopy(@ScriptDir&"\Einladung_Test.oft", @TempDir&"\PMail\Einladung_Test.oft", 9)
	Sleep(2000)
    _ArrayDisplay($aGrid)
	_pmailwordreplacetext(@TempDir&"\PMail\Einladung_Test.docx", $aGrid)
	Sleep(2000)
	_pmailwordsaveaspdf(@TempDir&"\PMail\Einladung_Test.docx", @TempDir&"\PMail\Einladung_Test.pdf")
	Local $attacharray[1]=[@TempDir&"\PMail\Einladung_Test.pdf"]
	Sleep(2000)
	; _pmailoutlookpreparemail($recipientadress, $subject, $snrarray, $oftfilepath, $attachmentsarray, $bcc="")
	_pmailoutlookpreparemail($adressatenliste[$j], "Exklusive Einladung zum Meet'n Greet für %$%Vorname%$% %$%NACHNAME%$%", $aGrid, @TempDir&"\PMail\Einladung_Test.oft", $attacharray, "some-mailadress@googlemail.com")
	Sleep(2000)
	_pmailoutlooksendmail(@TempDir&"\PMail\Einladung_Test.oft")
	Sleep(2000)
	DirRemove(@TempDir&"\PMail\", $DIR_REMOVE)
WEnd

; ###############################################################################
; ### # T # E # S # T # - # A # R # E # A ### 5 # 1 ########### E # N # D # E ###
; ###############################################################################

; ###############################################################################
; ### USER DEFINED FUNCTIONS - DO NOT CHANGE IF NOT SURE WHAT YOU'RE DOING!!! ###
; ###############################################################################

; #FUNCTION# ====================================================================================================================
; Name ..........: _pmailexcelreadfile
; Description ...: Reads the content of a defined range from an excelfile to an array
; Syntax ........: _readexcelfile($excelfilepath, $range)
; Parameters ....: $excelfilepath       - string: path to the excelfile. Will not be checked in this udf for existance!
;                  $range               - string: a range in the form "A1:B10".
; Return values .: $adressates          - array of strings: including the information read from the cells in the range of the excelfile.
; Author ........: Armin Wiesmüller
; Modified ......: 02.02.2017
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......: No
; ===============================================================================================================================
Func _pmailexcelreadfile($excelfilepath, $range)
	Local $oExcel =_Excel_Open()
	$data = _Excel_BookOpen($oExcel,$excelfilepath)
	$extracted = _Excel_RangeRead($data, Default, $range)
	_Excel_BookClose($data)
	ProcessClose("Excel.exe")
	;If IsArray($adressates) Then _ArrayDisplay($adressates)
	If Not IsArray($extracted) Then
		MsgBox(16,"Error!","Excelfile could not be read to an array. Please check your excelfile and your range!")
	Else
		Return $extracted
	EndIf
EndFunc

; #FUNCTION# ====================================================================================================================
; Name ..........: _pmailwordreplacetext
; Description ...:
; Syntax ........: _pmailwordreplace($wordfilepath, $snrarray)
; Parameters ....: $wordfilepath        - string: path to the wordfile that will be modified.
;                  $snrarray            - array of strings: containing search ([x][0]) and corresponding replace [x][1] patterns.
; Return values .: None (works on the given file)
; Author ........: Armin Wiesmüller
; Modified ......: 02.02.2017
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......: No
; ===============================================================================================================================
Func _pmailwordreplacetext($wordfilepath, $snrarray)
	; Create application object
	Local $oWord = _Word_Create(False)
	If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Word UDF: _Word_DocFindReplace Example", _
        "Error creating a new Word application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
	; Open document
	Local $oDoc = _Word_DocOpen($oWord, $wordfilepath)
	If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Word UDF: _Word_DocFindReplace Example", _
			"Error opening '"&$wordfilepath&"'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
	; *****************************************************************************
	; Replace operation
	; *****************************************************************************
	;DEBUG!!!
	;_ArrayDisplay($snrarray)
	$snrarraysize=UBound($snrarray)
	$i=0
	While($i < $snrarraysize)
		_Word_DocFindReplace($oDoc, $snrarray[$i][0], $snrarray[$i][1])
		If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Word UDF: _Word_DocFindReplace Example", _
				"Error replacing text in the document." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
		$i+=1
    WEnd
    ;MsgBox($MB_SYSTEMMODAL, "Word UDF: _Word_DocFindReplace Example", "Paragraph control character successfully replaced.")
    ; Save document
    _Word_DocSave($oDoc)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Word UDF: _Word_DocSave Example", _
			 "Error saving the Word document." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    ;MsgBox($MB_SYSTEMMODAL, "Word UDF: _Word_DocSave Example", "Document successfully saved as '" & _
	; 	  $oDoc.FullName & "'")
	_Word_DocClose($oDoc)
	_Word_Quit($oWord)
EndFunc

; #FUNCTION# ====================================================================================================================
; Name ..........: _pmailwordsaveaspdf
; Description ...:
; Syntax ........: _pmailwordsaveaspdf($wordfilepath, $pdffilepath)
; Parameters ....: $wordfilepath        - string: path and filename of the word-document to be converted.
;                  $pdffilepath         - string: path and filename of the destination file.
; Return values .: None (will create a new file in given destination)
; Author ........: Armin Wiesmüller
; Modified ......: 02.02.2017
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......: No
; ===============================================================================================================================
Func _pmailwordsaveaspdf($wordfilepath, $pdffilepath)
	; Create application object
	Local $oWord = _Word_Create(False)
	If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Word UDF: _Word_DocFindReplace Example", _
        "Error creating a new Word application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
	; Open document
	Local $oDoc = _Word_DocOpen($oWord, $wordfilepath)
	If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Word UDF: _Word_DocFindReplace Example", _
		"Error opening '"&$wordfilepath&"'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Word_DocSaveAs($oDoc, $pdffilepath, $wdFormatPDF)
#CS     If @error And $WordComErrorNumber = -2147352567 Then
;         MsgBox(0, "Error", 'You first need to install the "2007 Microsoft Office Add-in: Microsoft Save as PDF".' & @CRLF & @CRLF & _
;                 "The download link will now be opened for you.")
;         _IECreate("http://www.microsoft.com/downloads/details.aspx?FamilyId=F1FC413C-6D89-4F15-991B-63B07BA5F2E5&displaylang=en", 0, 1, 0)
;     EndIf
 #CE
	_Word_DocClose($oDoc)
	_Word_Quit($oWord)
EndFunc

; #FUNCTION# ====================================================================================================================
; Name ..........: _pmailoutlookpreparemail
; Description ...:
; Syntax ........: _pmailoutlookpreparemail($recipientadress, $subject, $snrarray, $oftfilepath,
;                  $attachmentsarray[, $bcc = ""])
; Parameters ....: $recipientadress     - string: mail adress of the recipient - noch checked for containing an @ and at least one dot.
;                  $subject             - string: containing the subject including replacemant patterns.
;                  $snrarray            - array of strings: containing search ([x][0]) and corresponding replace [x][1] patterns.
;                  $oftfilepath   - string: path to the template file to fill up.
;                  $attachmentsarray    - array of strings: containing the paths to the files to be attached.
;                  $bcc                 - [optional] string: bcc-adress to send the mails to - e.g. for archive reason. Default is "".
; Return values .: None
; Author ........: Armin Wiesmüller
; Modified ......: 02.02.2017
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......: No
; ===============================================================================================================================
Func _pmailoutlookpreparemail($recipientadress, $subject, $snrarray, $oftfilepath, $attachmentsarray, $bcc="")
	; Create a Copy of the file in the destination location
	; OLD CODE - ADD BEFORE CALLING THIS FUNCTION: FileCopy($oftsourcefilepath, $oftdestinationfilepath, $FC_OVERWRITE + $FC_CREATEPATH)
	$snrarraysize=UBound($snrarray)
	$i=0
	While($i < $snrarraysize)
		_ReplaceStringInFile($oftfilepath, $snrarray[$i][0], $snrarray[$i][1])
		$subject = StringReplace($subject, $snrarray[$i][0], $snrarray[$i][1], 0, $STR_CASESENSE)
		$i+=1
	WEnd
	$oOL = _OL_Open()
	$oItem = _OL_ItemCreate($oOL, $olMailItem, $oftfilepath)
	If @error <> 0 Then Exit MsgBox(16, "OutlookEX UDF: _OL_ItemCreate Example Script", "Error creating a mail in folder 'Outlook-UDF-Test\TargetFolder\Mail'. @error = " & @error & ", @extended = " & @extended)
	; Get current item
	$oItem0 = $oOL.ActiveInspector.CurrentItem
	If @error <> 0 Then Exit MsgBox(16, "OutlookEX UDF", "Error accessing current Inspector. @error = " & @error & ", @extended = " & @extended)
	; Get Subject property
	$aProperties = _OL_ItemGet($oOL, $oItem0, Default, "Subject")
	If @error <> 0 Then Exit MsgBox(16, "OutlookEX UDF", "Error accessing current item. @error = " & @error & ", @extended = " & @extended)
	; Modify Subject property
	$sSubject = $aProperties[1][1] & " - Changed Subject"
	_OL_ItemModify($oOL, $oItem, Default, "Subject=" & $sSubject)
	If @error <> 0 Then Exit MsgBox(16, "OutlookEX UDF", "Error accessing current item. @error = " & @error & ", @extended = " & @extended)
	; add recipient and probably also bcc
	$oItem1 = _OL_ItemRecipientAdd($oOL, $oItem, Default, $olTo, $recipientadress)
	If @error <> 0 Then Exit MsgBox(16, "OutlookEX UDF: _OL_ItemRecipientAdd Example Script", "Error adding member to distribution list in folder 'Outlook-UDF-Test\SourceFolder\Calendar'. @error = " & @error & ", @extended = " & @extended)
	If $bcc <> "" Then
		$oItem2 = _OL_ItemRecipientAdd($oOL, $oItem, Default, $olBCC, $bcc)
		If @error <> 0 Then Exit MsgBox(16, "OutlookEX UDF: _OL_ItemRecipientAdd Example Script", "Error adding member to distribution list in folder 'Outlook-UDF-Test\SourceFolder\Calendar'. @error = " & @error & ", @extended = " & @extended)
	EndIf
	If(UBound($attachmentsarray)>0) Then
		_OL_ItemAttachmentAdd($oOL, $oItem, Default, $attachmentsarray)
		If @error <> 0 Then Exit MsgBox(16, "OutlookEX UDF: _OL_ItemAttachmentAdd Example Script", "Error adding attachment to task item in folder 'Outlook-UDF-Test\SourceFolder\Tasks'. @error = " & @error & ", @extended = " & @extended)
	EndIf
	_OL_ItemSave($oOL, $oItem, Default, $oftfilepath, $olTemplate, 3)
EndFunc

; #FUNCTION# ====================================================================================================================
; Name ..........: _pmailoutlooksendmail
; Description ...:
; Syntax ........: _pmailoutlooksendmail($oftfilepath)
; Parameters ....: $oftfilepath         - string: path to the oft file to be sent.
; Return values .: None
; Author ........: Armin Wiesmüller
; Modified ......: 02.02.2017
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......: No
; ===============================================================================================================================
Func _pmailoutlooksendmail($oftfilepath)
	$oOL = _OL_Open()
	$oItem = _OL_ItemCreate($oOL, $olMailItem, $oftfilepath)
	If @error <> 0 Then Exit MsgBox(16, "OutlookEX UDF: _OL_ItemCreate Example Script", "Error creating a mail in folder 'Outlook-UDF-Test\TargetFolder\Mail'. @error = " & @error & ", @extended = " & @extended)
	; Send the mail
	_OL_ItemSend($oOL, $oItem)
	; Close Outlook connection
	_OL_Close($oOL)
EndFunc

;currently not used...
Func _pmailoutlooksendmail2($recipientadress, $recipientname, $subject, $bodytext, $bodyformat, $attachments, $bcc)
	If $bodyformat = "text" Then

	ElseIf $bodyformat = "html" Then

	ElseIf $bodyformat = "rtf" Then

	Else
		MsgBox(16,"Error!","$bodyformat not set! Mail has not been sent!")
	EndIf
	$oOL = _OL_Open()
	$oItem = _OL_ItemCreate($oOL, $olMailItem, "", "", "Subject=TestMail", "BodyFormat=" & $olFormatHTML, "HTMLBody=Bodytext in <b>bold</b>.")
	_OL_ItemRecipientAdd($oOL, $oItem, Default, $olTo, "Jon Doe")
	; Send the mail
	_OL_ItemSend($oOL, $oItem)
	; Close Outlook connection
	_OL_Close($oOL)
EndFunc