outlook contacts to phonebook

[Marlysa]

@Avic_weo

After reading your previous messages, I'm not sure to understand exactly what you want.

 

Is it ?

0 = General (only use this if you only have one number for that particular contact)

1 = Home

2 = Work

3 = Mobile

 

Because I consider I unse only 4 categories: Home, Work, Mobile, Other Phone. And I prefer to the phone in the correct position than place it is "0" if I only have one Number.

 

Could you explain how you want to organize your contacts in the AVIC phonebook?

[avic_weo]

Marlysa,

 

Your approach is not bad or wrong. After all, nobody said you have to do it one way or another. But what I mean is that there could be a problem with class 0.

 

The way I wrote the macro the first thing it does is check if there is more than 1 telephone number for the same contact. If there isn't, the variable numQual becomes 0. (lines 69 through 73 on the attachment in the first post). This means that ANY class 1, 2 or 3 will be forced to 0. So I can only use 3 classes effectively.

 

What you are trying to achieve, is to use 4 classes, with 0 being Other Phone number. I do not have any problem with that. But you left in the code to coerce the class to 0 if the contact has only 1 phone number, meaning you could end up with a contact in the AVIC where you think it is in the class OtherPhone.

 

My point is simply you have to decide what you want: use 3 or 4 categories. If you want 4 categories, than it would be better to take out the code with the qualifiers...

 

Perhaps like so:

  For Each myItem In myItems
   If InStr(1, myItem.Categories, "dont-export") = 0 Then

   'skip'  txtQual = Left(Trim(myItem.HomeTelephoneNumber), 1) _
   'skip'          & Left(Trim(myItem.MobileTelephoneNumber), 1) _
   'skip'          & Left(Trim(myItem.BusinessTelephoneNumber), 1)
   'skip'          
   'skip'  numQual = 0: If Len(txtQual) > 1 Then numQual = 1

     Call PrintSQLElement(myItem.FullName, myItem.HomeTelephoneNumber, 1)     'skip numQual
     Call PrintSQLElement(myItem.FullName, myItem.BusinessTelephoneNumber, 2) 'skip numQual
     Call PrintSQLElement(myItem.FullName, myItem.MobileTelephoneNumber, 3)   'skip numQual
     Call PrintSQLElement(myItem.FullName, myItem.OtherTelephoneNumber, 0)    'skip numQual

   End If
 Next myItem

 

But again, that is entirely up to you. I am not the one telling people how they should organize their contacts

 

Weo

[ndy4eva]

It works great!!! Thanks!!!!

[Hockey37]

Just wanted to say this made my life 1,000 times easier. I have well over 600 contacts in my Outlook, both business and personal. This let me simply edit out those who I didn't want to add.

 

Thanks again.

[Chuck88]

It works great except one thing: french letters are not converted and shown up with boxes instead.

 

Thank you for such a great script!!!

[Marlysa]

@Chuck88: On my french version , french letter are converted.

 

Look at it here:

[TUTO] Exporter vos contacts Outlook vers L'AVIC

[Chuck88]

@Chuck88: On my french version , french letter are converted.

 

Look at it here:

[TUTO] Exporter vos contacts Outlook vers L'AVIC

 

Merci beaucoup Marlysa pour ta suggestion, c'est juste qu'il faut que je m'enregistre dans votre forum juste pour télécharger le script ...

 

Y avait-il une chance que ton script se trouvait dans ce forum ou me le PM?

 

Merci d'avance!

[Marlysa]

Here is the french tutorial which explain how to use this script:

 

* Export contact from Outlook to AVIC

* Convert french letters

* Possibility to select only one categorie of contact to export it to the avic ( If you want to export the category named "Voiture" change the script with Const CatExportAnnuaire As String = "Voiture")

 

 

 

 

