Dear Friends,

As you have learnt about interacting with Text files through Excel VBA, it is time to see the usage of those methods you learnt. One usage, you have already seen here, in how to export excel data in CSV format.
In this article, I am going to teach you how to Export contacts stored in Excel table as vcf file format. This is also called vCard file. In other words, VBA to convert Contact details from Excel to Mobile Contact format.
Before we go on discussing the technical implementation of it, lets understand – What is VCF file format? What is the use of it? How is this article and code going to help you?

What is VCF or vCard File ?

Don’t worry… this is nothing new. You have always been using this file indirectly.
It is an abbreviation of Virtual Contact File. Basically, this is Text type file which stores the contacts which you use in Outlook, mobile etc. That is why, I said.. you have always been using this file directly or inderectly.
It is also known as vCard (Virtual Card) file. Extension for this file type is .vcf or .vcard
As I mentioned above, this is simple text file but all the details of a contacts are written in a specific format.

Sample Structure of vCard or VCF file

Here is a sample vCard file. Each contact details are stored within
BEGIN:VCARD and END:VCARD tags as shown below

EMAIL:[email protected]

You can refer Wiki page to know more about all the tags used in this file format. It is explained in detail there.
For now, number of fields etc. does not matter, because we are going to learn the method to convert excel data to this format. Addition of fields or change in syntax can be altered once you understand the base code with example file.

VBA Code to Save contact details as VCARD

With a very basic vCard format, I have created a VBA code which can convert all the contacts stored in an excel sheet in a VCF File.

Sub excelTovcf()
    Dim FileNum As Integer
    Dim iRow As Integer
    Dim FirstName As String
    Dim LastName As String
    Dim FullName As String
    Dim EmailAddress As String
    Dim PhoneHome As String
    Dim PhoneWork As String
    Dim Organization As String
    Dim JobTitle As String
    iRow = 7
    ' set a unique integer for the new
    ' text file
    FileNum = FreeFile
    ' Save this vcf file on desktop
    OutFilePath = VBA.Environ$("UserProfile") & "\Desktop\MyContacts.VCF"
    Open OutFilePath For Output As FileNum

    With Sheets("contacts")
    While VBA.Trim(.Cells(iRow, 1)) <> ""
        FirstName = VBA.Trim(.Cells(iRow, 1))
        LastName = VBA.Trim(.Cells(iRow, 2))
        FullName = VBA.Trim(.Cells(iRow, 3))
        EmailAddress = VBA.Trim(.Cells(iRow, 4))
        PhoneHome = VBA.Trim(.Cells(iRow, 5))
        PhoneWork = VBA.Trim(.Cells(iRow, 6))
        Organization = VBA.Trim(.Cells(iRow, 7))
        JobTitle = VBA.Trim(.Cells(iRow, 8))
    ' Start printing the data in above specified
    ' format of VCF file format
        Print #FileNum, "BEGIN:VCARD"
        Print #FileNum, "VERSION:3.0"
        Print #FileNum, "N:" & FirstName & ";" & LastName & ";;;"
        Print #FileNum, "FN:" & FullName
        Print #FileNum, "ORG:" & Organization
        Print #FileNum, "TITLE:" & JobTitle
        Print #FileNum, "TEL;TYPE=HOME,VOICE:" & PhoneHome
        Print #FileNum, "TEL;TYPE=WORK,VOICE:" & PhoneWork
        Print #FileNum, "EMAIL:" & EmailAddress
        Print #FileNum, "END:VCARD"
        iRow = iRow + 1
 End With
    'Close The File
    MsgBox "Total " & iRow - 7 & " Contacts are exported to VCF File. It is saved on your Desktop"
    Close #FileNum
End Sub

In the above code, I have used most commonly used data for a contact. vCards are growing with lot of information which can be stored for a contact. There are are vCards, which even stores profile pictures as well.
In this example, I have used Version 3.0. You can see there are more versions available on wiki page.

FREE DOWNLOAD : Excel To VCF File Converter

I have created a sample excel workbook, using which you can simply convert contact details stored in an Excel Sheet to a VCF File format which can be imported to your outlook or phone contacts.

Excel To VCF File Converter

Excel To VCF File Converter



Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!