Search the Community

I was in a hurry to convert 200+ address book from Google Contacts (G Suite, contacts exported in .CSV with Google Takeout, but the contact format is the same for every Google Gmail account) to Outlook contacts, so I developed this quick and very dirty script. Starting from a .CSV exported from Google, you can run the script and you have the contacts ported directly (no outlook import) on the Outlook Address book. In my experience, the fields of Google contacts are not fixed in number and maybe also in position, so I made the choice to import only certain fields to Outlook, scanning every line. These fields, exact google names: "Given Name" "Family Name" "E-mail 1 - Value" "E-mail 2 - Value" "Phone 1 - Value" "Phone 2 - Value" "Website 1 - Value" "Notes" "Name" "Address 1 - Formatted" Also the lines of google contacts can be weird as CR and LF are not "regular". I include my code, (worked with no errors on a lot of conversions) and also a very small fake adress book with examples of problems I encountered and resolved. #Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_Icon=Icone\Win98-ico\script_file_teal.ico #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** ;GOA2OAB ;G.oogle A.ddress B.ook 2 (to) O.utlook A.ddress B.ook ;(C) NSC 2019 ; convert a google (takeout) address book to an Outlook Address Book #include <File.au3> #include <Array.au3> #include <_Gollog.au3> #include <MsgBoxConstants.au3> #include <FileConstants.au3> #include <MsgBoxConstants.au3> #include <WinAPIFiles.au3> #include <String.au3> #include <Math.au3> ; ***************************************************************************** #include <OutlookEX.au3> ; super perla pearl outlook create contacts !!!!! ; ***************************************************************************** Global $oItem Global $oOutlook = _OL_Open() If @error <> 0 Then MsgBox(16, "OutlookEX UDF", "Error creating a connection to Outlook. @error = " & @error & ", @extended = " & @extended) ; ***************************************************************************** Global $ver = "V.0.8192", $tempdir = "c:\GAB2OAB_temp", $File2convert, $aRetArray, $workfile = $tempdir & "\work-GAB2OAB.csv", $workfile2 = $tempdir & "\WORK2-gab2oab.csv", $FINALab = $tempdir & "\NEW-ab.csv", $A3clean[0], $A4clean[0], $A5clean[0] ; Gollog Autogenerated Gui $mygui = "G.oogle A.ddress B.ook 2 (to) O.utlook A.ddress B.ook " & $ver & " NSC 2019" gollog_ini($mygui, 600, 200, 50, 50, "0x0033cc", "0xA8D9BD", "courier", 8, 600, @ScriptDir & "\GAB2OAB_log.txt") ; initialization with colors and font choice, more options see the UDF Gollog(">>>>>>>> GAB2OAB " & $ver) $File2convert = FileOpenDialog("Select Google Address Book File", @DesktopDir & "\", "CSV (*.csv)", BitOR($FD_FILEMUSTEXIST, $FD_MULTISELECT)) Gollog("selected " & $File2convert) If Not FileExists($tempdir) Then Gollog("creating " & $tempdir) DirCreate($tempdir) EndIf If FileExists($workfile) Then Gollog("deleting " & $workfile) FileDelete($workfile) EndIf If FileExists($workfile2) Then Gollog("deleting " & $workfile2) FileDelete($workfile2) EndIf If FileExists($FINALab) Then Gollog("deleting " & $FINALab) FileDelete($FINALab) EndIf cleanfile() readfile() cleanfile2() readfile2() write2Outlook() Exit Func write2Outlook() Gollog("---> Assigning Fields...") Sleep(300) Local $FirstName = 4 ; initialize with Yomi name - always empty in tested companies, low class workaround Local $LastName = 4 Local $Email1Address = 4 Local $Email2Address = 4 Local $MobileTelephoneNumber = 4 Local $OtherTelephoneNumber = 4 Local $Webpage = 4 Local $Body = 4 Local $Fullname = 4 Local $BusinessAddress = 4 For $z = 0 To UBound($A5clean, 2) - 1 ;If $A5clean[2][$z] = "Additional Name" Then $MiddleName = $z ; limit @ 10 If $A5clean[1][$z] = "Given Name" Then $FirstName = $z If $A5clean[1][$z] = "Family Name" Then $LastName = $z If $A5clean[1][$z] = "E-mail 1 - Value" Then $Email1Address = $z If $A5clean[1][$z] = "E-mail 2 - Value" Then $Email2Address = $z If $A5clean[1][$z] = "Phone 1 - Value" Then $MobileTelephoneNumber = $z If $A5clean[1][$z] = "Phone 2 - Value" Then $OtherTelephoneNumber = $z If $A5clean[1][$z] = "Website 1 - Value" Then $Webpage = $z If $A5clean[1][$z] = "Notes" Then $Body = $z If $A5clean[1][$z] = "Name" Then $Fullname = $z If $A5clean[1][$z] = "Address 1 - Formatted" Then $BusinessAddress = $z Next Gollog("Fields Found !") For $i = 2 To UBound($A5clean, 1) - 1 Gollog("Contact 2 Outlook line " & $i) ;Gollog($A5clean[$i][1]) ; Create a contact with first- and lastname ; ***************************************************************************** $oItem = _OL_ItemCreate($oOutlook, $olContactItem, "", "", "FullName=" & $A5clean[$i][$Fullname], "BusinessAddress=" & $A5clean[$i][$BusinessAddress], "FirstName=" & $A5clean[$i][$FirstName], "LastName=" & $A5clean[$i][$LastName], "Email1Address=" & $A5clean[$i][$Email1Address], "Email2Address=" & $A5clean[$i][$Email2Address], "WebPage=" & $A5clean[$i][$Webpage], "MobileTelephoneNumber=" & $A5clean[$i][$MobileTelephoneNumber], "OtherTelephoneNumber=" & $A5clean[$i][$OtherTelephoneNumber], "Body=" & $A5clean[$i][$Body]) If @error <> 0 Then Exit MsgBox(16, "OutlookEX UDF: _OL_ItemCreate ", "Error creating a contact'. @error = " & @error & ", @extended = " & @extended) ; Add a picture to the contact ;$oItem.AddPicture(@ScriptDir & "\The_Outlook.jpg") $oItem.Save() ; ***************************************************************************** Next MsgBox(64, "OutlookEX UDF: the magic was made!", "All contacts successfully created ") _OL_Close($oOutlook) ; close the connection to Outlook EndFunc ;==>write2Outlook Func cleanfile() Gollog('start check for unwanted LF... / final rogue "') Sleep(300) Local $A2Clean = FileReadToArray($File2convert) ;_ArrayDisplay($A2Clean, "original file", Default, 8) Local $dimLIST = UBound($A2Clean) - 1 Local $string2write = "" Local $Astring1 = "" Local $Astring2 = "" Local $salvaAstring1 = "" Local $openLF = 0 ; we are between "LF" across lines For $i = 0 To $dimLIST Gollog('workin on line ' & $i) ;Gollog($A2Clean[$i]) $Astring1 = StringToASCIIArray($A2Clean[$i]) ;_ArrayDisplay($Astring1) Local $DimAstring1 = UBound($Astring1) - 1 If $openLF = 1 Then ;check new line if start with " $Astring2 = StringToASCIIArray($A2Clean[$i]) If $Astring2[0] = "34" Then ; gollog('found initial " ') $openLF = 2 Else Gollog('error new line not starting with "') MsgBox(48, "error", 'error new line not starting with "') EndIf EndIf If $Astring1[$DimAstring1] = "34" Then ; gollog('First line -> Found final "') If $openLF <> 0 Then $salvaAstring1 = $salvaAstring1 & $A2Clean[$i] Else $salvaAstring1 = $A2Clean[$i] EndIf Else _ArrayAdd($A3clean, $salvaAstring1 & $A2Clean[$i]) ;Gollog("adding to cleaned array state:" & $openLF) $openLF = 0 $salvaAstring1 = "" EndIf Next Gollog('end clean from rogue "') ;_ArrayDisplay($A3clean, 'cleaned from rogue "') ;___________________________________P-PP-PP,P------------------------------------------------- Gollog('Start joining lines based on ODD " ') Sleep(300) ;_MathCheckDiv ( $iNum1 [, $iNum2 = 2] ) pari e dispari perla pearl Local $hFileOpen = FileOpen($workfile2, $FO_APPEND) If $hFileOpen = -1 Then MsgBox($MB_SYSTEMMODAL, "", "An error occurred whilst writing the file.") Return False EndIf Local $dimLIST = UBound($A3clean) - 1 Local $Astring1 = "" Local $Astring2 = "" Local $dimSTRING = "" Local $AapiciINriga Local $numeroapiciINriga = 0 Local $sommaNumeroapiciINrighe = 0 Local $salvaAstring1 = "" Local $dispari = 0 For $i = 0 To $dimLIST Gollog("workin' on line " & $i) $Astring1 = StringToASCIIArray($A3clean[$i]) ;_ArrayDisplay($Astring1) ;Gollog($A3clean[$i]) $AapiciINriga = _ArrayFindAll($Astring1, '34') If $AapiciINriga <> -1 Then $numeroapiciINriga = UBound($AapiciINriga) ; Gollog("N° " & $numeroapiciINriga & " apici in riga " & $i) $sommaNumeroapiciINrighe += $numeroapiciINriga If _MathCheckDiv($sommaNumeroapiciINrighe) = 1 Then $salvaAstring1 = $salvaAstring1 & $A3clean[$i] $dispari = 1 Else Local $towrite = $salvaAstring1 & $A3clean[$i] ; gollog("scrivo " & $towrite) FileWriteLine($hFileOpen, $towrite) $salvaAstring1 = "" $sommaNumeroapiciINrighe = 0 $dispari = 0 EndIf Else ; Gollog("zero apici in riga " & $i) If $dispari = 1 Then ;linea vuota o senza apici ma " aperte dispari $salvaAstring1 = $salvaAstring1 & $A3clean[$i] Else Local $towrite = $salvaAstring1 & $A3clean[$i] ; gollog("scrivo " & $towrite) FileWriteLine($hFileOpen, $towrite) $salvaAstring1 = "" $sommaNumeroapiciINrighe = 0 $dispari = 0 EndIf EndIf Next Gollog('END joined lines based on ODD "') ;_ArrayDisplay($A4clean, 'joined lines based on ODD "') FileClose($hFileOpen) EndFunc ;==>cleanfile Func readfile() gollog("reading work file and creating multiD array") Sleep(300) $A4clean = FileReadToArray($workfile2) If @error = 1 Or @error = 2 Then gollog("error reading work2 file " & @error) Else ; _ArrayDisplay($A4clean, "cleaned 1", Default, 8) EndIf EndFunc ;==>readfile Func cleanfile2() Local $dimLIST = UBound($A4clean) - 1 Local $Astring1 = "" Local $conta = 0 Local $salvaconta = 0 Local $dimSTRING = "" $salvalinea = "" Local $standardNumeroVirgole = 0 Local $hFileOpen = FileOpen($FINALab, $FO_APPEND) If $hFileOpen = -1 Then MsgBox($MB_SYSTEMMODAL, "", "An error occurred whilst writing the file.") Return False EndIf Gollog('Checkin , number') For $i = 0 To $dimLIST ;Gollog('workin on line ' & $i) $Astring1 = StringToASCIIArray($A4clean[$i]) ;_ArrayDisplay($Astring1) $dimSTRING = UBound($Astring1) - 1 For $y = 0 To $dimSTRING If $Astring1[$y] = "44" Then ; secondo step , $conta += 1 EndIf Next If $i = 0 Then $standardNumeroVirgole = $conta gollog("----------numero virgole -> " & $standardNumeroVirgole) EndIf If $conta = $standardNumeroVirgole Then FileWriteLine($hFileOpen, $A4clean[$i]) EndIf If $conta > $standardNumeroVirgole Then Gollog('>>> Line ' & $i & ' Virgole' & $conta) $toprecise = $A4clean[$i] $tofinal = '' While StringInStr($toprecise, '"') <> 0 $tofinal = preciseremoval($toprecise) ;super smart $toprecise = $tofinal WEnd FileWriteLine($hFileOpen, $toprecise) EndIf If $conta < $standardNumeroVirgole Then Gollog('<<< Line ' & $i & ' Virgole' & $conta) $salvalinea = $salvalinea & $A4clean[$i] $salvaconta += $conta If $salvaconta = $standardNumeroVirgole Then $salvaconta = 0 FileWriteLine($hFileOpen, $salvalinea) $salvalinea = 0 EndIf EndIf $conta = 0 Next FileClose($hFileOpen) EndFunc ;==>cleanfile2 Func writeWORKfile2() Gollog("writing work file") If _FileWriteFromArray($workfile, $A4clean) <> 1 Then gollog("error writing work file " & @error) EndIf EndFunc ;==>writeWORKfile2 Func readfile2() gollog("reading work file and creating multiD array") If _FileReadToArray($FINALab, $A5clean, Default, ",") = 0 Then MsgBox(48, "errore", @error) EndIf ;_ArrayDisplay($A5clean, "cleaned 2", Default, 8) EndFunc ;==>readfile2 Func preciseremoval($Pclean) ; llllllllllllllllllllllllll ---- pass 1 for ",," $Astring1 = StringToASCIIArray($Pclean) ;_ArrayDisplay($Astring1) $dimSTRING = UBound($Astring1) - 1 Local $salvadelete = "" Local $cond1 = 0 For $y = 0 To $dimSTRING If $cond1 = 1 Then ; , removed so exit from loop If $Astring1[$y] = "34" Then ; terzo step " in chiusura ; gollog('Found right "') If $salvadelete = "" Then $salvadelete = String($y) Else $salvadelete = $salvadelete & ";" & $y EndIf $cond1 = 0 ExitLoop EndIf EndIf If $cond1 = 1 Then If $Astring1[$y] = "44" Then ; secondo step , If $salvadelete = "" Then $salvadelete = String($y) Else $salvadelete = $salvadelete & ";" & $y EndIf EndIf EndIf If $Astring1[$y] = "34" And $cond1 = 0 Then ;PRimo step per primo ->" $cond1 = 1 ;gollog('Found left "') If $salvadelete = "" Then $salvadelete = String($y) Else $salvadelete = $salvadelete & ";" & $y EndIf ;Gollog("debug" & $salvaApici) EndIf Next ; Gollog("apici e virgole da togliere: " & $salvadelete) ;gollog("virgole togliere: " & $salvaVirgole) If _ArrayDelete($Astring1, $salvadelete) = -1 Then Gollog("error in arraydelete apici " & @error) gollog(StringFromASCIIArray($Astring1)) Else gollog(' PASS 1 removed unwanted ",," from line ') EndIf $Astring2 = StringFromASCIIArray($Astring1) $Pclean = $Astring2 Return $Pclean EndFunc ;==>preciseremoval You will need the OutlookEX.au3 , look at this link and also my logging UDF _Gollog.au3, attached. Used on Outlook from 2010 to 2016/365 with no problems. Hope this script can help someone. TEST1.csv _GOLLOG.au3