Here is the script: (Sorry comments and dialog boxes are only in french for the moment - If it is asked I'll translate it in English)

 

'******************
'Cette macro Outlook permet d'exporter les contacts sélectionnés dans Outlook vers le poste Pioneer - AVIC Fxxx
'
'Elle a été inspirée à partir de la macro de avic_weo publiée sur le Forum AVIC 411 et a été améliorée pour être compatible avec les caractères Français et la sélection d'une partie des contacts
'
'Version du 03-10-2009  - Réalisé Par Marlysa de la Lance
'******************


Option Explicit

' **** Cette partie est à configurer Manuellement
' Créez un répertoire sur votre disque dur : c:\AVICPhone

' Téléchargez l'application sqlite lite ici: http://www.sqlite.org/download.html - Precompiled Binaries For Windows -  sqlite-3_6_18.zip [Version dispo le 3/10/2009]
' Placez la dans le répertoire que vous avez créé sur votre Ordi


Const SQLITE   As String = "C:\AVICPhone\sqlite3.exe"

Const SQDB     As String = "C:\AVICPhone\contacts.sqdb"
Const SQLfile  As String = "C:\AVICPhone\contacts.sql"


Const CatExportAnnuaire As String = "Aucune"
   '*******
   'Cette variable permet de ne sélectionner qu'une partie de vos contacts dans votre annuaire Outlook
   '
   'Vous pouvez créer votre propre catégorie dans ce cas remplacer la valeur "Aucune" par votre catégorie
   'Si par exemple vous voulez sélectionner les contacts que vous voulez exportez qui sont dans la catégorie Voiture (Bien respectez la Case)
   'Remplacez alors la valeur "Aucune" par votre catégorie ici "Voiture"
   '
   'Const CatExportAnnuaire As String = "Voiture"
   '********


' ***** Fin de la zone à personnaliser


Sub ContactsToSQDB()
Dim sqlCmd As String
Dim retval As Double
 Dim Msg       As String


 retval = MsgBox("Ecraser le fichier " & SQLfile & " (si il existe)?", _
          vbQuestion + vbYesNo)

 If retval = vbYes Then
   Call ContactsToSQL
 End If

   sqlCmd = SQLITE _
       & " -echo " _
       & SQDB _
       & " "".read " _
       & Replace(SQLfile, "\", "\\") _
       & """"

 retval = MsgBox("OK pour générer votre PhonebookDB?" & vbCrLf & vbCrLf, _
          vbQuestion + vbYesNo)

 If retval = vbYes Then

     'sqlite3.exe -echo temp.db ".read c:\\temp\\contacts.txt"
     retval = Shell(sqlCmd, vbNormalFocus)

 End If

Msg = "Votre fichier Contact a été généré.  Veuillez renommer le fichier " & SQDB & " selon le nom associé à votre téléphone: PhoneBookxxxxxxxxxx.db"
retval = MsgBox(Msg, vbOKOnly)


End Sub

Private Sub ContactsToSQL()

 Dim olApp     As Outlook.Application
 Dim objName   As Outlook.NameSpace
 Dim myFolder  As Outlook.MAPIFolder
 Dim myItems   As Outlook.Items
 Dim myItem    As Object

 Dim txtQual   As String
 Dim numQual   As Integer


 Set olApp = Outlook.Application
 Set objName = olApp.GetNamespace("MAPI")
 Set myFolder = objName.PickFolder()

 'Set myFolder = objName.GetDefaultFolder(olFolderContacts)
 'Set myFolder = myFolder.Folders("Contactpersonen")

 Set myItems = myFolder.Items
 myItems.Sort "[FullName]", False

 Open SQLfile For Output As #1

 On Error Resume Next

'Print #1, ".echo on"
 Print #1, "drop table phonebook;"
 Print #1, "CREATE TABLE phonebook"
 Print #1, "  (ID           INTEGER PRIMARY KEY,"
 Print #1, "   chShowName   TEXT,"
 Print #1, "   chPhoneCode  TEXT,"
 Print #1, "   dwType       INTEGER"
 Print #1, "  );"
 Print #1, "BEGIN TRANSACTION;"

 For Each myItem In myItems
  '*****
  'Sélectionne les contacts dans la catégorie que vous voulez exportez
  'Et lui affecxte la catégorie:
               '0 si un seul N°
               '1 Tel Maison
               '2 Tel Portable
               '3 Tel Bureau
  '*****

   If (CatExportAnnuaire = "Aucune" Or InStr(1, myItem.Categories, CatExportAnnuaire) = 1) Then
      txtQual = Left(Trim(myItem.HomeTelephoneNumber), 1) _
             & Left(Trim(myItem.MobileTelephoneNumber), 1) _
             & Left(Trim(myItem.BusinessTelephoneNumber), 1)

     numQual = 0: If Len(txtQual) > 1 Then numQual = 1

     Call PrintSQLElement(myItem.FullName, myItem.HomeTelephoneNumber, numQual * 1)
     Call PrintSQLElement(myItem.FullName, myItem.MobileTelephoneNumber, numQual * 3)
     Call PrintSQLElement(myItem.FullName, myItem.BusinessTelephoneNumber, numQual * 2)
     Call PrintSQLElement(myItem.FullName, myItem.OtherTelephoneNumber, numQual * 0)

   End If
 Next myItem

 Print #1, "COMMIT;"
 Close #1


End Sub

Private Sub PrintSQLElement(Name, Phone, qualifier)
 Phone = Replace(Phone, " ", "")
 Name = Replace(Name, "'", "''")
 Name = Replace(Name, "ç", "c")
 Name = Replace(Name, "ö", "o")
 Name = Replace(Name, "ð", "g")
 Name = Replace(Name, "þ", "s")
 Name = Replace(Name, "ý", "i")
 Name = Replace(Name, "ü", "u")
 Name = Replace(Name, "Ç", "C")
 Name = Replace(Name, "Ö", "O")
 Name = Replace(Name, "Ð", "G")
 Name = Replace(Name, "Þ", "S")
 Name = Replace(Name, "Ü", "U")
Name = Replace(Name, "é", "e")
Name = Replace(Name, "è", "e")
Name = Replace(Name, "ê", "e")
Name = Replace(Name, "ï", "i")


 If Len(Phone) > 0 Then
   Print #1, "insert into 'phonebook' " _
           & "values(Null,'" & Name & "','" & Phone & "'," & qualifier & ");"
 End If
End Sub

__avicmb.xooit.fr_t21-Exporter-vos-contacts-Outlook-vers-L.pdf

[Chuck88]

Thank you very much. I will try it out.

 

[irkgreen]

Hey guys,

 

I'm gonna try my newly exported db file with the awesome VBA code. Thanks!

 

I was curious though about the caller ID. Do the phone numbers need to be stripped of (, ), and -'s? I've never had any luck with my custom PhoneData DB's showing an ID when someone calls me. They work fine when I pull up the contact but not when called. I can make an easy mod to the code if they just need to be numbers.

 

Also, while I'm booted up in test mode I was going to update my splash screen. It has been a while and I can't remember how I did it last time. A search for 'splash screen' didn't help. Is it an easy menu setting? Or need to be in a specific folder? Thanks

 

Eric

[avic_weo]

Hey guys,

 

I'm gonna try my newly exported db file with the awesome VBA code. Thanks!

 

I was curious though about the caller ID. Do the phone numbers need to be stripped of (, ), and -'s? I've never had any luck with my custom PhoneData DB's showing an ID when someone calls me. They work fine when I pull up the contact but not when called. I can make an easy mod to the code if they just need to be numbers.

 

Also, while I'm booted up in test mode I was going to update my splash screen. It has been a while and I can't remember how I did it last time. A search for 'splash screen' didn't help. Is it an easy menu setting? Or need to be in a specific folder? Thanks

 

Eric

 

Correct: the numbers need to be stripped of all non-numeric stuff. There is a thread in which this is explained. Outlook tries to store the phone numbers in a country-related style, e.g. for Belgium +32 (52) 19 12 14. This won't work. What I always do is to set my Outlook country setting to Generic. Then it will let you enter the phone number in the format you want.

 

Some work, but it pays of..

 

It is of course possible to have the Macro do the work for you. In the last paragraph, you could write:

Private Sub PrintSQLElement(Name, Phone, qualifier)
 Phone = Replace(Phone, " ", "")
' get rid of more unwanted characters in the phone number
 Phone = Replace(Phone, "(", "")
 Phone = Replace(Phone, ")", "")
 Phone = Replace(Phone, "-", "")
 ' ... and so on, you get the picture

 Name = Replace(Name, "'", "''")
 If Len(Phone) > 0 Then
   Print #1, "insert into 'phonebook' " _
           & "values(Null,'" & Name & "','" & Phone & "'," & qualifier & ");"
 End If
End Sub

 

 

Weo

[irkgreen]

I did just that. My SQL looks like:

 

insert into 'phonebook' values(Null,'Eric Green','859???????',1);

 

(anonymized for my privacy)

 

But it still shows as unknown if I initiate the call from my phone.

 

There's a thread on this you say?

[Marlysa]

@irkgreen: I couldn't test for the moment because I'm travelling in Eastern Europe (Without my car and my Avic ) but you could try to store the phone number as + 32 xxx xxx xxx

[avic_weo]

OK, I think the easiest thing to do is to monitor how the phone number appears on your AVIC when you recieve a call. The format in which you store the phonenumbers (and the SQL) has to match just that.

 

The thing is when you try to call '052123456', this will work. But when you recieve the call, the number that is presented to the AVIC, will be '+3252123456' (the 'national' 0 will be replaced by the international code for the country of the caller) Now if your phonebook entry does not match this exactly, it will not work.

 

HTH,

 

Weo

[irkgreen]

I will have to look, but I am pretty sure its like 8591234567, which is how I have it. Calling from the US I'm not sure how this would differ.