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 withinBEGIN: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
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.
Hi,
Could you add a cell phone to the excel table?
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…
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.
This is Great Tool!! Thanks a lot. God Bless You !!!
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.