I had a problem using OutlookEX when automating Outlook v14 (2010) when it was already running; the object returned from _OL_Open wasn't usable . As a test, this example would fail to create an object for $objMail but there was no error from _OL_Open() $outlook = _OL_Open() $objMail = $outlook.Application.CreateItem(0) but this replacement would work: $outlook = ObjCreate("Outlook.Application") $objMail = $outlook.Application.CreateItem(0) and this would fail: $outlook = ObjGet("", "Outlook.Application") $objMail = $outlook.Application.CreateItem(0) So for my own purposes, the "fix" was to just to add a flag for _OL_Open() to ObjCreate() instead of ObjGet() when needed, setting it to True when there's a problem Func _OL_Open($bCreateNew = False, $bWarningClick = False, $sWarningProgram = "", $iWinCheckTime = 1000, $iCtrlCheckTime = 1000, $sProfileName = "", $sPassword = "") If $bCreateNew = Default Then $bCreateNew = False If $bWarningClick = Default Then $bWarningClick = False If $sWarningProgram = Default Then $sWarningProgram = "" If $iWinCheckTime = Default Then $iWinCheckTime = 1000 If $iCtrlCheckTime = Default Then $iCtrlCheckTime = 1000 If $sProfileName = Default Then $sProfileName = "" If $sPassword = Default Then $sPassword = "" Local $oOL = ObjGet("", "Outlook.Application") If IsObj($oOL) Then $__bOL_AlreadyRunning = True If Not IsBool($bWarningClick) Then Return SetError(3, 0, 0) If Not IsInt($iWinCheckTime) Then Return SetError(4, 0, 0) If Not IsInt($iCtrlCheckTime) Then Return SetError(7, 0, 0) If $__bOL_AlreadyRunning And $sProfileName <> "" Then Return SetError(10, 0, 0) ;Specified a profile name to logon to but Outlook is already running. ; Activate the COM error handler for older AutoIt versions If $__iOL_Debug = 0 And (Number(StringReplace(@AutoItVersion, ".", "")) < 3392 Or Number(StringReplace(@AutoItVersion, ".", "")) > 33120) Then _OL_ErrorNotify(4) SetError(0) ; Reset @error which is returned by _OL_ErrorNotify if a COM error handler has already been set up by the user EndIf If Not $__bOL_AlreadyRunning Or $bCreateNew Then $oOL = ObjCreate("Outlook.Application") If @error Or Not IsObj($oOL) Then Return SetError(1, @error, 0) EndIf However, the problem with modifying a UDF is in remembering to apply the same fix for to any update Was there any other "fix" I should have tried before tampering with the UDF? Thanks.

@water HEllo mate Everyone else Hi ! just noticed _OL_Wrapper_SendMail Is not working with #RequireAdmin I think I have to change the actual used usermailbox or somthing ? And how ?