H
HBruno
Hi,
I am trying to get a code for exporting data from an custom outlook form to
Excel.
I had tried several routines but they don´t functions.
In the last routine below, i keep getting the message "Folder does not
contain contacts", but I have more than 300 contacts in the folder
\Inbox\Tickets.
So what am i doing wrong?
Heres the code
Sub SaveContactsToExcel()
On Error GoTo ErrorHandler
Dim appWord As Word.Application
Dim appExcel As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim rng As Excel.Range
Dim strSheet As String
Dim strTemplatePath As String
Dim i As Integer
Dim j As Integer
Dim lngCount As Long
Dim nms As Outlook.NameSpace
Dim fld As Outlook.MAPIFolder
'Must declare as Object because folders may contain different
'types of items
Dim itm As Object
Dim strTitle As String
Dim strPrompt As String
'Pick up Template path from the Word Options dialog
Set appWord = GetObject(, "Word.Application")
strTemplatePath = appWord.Options.DefaultFilePath(wdUserTemplatesPath) & "\"
Debug.Print "Templates folder: " & strTemplatePath
' MsgBox "strTemplatePath...." & strTemplatePath '
strSheet = "Contacts.xls"
strSheet = strTemplatePath & strSheet
Debug.Print "Excel workbook: " & strSheet
'Test for file in the Templates folder
If TestFileExists(strSheet) = False Then
strTitle = "Worksheet file not found"
strPrompt = strSheet & _
" not found; please copy Calendar.xls to this folder and try again"
MsgBox strPrompt, vbCritical + vbOKOnly, strTitle
GoTo ErrorHandlerExit
End If
Set appExcel = GetObject(, "Excel.Application")
appExcel.Workbooks.Open (strSheet)
Set wkb = appExcel.ActiveWorkbook
Set wks = wkb.Sheets(1)
wks.Activate
appExcel.Application.Visible = True
'Let user select a folder to export
Set nms = Application.GetNamespace("MAPI")
Set fld = nms.PickFolder
If fld Is Nothing Then
GoTo ErrorHandlerExit
End If
'
' MsgBox "pasta p/exportar...fld...." & fld
'Test whether selected folder contains contact items
If fld.DefaultItemType <> olContactItem Then
MsgBox "Folder does not contain contacts" ´here is the problem
' MsgBox "fld.DefaultItemType..." & fld.DefaultItemType
' MsgBox "olContactItem.... " & olContactItem
GoTo ErrorHandlerExit
End If
lngCount = fld.Items.Count
If lngCount = 0 Then
MsgBox "No Contacts to export"
GoTo ErrorHandlerExit
Else
Debug.Print lngCount & " Contacts to export"
End If
'Adjust i (row number) to be 1 less than the number of the first body row
i = 3
'Iterate through contact items in Contacts folder, and export a few fields
'from each item to a row in the Contacts worksheet
For Each itm In fld.Items
If itm.Class = olContact Then
'Process item only if it is a contact item
i = i + 1
'j is the column number
j = 1
Set rng = wks.Cells(i, j)
If itm.Title <> "" Then rng.Value = itm.Title
j = j + 1
Set rng = wks.Cells(i, j)
If itm.FirstName <> "" Then rng.Value = itm.FirstName
j = j + 1
Set rng = wks.Cells(i, j)
If itm.MiddleName <> "" Then rng.Value = itm.MiddleName
j = j + 1
Set rng = wks.Cells(i, j)
If itm.LastName <> "" Then rng.Value = itm.LastName
j = j + 1
Set rng = wks.Cells(i, j)
If itm.JobTitle <> "" Then rng.Value = itm.JobTitle
j = j + 1
Set rng = wks.Cells(i, j)
If itm.CompanyName <> "" Then rng.Value = itm.CompanyName
j = j + 1
Set rng = wks.Cells(i, j)
If itm.BusinessTelephoneNumber <> "" Then
rng.Value = itm.BusinessTelephoneNumber
End If
j = j + 1
Set rng = wks.Cells(i, j)
If itm.BusinessFaxNumber <> "" Then
rng.Value = itm.BusinessFaxNumber
End If
j = j + 1
Set rng = wks.Cells(i, j)
On Error Resume Next
'The next line illustrates the syntax for referencing
'a custom Outlook field
If itm.UserProperties("CustomField") <> "" Then
rng.Value = itm.UserProperties("CustomField")
End If
j = j + 1
End If
i = i + 1
Next itm
ErrorHandlerExit:
Exit Sub
ErrorHandler:
If Err.Number = 429 Then
'Application object is not set by GetObject; use CreateObject instead
If appWord Is Nothing Then
Set appWord = CreateObject("Word.Application")
Resume Next
ElseIf appExcel Is Nothing Then
Set appExcel = CreateObject("Excel.Application")
Resume Next
End If
Else
MsgBox "Error No: " & Err.Number & "; Description: "
Resume ErrorHandlerExit
End If
End Sub
I am trying to get a code for exporting data from an custom outlook form to
Excel.
I had tried several routines but they don´t functions.
In the last routine below, i keep getting the message "Folder does not
contain contacts", but I have more than 300 contacts in the folder
\Inbox\Tickets.
So what am i doing wrong?
Heres the code
Sub SaveContactsToExcel()
On Error GoTo ErrorHandler
Dim appWord As Word.Application
Dim appExcel As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim rng As Excel.Range
Dim strSheet As String
Dim strTemplatePath As String
Dim i As Integer
Dim j As Integer
Dim lngCount As Long
Dim nms As Outlook.NameSpace
Dim fld As Outlook.MAPIFolder
'Must declare as Object because folders may contain different
'types of items
Dim itm As Object
Dim strTitle As String
Dim strPrompt As String
'Pick up Template path from the Word Options dialog
Set appWord = GetObject(, "Word.Application")
strTemplatePath = appWord.Options.DefaultFilePath(wdUserTemplatesPath) & "\"
Debug.Print "Templates folder: " & strTemplatePath
' MsgBox "strTemplatePath...." & strTemplatePath '
strSheet = "Contacts.xls"
strSheet = strTemplatePath & strSheet
Debug.Print "Excel workbook: " & strSheet
'Test for file in the Templates folder
If TestFileExists(strSheet) = False Then
strTitle = "Worksheet file not found"
strPrompt = strSheet & _
" not found; please copy Calendar.xls to this folder and try again"
MsgBox strPrompt, vbCritical + vbOKOnly, strTitle
GoTo ErrorHandlerExit
End If
Set appExcel = GetObject(, "Excel.Application")
appExcel.Workbooks.Open (strSheet)
Set wkb = appExcel.ActiveWorkbook
Set wks = wkb.Sheets(1)
wks.Activate
appExcel.Application.Visible = True
'Let user select a folder to export
Set nms = Application.GetNamespace("MAPI")
Set fld = nms.PickFolder
If fld Is Nothing Then
GoTo ErrorHandlerExit
End If
'
' MsgBox "pasta p/exportar...fld...." & fld
'Test whether selected folder contains contact items
If fld.DefaultItemType <> olContactItem Then
MsgBox "Folder does not contain contacts" ´here is the problem
' MsgBox "fld.DefaultItemType..." & fld.DefaultItemType
' MsgBox "olContactItem.... " & olContactItem
GoTo ErrorHandlerExit
End If
lngCount = fld.Items.Count
If lngCount = 0 Then
MsgBox "No Contacts to export"
GoTo ErrorHandlerExit
Else
Debug.Print lngCount & " Contacts to export"
End If
'Adjust i (row number) to be 1 less than the number of the first body row
i = 3
'Iterate through contact items in Contacts folder, and export a few fields
'from each item to a row in the Contacts worksheet
For Each itm In fld.Items
If itm.Class = olContact Then
'Process item only if it is a contact item
i = i + 1
'j is the column number
j = 1
Set rng = wks.Cells(i, j)
If itm.Title <> "" Then rng.Value = itm.Title
j = j + 1
Set rng = wks.Cells(i, j)
If itm.FirstName <> "" Then rng.Value = itm.FirstName
j = j + 1
Set rng = wks.Cells(i, j)
If itm.MiddleName <> "" Then rng.Value = itm.MiddleName
j = j + 1
Set rng = wks.Cells(i, j)
If itm.LastName <> "" Then rng.Value = itm.LastName
j = j + 1
Set rng = wks.Cells(i, j)
If itm.JobTitle <> "" Then rng.Value = itm.JobTitle
j = j + 1
Set rng = wks.Cells(i, j)
If itm.CompanyName <> "" Then rng.Value = itm.CompanyName
j = j + 1
Set rng = wks.Cells(i, j)
If itm.BusinessTelephoneNumber <> "" Then
rng.Value = itm.BusinessTelephoneNumber
End If
j = j + 1
Set rng = wks.Cells(i, j)
If itm.BusinessFaxNumber <> "" Then
rng.Value = itm.BusinessFaxNumber
End If
j = j + 1
Set rng = wks.Cells(i, j)
On Error Resume Next
'The next line illustrates the syntax for referencing
'a custom Outlook field
If itm.UserProperties("CustomField") <> "" Then
rng.Value = itm.UserProperties("CustomField")
End If
j = j + 1
End If
i = i + 1
Next itm
ErrorHandlerExit:
Exit Sub
ErrorHandler:
If Err.Number = 429 Then
'Application object is not set by GetObject; use CreateObject instead
If appWord Is Nothing Then
Set appWord = CreateObject("Word.Application")
Resume Next
ElseIf appExcel Is Nothing Then
Set appExcel = CreateObject("Excel.Application")
Resume Next
End If
Else
MsgBox "Error No: " & Err.Number & "; Description: "
Resume ErrorHandlerExit
End If
End Sub