Sign in to follow this  
Followers 0
Sam1814

Export from Access/Import Outlook Contacts

11 posts in this topic

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.

Share this post


Link to post
Share on other sites



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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (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 by Sam1814

Share this post


Link to post
Share on other sites
"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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

#9 ·  Posted (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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0