VBA to Convert Contact Data in Excel to VCF format

.

Dear Friends,

As you have learned about interacting with Text files through Excel VBA, it is time to see the usage of those methods you learned. 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 an Excel table as the vcf file format. This is also called a 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, let’s understand – What is the VCF file format? What is its use of it? How are 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:

BEGIN:VCARD
VERSION:3.0
N:FirstName;LastName;;;
FN:FullName
ORG:OrganizationName
TITLE:PostNameInOrganization
TEL;TYPE=WORK,VOICE:031-11223344
TEL;TYPE=HOME,VOICE:031-11223345
EMAIL:forrestgump@example.com
END:VCARD

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, a number of fields, etc. does not matter, because we are going to learn the method to convert excel data to this format. The addition of fields or changes in syntax can be altered once you understand the base code with an example file.

VBA Code to Save contact details as VCARD

With a very basic vCard format, I have created a VBA code that can convert all the contacts stored in an excel sheet into a VCF File.[/fusion_text][fusion_text]


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
    Wend
 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

 

Note:
In the above code, I have used the most commonly used data for a contact. vCards are growing with a lot of information that can be stored for a contact. There are vCards, which even store profile pictures as well.
In this example, I have used Version 3.0. You can see there are more versions available on the 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

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

5 Comments

  1. İsmail

    Hi,

    Could you add a cell phone to the excel table?

    Reply
  2. Signy13

    Little improvement:
    * macro asks for file name
    * macro writes only not empty fields
    I am sorry, I have no idea how to write code here…

    
    Sub ExcelToVCF()
    '
    ' ExcelToVCF Makro
    ' Converts SiMoCo contacts into do Vcard 3.0
    ' The result file must be in UTF-8 encoding without BOM.
    ' First contact is in iRowStart variable
    '
        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
        Dim varResult As Variant
        Dim FileName As String
    
        ' Displays the save file dialog
        FileName = Activeworkbook.name
        If InStr(FileName, ".") > 0 Then
           FileName = Left(FileName, InStr(FileName, ".") - 1)
        End If
        varResult = Application.GetSaveAsFilename(FileName, "VCArd Files (.vcf), *.vcf,All Files (*.*), *.*")
        Cells(5, 5) = varResult
        ' Checks to make sure the user hasn't canceled the dialog
        If varResult  False Then
            iRowStart = 3
            iRow = iRowStart
            ' set a unique integer for the new
            ' text file
            FileNum = FreeFile
            OutFilePath = varResult
            Open OutFilePath For Output As FileNum
        
            With Activeworkbook.ActiveSheet
            While VBA.Trim(.Cells(iRow, 1))  ""
                'FirstName = VBA.Trim(.Cells(iRow, 1))
                'LastName = VBA.Trim(.Cells(iRow, 2))
                FullName = VBA.Trim(.Cells(iRow, 2))
                EmailAddress = VBA.Trim(.Cells(iRow, 13))
                ' SiMoCo does hav no difference between Home/Work, just Tel/Line/Mobil
                Phone = VBA.Trim(.Cells(iRow, 4))
                PhoneHome = VBA.Trim(.Cells(iRow, 10))
                PhoneWork = VBA.Trim(.Cells(iRow, 11))
                ' SiMoCo groups into Organization.
                Organization = VBA.Trim(.Cells(iRow, 15))
                Address = VBA.Trim(.Cells(iRow, 6))
                '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 & ";;;"
                If FullName  "" Then
                    Print #FileNum, "FN:" & FullName
                End If
                If Organization  "" Then
                    Print #FileNum, "ORG:" & Organization
                End If
                'Print #FileNum, "TITLE:" & JobTitle
                If Phone  "" Then
                    Print #FileNum, "TEL;TYPE=VOICE:" & Phone
                End If
                If PhoneHome  "" Then
                    'Print #FileNum, "TEL;TYPE=HOME,VOICE:" & PhoneHome
                    Print #FileNum, "TEL;TYPE=VOICE:" & PhoneHome
                End If
                If PhoneWork  "" Then
                    'Print #FileNum, "TEL;TYPE=WORK,VOICE:" & PhoneWork
                    Print #FileNum, "TEL;TYPE=VOICE:" & PhoneWork
                End If
                If Address  "" Then
                    Print #FileNum, "ADR;:;;" & Address
                End If
                If EmailAddress  "" Then
                    Print #FileNum, "EMAIL:" & EmailAddress
                End If
                Print #FileNum, "END:VCARD"
                iRow = iRow + 1
            Wend
         End With
            'Close The File
            MsgBox "Total " & iRow - iRowStart & " Contacts are exported to VCF File. It is saved to VCF file " & varResult
            Close #FileNum
        End If
    End Sub
    
    
    Reply
  3. ismail

    Hi,

    When you put it on the phone, the table, Vba code; mobile phone, business phone, fax, mail, address, company, company, title, website, group, how can I add photos?

    Respect.

    Reply
  4. Rado

    This is Great Tool!! Thanks a lot. God Bless You !!!

    Reply
  5. levent erdem

    Thank you for easy and very usefull vba. There is a problem with ö, ı, ü , ş etc. letters. When you save as UTF-8 encoding from notepad, it is ok.

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest