Cook09
asked on
Set up an Excel Spreadsheet from Outlook Contacts - vs. 2010
Within my Outlook (2010) there are several Categories that have the necessary contact information that I need would like to transfer to a Workbook, with each category having a different tab.
Attached is a workbook that provides a model on how the contact information is listed, preferably. What is the best way to have the code loop through the contacts and create an Excel category list on different worksheet tabs?
For one Category, there is actually a distribution list that may be easier to use for one of the categories, and is listed in Col A. However, I'm not sure if the distribution list has all of the data which is needed, or just a more complete e-mail list than what I have manually put together.
MC-Update.xlsm
Attached is a workbook that provides a model on how the contact information is listed, preferably. What is the best way to have the code loop through the contacts and create an Excel category list on different worksheet tabs?
For one Category, there is actually a distribution list that may be easier to use for one of the categories, and is listed in Col A. However, I'm not sure if the distribution list has all of the data which is needed, or just a more complete e-mail list than what I have manually put together.
MC-Update.xlsm
ASKER
Harry,
Well part of the problem is that some of the fields that are in my Contacts are not in the Export Field.
Additionally, the categories are all over the place, so it would take time to filter each, manually create a tab, and then copy/paste. I was looking for something a little more automatic, that would also capture the information needed which is not in the export.
Well part of the problem is that some of the fields that are in my Contacts are not in the Export Field.
Additionally, the categories are all over the place, so it would take time to filter each, manually create a tab, and then copy/paste. I was looking for something a little more automatic, that would also capture the information needed which is not in the export.
Cook,
I just did an contacts export from my Outlook 2013, I know you are using 2010, I find that all the field you are looking for is in the exported CSV file.
Which field are you looking for, specifically, that is not exported?
Maybe I can help you to develop a VBA macro in Excel to extract the data from Outlook without going to Outlook to click export to get the job done.
I just did an contacts export from my Outlook 2013, I know you are using 2010, I find that all the field you are looking for is in the exported CSV file.
Which field are you looking for, specifically, that is not exported?
Maybe I can help you to develop a VBA macro in Excel to extract the data from Outlook without going to Outlook to click export to get the job done.
ASKER
Harry,
Well, one thing I found while trying different solutions is to highlight the Customize Heading, drag the mouse about 1/4" and it highlights the entire group. Then all that is needed is to copy and paste it into Excel, which also brings the Headings during the Paste. I think this is even faster than trying to use code.
Maybe you could assist me with something else I was trying to accomplish, and that is to extract the names within a Distribution List. Is this even possible?
Well, one thing I found while trying different solutions is to highlight the Customize Heading, drag the mouse about 1/4" and it highlights the entire group. Then all that is needed is to copy and paste it into Excel, which also brings the Headings during the Paste. I think this is even faster than trying to use code.
Maybe you could assist me with something else I was trying to accomplish, and that is to extract the names within a Distribution List. Is this even possible?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I did try the code that you provided, and received notification that it was trying to access the Exchange Server. However, it did error out on....
I did find this code, that will import my Contacts, but the e-mail address is in Exchange Format and not really usable. I did see that a "DisplayName," could provide the e-mail address, but the syntax is different, and still trying to figure it out.
Do you have any suggestions?
For Each Member In Entry.Members
I don't know if there is another command that would work, or is it the security settings?I did find this code, that will import my Contacts, but the e-mail address is in Exchange Format and not really usable. I did see that a "DisplayName," could provide the e-mail address, but the syntax is different, and still trying to figure it out.
Sub Import_Contacts()
'Outlook objects. From Local Address Book
Dim olApp As Outlook.Application
Dim olNamespace As Outlook.Namespace
Dim olFolder As Outlook.MAPIFolder
Dim olConItems As Outlook.Items
Dim olItem As Object
'Excel objects.
Dim wbBook As Workbook
Dim wsSheet As Worksheet
'Location in the imported contact list.
Dim lnContactCount As Long
Dim strDummy As String
'Turn off screen updating.
Application.ScreenUpdating = False
'Initialize the Excel objects.
' Set wbBook = ThisWorkbook
Set wbBook = ActiveWorkbook
Set wsSheet = wbBook.Worksheets(1)
'Format the target worksheet.
With wsSheet
.Range("A1").CurrentRegion.Clear
.Cells(1, 1).Value = "City"
.Cells(1, 2).Value = "State"
.Cells(1, 3).Value = "Zip Code"
.Cells(1, 4).Value = "MAC Address"
.Cells(1, 5).Value = "Name"
.Cells(1, 6).Value = "Job Title"
.Cells(1, 7).Value = "E-mail"
.Cells(1, 8).Value = "Account"
.Cells(1, 9).Value = "Tel No."
.Cells(1, 10).Value = "Street"
.Cells(1, 11).Value = "Email"
With .Range("A1:K1")
.Font.Bold = True
.Font.ColorIndex = 10
.Font.Size = 11
End With
End With
wsSheet.Activate
'Initalize the Outlook variables with the MAPI namespace and the default Outlook folder of the current user.
Set olApp = New Outlook.Application
Set olNamespace = olApp.GetNamespace("MAPI")
Set olFolder = olNamespace.GetDefaultFolder(10)
Set olConItems = olFolder.Items
'Row number to place the new information on; starts at 2 to avoid overwriting the header
lnContactCount = 2
'For each contact: if it is a business contact, write out the business info in the Excel worksheet;
'otherwise, write out the personal info.
For Each olItem In olConItems
If TypeName(olItem) = "ContactItem" Then
With olItem
If InStr(olItem.CompanyName, strDummy) > 0 Then
Cells(lnContactCount, 1).Value = .BusinessAddressCity
Cells(lnContactCount, 2).Value = .BusinessAddressState
Cells(lnContactCount, 3).Value = .BusinessAddressPostalCode
Cells(lnContactCount, 4).Value = .OfficeLocation
Cells(lnContactCount, 5).Value = .FullName
Cells(lnContactCount, 6).Value = .JobTitle
Cells(lnContactCount, 7).Value = .IMAddress
Cells(lnContactCount, 8).Value = .Account
Cells(lnContactCount, 9).Value = .BusinessTelephoneNumber
Cells(lnContactCount, 10).Value = .BusinessAddressStreet
Cells(lnContactCount, 11).Value = .DisplayName --- Not Correct Format
Else
Cells(lnContactCount, 1) = .FullName
Cells(lnContactCount, 2) = .HomeAddressStreet
Cells(lnContactCount, 3) = .HomeAddressPostalCode
Cells(lnContactCount, 4) = .HomeAddressCity
Cells(lnContactCount, 5) = .FullName
Cells(lnContactCount, 6) = .IMAddress
End If
On Error Resume Next 'The 11 was a 6 - Not sure what difference this makes
wsSheet.Hyperlinks.Add Anchor:=Cells(lnContactCount, 11), _
Address:="mailto:" & Cells(lnContactCount, 11).Value, _
TextToDisplay:=Cells(lnContactCount, 11).Value
End With
lnContactCount = lnContactCount + 1
End If
Next olItem
'Null out the variables.
Set olItem = Nothing
Set olConItems = Nothing
Set olFolder = Nothing
Set olNamespace = Nothing
Set olApp = Nothing
'Sort the rows alphabetically using the CompanyName or FullName as appropriate, and then autofit.
With wsSheet
.Range("A2", Cells(2, 11).End(xlDown)).Sort key1:=Range("A1"), order1:=xlAscending
.Range("A:K").EntireColumn.AutoFit
End With
'Turn screen updating back on.
Application.ScreenUpdating = True
MsgBox "The list has successfully been created!", vbInformation
End Sub
Do you have any suggestions?
I don't know why you have problem with
Anyway,
In the above code you have attached, the line 75 should be
For Each Member In Entry.Members
Maybe it has something to do with which MS Outlook Reference Library you are using.Anyway,
In the above code you have attached, the line 75 should be
Cells(lnContactCount, 11).Value = .Email1DisplayName
ASKER
Made the mistake of not adding the correct name to the Distribution List. When corrected, it ran fine.
I'm glad it works for you.
Instead of trying to get the info using loop to extract data from Outlook, I think a more simple and better approach is to export the Outlook contacts to a CSV file, then from Excel extract the data from the CSV by deleting unwanted columns and rearrange the columns order using vba or manually.