Export from Access/Import Outlook Contacts

[Sam1814]

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.

[water]

Have you tried function _OL_ItemImport? The example script _OL_ItemImport.au3 should give you an idea.

[Sam1814]

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

[water]

"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)

[water]

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.

[Sam1814]

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.

[water]

Did you check the help file for the meaning of the error code?

[Sam1814]

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. 

[water]

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

[Sam1814]

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

[water]

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