Sam1814 Posted September 27, 2014 Share Posted September 27, 2014 I have searched the forums for a script that will aloow me to import a text file into Outlook Contacts Folder. So far, no luck finding what I need and every effort to understand the OutlookEX scripts has come up dry. In a nutshell: 1- I have created an MS Acccess 2010 database. From within the DB I create an external export of records inot a tab delimited text file. 2- Manually, I can open Outlook and import the TXT file (File, Open, Import, Import from another program or file, Tab Separated Values, "C:importscontacts.txt", highlight replace duplicates) into the Contacts folder. I need a script to automate Step #2. Thx, in advance. Link to comment Share on other sites More sharing options...
water Posted September 27, 2014 Share Posted September 27, 2014 Have you tried function _OL_ItemImport? The example script _OL_ItemImport.au3 should give you an idea. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Sam1814 Posted September 28, 2014 Author Share Posted September 28, 2014 (edited) Water, THX for the suggestion. I modified the filename in _OL_Itemimport, as follows: Global $iResult = _OL_ItemImport($oOutlook, "C:Residentcontacts.txt", "", "", 1, "*Outlook-UDF-TestTargetFolderContacts", $olContactItem) [i am not certain I understand the syntax] This dialog pops up and I haven't a clue as to what it means. The error numbers are not defined. "Error creating test environment. @error=501, @extended =400"; Following the error dialog, and creation of a Test Environment, _OL_Itemimport then proceeds to create a task. I don't understand why. Can't figure out from where that is coming the the AU3 >>>>>>>>>>>>>>>>>>>> Actually, I am not looking to import individual contacts, but rather (when perfoming the steps manually) the existing content of the Contacts folder is updated with the fields in the "Contacts.txt" file. Is the txt file format part of the problem? I don't want to use CSV. I looked over _OL_CreateFolder, but that doesn't seem to give me what I need. I thought I might combine that with _OL_Delete to accomplish the goal. Deleting and replacing the Contacts Folder is not desirable, but better than nothing. Essentially, it seems that this should be a simple endeavor, but I am probably too stupid to see the forest for the trees. I thought of Au3Record as a last ditch effort, but that won't do for multiple environments. It is really not much more than opening Outlook,executing a number of mouse clicks along the Navigation Bar and within popup modals, and closing Outlook. File Open Import Import from other program or file (In this case Contacts.txt) Tab Separated Value Replace Duplicates Finish Outlook is 2010, 32 bit running in 64bit Windows 7 environment. Setup is default Office 2010 into "Program Files (x86)" . Thx for whatever help you can provide Edited September 28, 2014 by Sam1814 Link to comment Share on other sites More sharing options...
water Posted September 28, 2014 Share Posted September 28, 2014 "Error creating test environment. @error=501, @extended =400"; The exxample scripts create a test environment so that all changes can be easily removed when needed. @error = 501 is being shown when the creation of an appointment fails. To avoid this bug. Create a test folder "Test-Contacts" to import the contacts to and run this example: #include <OutlookEX.au3> Global $oOutlook = _OL_Open() If @error <> 0 Then Exit MsgBox(16, "OutlookEX UDF", "Error creating a connection to Outlook. @error = " & @error & ", @extended = " & @extended) Global $iResult = _OL_ItemImport($oOutlook, "C:\temp\_OL_ItemExport.csv", "", "", 1, "*\Test-Contacts", $olContactItem) If @error <> 0 Then Exit MsgBox(16, "OutlookEX UDF: _OL_ItemImport Example Script", "Error creating contacts from file 'C:\temp\_OL_ItemExport.csv' in folder '*\Outlook-UDF-Test\TargetFolder\Contacts'. @error = " & @error & ", @extended = " & @extended) MsgBox(64, "OutlookEX UDF: _OL_ItemImport Example Script", $iResult & " contact(s) successfully imported to folder '*\Outlook-UDF-Test\TargetFolder\Contacts'.") _OL_Close($oOutlook) My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted September 28, 2014 Share Posted September 28, 2014 The CSV file (the file extension doesn't matter: CSV, TXT ...) needs to be in the correct format. "The first line of the file (header line) has to be a list of Outlook item property names. The manual import allows to map user defined names to Outlook item property names. This isn't supported with this function! E.g.: Name,Mobile Phone,Business Phone,e-mail is invalid FullName,MobileTelephoneNumber,BusinessTelephoneNumber,Email1Address is fine!" The list of property names can be found here. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Sam1814 Posted September 28, 2014 Author Share Posted September 28, 2014 Water, I created a CSV file, named it _OL_ItemExport.CSV, and had the header as basic as I thought would make a good test.... .LastName .FirstName .HomePhone .MobilePhone .E-mail1Address I placed the CSV into the C:temp folder and also created the "Test-Contacts" folder. The script you provided compiled without error, but when run it reported: "......... @error=6, @extended =4" Any ideas as to what I am stll doing incorrectly? FWIW: MS access creates the .TXT file using the field names from the DB query as headers for the TXT. I replaced those headers with property names used by Outlook. When I perform this task manually for the first time in Outlook, I must create the one-to-one relationships (after the first time, Outlook "remembers" those relationships. From within Access I can export individual records directly into the Outlook Contacts folder using this module: Function AddOlContact() On Error GoTo Error_Handler Const olContactItem = 2 Dim olApp As Object Dim Ctct As Object Set olApp = CreateObject("Outlook.Application") Set olContact = olApp.CreateItem(olContactItem) With olContact If Not IsNull(Forms![Resident Details].[First Name].Value) Then .FirstName = Forms![Resident Details].[First Name].Value End If If Not IsNull(Forms![Resident Details].[Last Name].Value) Then .LastName = Forms![Resident Details].[Last Name].Value End If If Not IsNull(Forms![Resident Details].[Home Phone].Value) Then .HomeTelephoneNumber = Forms![Resident Details].[Home Phone].Value End If If Not IsNull(Forms![Resident Details].[Alt Phone].Value) Then .MobileTelephoneNumber = Forms![Resident Details].[Alt Phone].Value End If If Not IsNull(Forms![Resident Details].[E-mail Address]) Then .Email1Address = Forms![Resident Details].[E-mail Address].Value End If .Save End With Error_Handler_Exit: On Error Resume Next Set olContact = Nothing Set olApp = Nothing Exit Function Error_Handler: MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _ Err.Number & vbCrLf & "Error Source: AddOlContact" & vbCrLf & "Error Description: " & _ Err.Description, vbCritical, "An Error has Occured!" Resume Error_Handler_Exit End Function I can the export of an entire table. Using the Access built in "Saved Exports" functionality, and running DoCmd.RunSavedImportExport "Export-Contacts". What I am trying to do with AutoIt is import the file which has been created, using this DoCmd method, into Outlook Contacts folder. Link to comment Share on other sites More sharing options...
water Posted September 28, 2014 Share Posted September 28, 2014 Did you check the help file for the meaning of the error code? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Sam1814 Posted September 28, 2014 Author Share Posted September 28, 2014 It looks as though I have misread the syntax and what is being created with _OL_ItemImport. I think writng a script would be as complex as creating the VBA within Access, but I'll go that route since I am more adept at the latter. Thanks for your assistance. Link to comment Share on other sites More sharing options...
water Posted September 28, 2014 Share Posted September 28, 2014 (edited) This works fine here: #include <OutlookEX.au3> #include <Array.au3> Global $oOutlook = _OL_Open() If @error <> 0 Then Exit MsgBox(16, "OutlookEX UDF", "Error creating a connection to Outlook. @error = " & @error & ", @extended = " & @extended) Global $iResult = _OL_ItemImport($oOutlook, "C:\temp\test.txt", "", "", 1, "*\Kontakte", $olContactItem) If @error <> 0 Then Exit MsgBox(16, "OutlookEX UDF", "Error creating contacts. @error = " & @error & ", @extended = " & @extended) MsgBox(64, "OutlookEX UDF", $iResult & " contact(s) successfully imported.") _OL_Close($oOutlook) to import 2 contacts from this file to your contacts folder (mine is named german "Kontakte"). FirstName,Lastname,BusinessTelephoneNumber John,Doe,+49 123 45678 Jane,Doe,+49 123 87654 Notice that there has to be a CRLF after the last contact. The first line contains the property names without a dot. Edited September 28, 2014 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Sam1814 Posted September 28, 2014 Author Share Posted September 28, 2014 Water, that worked! With some tweaking I was able to export 789 records without a hitch. I was overthinking this. You obviously know how to make life easier. Now, can you tell me how to avoid duplicates? I've searched the forum and noted a request for this functionality, but no suggested resolution. Can you point me in the right direction (again)? THX Link to comment Share on other sites More sharing options...
water Posted September 28, 2014 Share Posted September 28, 2014 Great! At the moment there is no way to remove duplicates. You could modify function _OL_ItemImport after the array to import has been created. If you only need to check one column you could use _ArrayUnique. If you need to check multiple columns you would need one of the functions from the forum to sort on multiple culumns and then delete all duplicates. Func _OL_ItemImportEX($oOL, $sPath, $sDelimiters, $sQuote, $iFormat, $vFolder, $iItemType) If StringStripWS($sPath, 3) = "" Then Return SetError(1, 0, 0) If Not FileExists($sPath) Then Return SetError(2, 0, 0) If Not IsObj($vFolder) Then If StringStripWS($vFolder, 3) = "" Then Return SetError(3, 0, 0) Local $aTemp = _OL_FolderAccess($oOL, $vFolder) If @error Then Return SetError(6, @error, "") $vFolder = $aTemp[1] EndIf If Not IsNumber($iItemType) Then Return SetError(4, 0, 0) Local $aData, $sString, $aItemData $aData = __ParseCSV($sPath, $sDelimiters, $sQuote, $iFormat) If @error Then Return SetError(5, @error, 0) => Sort the array on one or multiple columns and delete duplicates For $iIndex1 = 1 To UBound($aData, 1) - 1 $sString = "" For $iIndex2 = 0 To UBound($aData, 2) - 1 $sString = $sString & "|" & $aData[0][$iIndex2] & "=" & $aData[$iIndex1][$iIndex2] Next $aItemData = StringSplit($sString, "|", 2) $sString = StringMid($sString, 2) ; Get rid of first | _OL_ItemCreate($oOL, $iItemType, $vFolder, "", $aItemData) If @error Then Return SetError(7, @error, 0) Next Return UBound($aData, 1) - 1 EndFunc ;==>_OL_ItemImportEX My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now