form's VBscript disabled after the first save

J

j1313

If I open a new form then save it its all good but if i then try to edit this
new contact, the VB script in the form is disabled and events do not take
place (eg at contact item write).

Some things I've tried:
* I Opened the newly saved contact where the scripts don't work and went to
tools/forms/design this form, and then in the form design window to form/view
code and the script code is all there. I go to form/run this form and the
form runs with the scripts.


I've narrowed things down to using method: "item.UserProperties.Find"



in
Function Item_Write()
....
item.UserProperties.Find("GovernmentIDNumber", False)


#############################################

Const MAX_RAND_SIZE = 1000000000
Const MIN_RAND_SIZE = 100000000
Const FOLDER_L0_MAILBOX = "Public Folders"
Const FOLDER_L1 = "All Public Folders"
Const FOLDER_L2 = "Contacts Database"


Dim cloneBunnyLoopProtection

Function MyRand()
Dim r1
r1 = (Rnd * MAX_RAND_SIZE) Mod MAX_RAND_SIZE
If r1 < 100000000 Then
MyRand = MyRand()
Else
MyRand = r1
End If
End Function

'just for mailboxes
Function getMailboxFolder(mbName)
For Each mailbox In Application.Session.Folders
If InStr(mailbox.Name, mbName) Then
Set getMailboxFolder = mailbox
End If
Next
End Function

'generic folder search function
Function getFolderWithStringInFolder(folderName, parentFolder)
For Each Folder In parentFolder.Folders
If InStr(Folder.Name, folderName) Then
Set getFolderWithStringInFolder = Folder
End If
Next
End Function

Function Item_Open()
cloneBunnyLoopProtection = True
End Function

Function Item_Read()
End Function

Function Item_Write()
Dim currFolder
Dim pubFolder
Dim pubContact
Dim currGovID
Dim pubGovID
msgbox "form - write"
If cloneBunnyLoopProtection then
set currGovID = item.UserProperties.Find("GovernmentIDNumber", False)
If currGovID.Value = "" Then 'new contact
Randomize
currGovID.Value = "id" & MyRand()
Randomize
currGovID.Value = currGovID.Value & MyRand()
Randomize
currGovID.Value = currGovID.Value & MyRand()
Else 'else find the public copy of contact
Set currFolder = getMailboxFolder(FOLDER_L0_MAILBOX )
Set currFolder = getFolderWithStringInFolder(FOLDER_L1,
currFolder)
Set pubFolder = getFolderWithStringInFolder(FOLDER_L2, currFolder)
set pubContact = pubFolder.Items.Find("[GovernmentIDNumber] = "
& currGovID.Value)
If VarType(pubContact) = 8 Then
set pubGovID =
pubContact.UserProperties.Find("GovernmentIDNumber", False)
If pubGovID.Value = currGovID.Value Then
pubContact.Delete
End if
End if
End If
CreateCensoredContact()
End If
cloneBunnyLoopProtection = False
End Function

Sub CreateCensoredContact()
Dim currFolder
Dim pubFolder
Dim ncc

Set currFolder = getMailboxFolder(FOLDER_L0_MAILBOX )
Set currFolder = getFolderWithStringInFolder(FOLDER_L1, currFolder)
Set pubFolder = getFolderWithStringInFolder(FOLDER_L2, currFolder)

Set ncc = item.copy

CensorContactFields(ncc)
CensorCustomFields(ncc)
ncc.Move pubFolder
End Sub

Sub CensorContactFields(ncc)
ncc.Account = ""
ncc.AssistantName = ""
ncc.AssistantTelephoneNumber = ""
ncc.BillingInformation = ""
'ncc.Body = "" 'Notes Field
'ncc.Business2TelephoneNumber = ""
'ncc.BusinessAddress = ""
'ncc.BusinessAddressCity = ""
'ncc.BusinessAddressCountry = ""
'ncc.BusinessAddressPostalCode = ""
'ncc.BusinessAddressPostOfficeBox = ""
'ncc.BusinessAddressState = ""
'ncc.BusinessAddressStreet = ""
'ncc.BusinessFaxNumber = ""
'ncc.BusinessHomePage = ""
'ncc.BusinessTelephoneNumber = ""
ncc.CallbackTelephoneNumber = ""
ncc.CarTelephoneNumber = ""
'ncc.Categories = ""
ncc.Children = ""
'ncc.Companies = ""
ncc.CompanyMainTelephoneNumber = ""
'ncc.CompanyName = ""
ncc.ComputerNetworkName = ""
'ncc.CustomerID = ""
'ncc.Department = ""
'ncc.Email1Address = ""
'ncc.Email1AddressType = ""
'ncc.Email1DisplayName = ""
ncc.Email2Address = ""
ncc.Email2AddressType = ""
ncc.Email2DisplayName = ""
ncc.Email3Address = ""
ncc.Email3AddressType = ""
ncc.Email3DisplayName = ""
'ncc.FileAs = ""
'ncc.FirstName = ""
ncc.FormDescription = ""
ncc.FTPSite = ""
'ncc.FullName = " "
'ncc.GovernmentIDNumber = ""


ncc.Hobby = ""
ncc.Home2TelephoneNumber = ""
ncc.HomeAddress = ""
ncc.HomeAddressCity = ""
ncc.HomeAddressCountry = ""
ncc.HomeAddressPostalCode = ""
ncc.HomeAddressPostOfficeBox = ""
ncc.HomeAddressState = ""
ncc.HomeAddressStreet = ""
ncc.HomeFaxNumber = ""
ncc.HomeTelephoneNumber = ""
ncc.IMAddress = ""
'ncc.Initials = ""
ncc.InternetFreeBusyAddress = ""
ncc.ISDNNumber = ""
'ncc.JobTitle = ""
ncc.Language = ""
'ncc.LastName = ""
'ncc.MailingAddress = ""
'ncc.MailingAddressCity = ""
'ncc.MailingAddressCountry = ""
'ncc.MailingAddressPostalCode = ""
'ncc.MailingAddressPostOfficeBox = ""
'ncc.MailingAddressState = ""
'ncc.MailingAddressStreet = ""
ncc.ManagerName = ""
ncc.MessageClass = ""
'ncc.MiddleName = ""
ncc.Mileage = ""
ncc.MobileTelephoneNumber = ""
ncc.NetMeetingAlias = ""
ncc.NetMeetingServer = ""
'ncc.NickName = ""

ncc.OfficeLocation = ""
ncc.OrganizationalIDNumber = ""
ncc.OtherAddress = ""
ncc.OtherAddressCity = ""
ncc.OtherAddressCountry = ""
ncc.OtherAddressPostalCode = ""
ncc.OtherAddressPostOfficeBox = ""
ncc.OtherAddressState = ""
ncc.OtherAddressStreet = ""
ncc.OtherFaxNumber = ""
ncc.OtherTelephoneNumber = ""
ncc.PagerNumber = ""
ncc.PersonalHomePage = ""
ncc.PrimaryTelephoneNumber = ""
'ncc.Profession = ""
ncc.RadioTelephoneNumber = ""
ncc.ReferredBy = ""
ncc.Spouse = ""
'ncc.Subject = ""
'ncc.Suffix = ""
ncc.TelexNumber = ""
'ncc.Title = ""
ncc.TTYTDDTelephoneNumber = ""
ncc.User1 = ""
ncc.User2 = ""
ncc.User3 = ""
ncc.User4 = ""
'ncc.UserCertificate = ""
ncc.WebPage = ""
ncc.YomiCompanyName = ""
ncc.YomiFirstName = ""
ncc.YomiLastName = ""
End Sub

Sub CensorCustomFields(ncc)
ncc.UserProperties.Find("Private Notes").Value = ""
'ncc.UserProperties.Find("Project").Value = ""
'ncc.UserProperties.Find("Project Role").Value = ""

ncc.UserProperties.Find("Christmas card 2001 sent").Value = False
ncc.UserProperties.Find("Christmas card 2001 received").Value = False
ncc.UserProperties.Find("Christmas card 2002 sent").Value = False
ncc.UserProperties.Find("Christmas card 2002 received").Value = False
ncc.UserProperties.Find("Christmas card 2003 sent").Value = False
ncc.UserProperties.Find("Christmas card 2003 received").Value = False
ncc.UserProperties.Find("Christmas card 2004 sent").Value = False
ncc.UserProperties.Find("Christmas card 2004 received").Value = False
ncc.UserProperties.Find("Christmas card 2005 sent").Value = False
ncc.UserProperties.Find("Christmas card 2005 received").Value = False
ncc.UserProperties.Find("Christmas card 2006 sent").Value = False
ncc.UserProperties.Find("Christmas card 2006 received").Value = False
ncc.UserProperties.Find("Christmas card 2007 sent").Value = False
ncc.UserProperties.Find("Christmas card 2007 received").Value = False
ncc.UserProperties.Find("Christmas card 2008 sent").Value = False
ncc.UserProperties.Find("Christmas card 2008 received").Value = False
ncc.UserProperties.Find("Christmas card 2009 sent").Value = False
ncc.UserProperties.Find("Christmas card 2009 received").Value = False
ncc.UserProperties.Find("Christmas card 2010 sent").Value = False
ncc.UserProperties.Find("Christmas card 2010 received").Value = False
ncc.UserProperties.Find("Christmas card 2011 sent").Value = False
ncc.UserProperties.Find("Christmas card 2011 received").Value = False
ncc.UserProperties.Find("Christmas card 2012 sent").Value = False
ncc.UserProperties.Find("Christmas card 2012 received").Value = False
ncc.UserProperties.Find("Christmas card 2013 sent").Value = False
ncc.UserProperties.Find("Christmas card 2013 received").Value = False
ncc.UserProperties.Find("Christmas card 2014 sent").Value = False
ncc.UserProperties.Find("Christmas card 2014 received").Value = False
ncc.UserProperties.Find("Christmas card 2015 sent").Value = False
ncc.UserProperties.Find("Christmas card 2015 received").Value = False

ncc.UserProperties.Find("HSBC").Value = False
ncc.UserProperties.Find("Vents").Value = False
ncc.UserProperties.Find("CraftsC").Value = False
ncc.UserProperties.Find("V&A").Value = False
ncc.UserProperties.Find("Sci-M").Value = False
ncc.UserProperties.Find("F&T").Value = False
ncc.UserProperties.Find("Sitooterie").Value = False
ncc.UserProperties.Find("DesignM").Value = False
ncc.UserProperties.Find("Zipbags").Value = False

ncc.UserProperties.Find("Aberystwyth").Value = False
ncc.UserProperties.Find("Bleigiessen").Value = False
ncc.UserProperties.Find("Blue Carpet").Value = False
ncc.UserProperties.Find("B of the Bang").Value = False
ncc.UserProperties.Find("Guy's Hospital").Value = False
ncc.UserProperties.Find("Little Hampton").Value = False
ncc.UserProperties.Find("Longchamp NY").Value = False
ncc.UserProperties.Find("Rolling Bridge").Value = False

End Sub


'##########################################################
' Phone number reformating
'##########################################################



Function purePhoneNumber(markedNumber)
markedNumber = Join(Split(markedNumber, " "), "")
markedNumber = Join(Split(markedNumber, "("), "")
markedNumber = Join(Split(markedNumber, "-"), "")
markedNumber = Join(Split(markedNumber, "."), "")
purePhoneNumber = Join(Split(markedNumber, ")"), "")
End Function

Sub Item_PropertyChange(ByVal Name)
Select case Name
Case "AssistantTelephoneNumber"
item.AssistantTelephoneNumber =
ReformatedNumber(item.AssistantTelephoneNumber)
Case "Business2TelephoneNumber"
item.Business2TelephoneNumber =
ReformatedNumber(item.Business2TelephoneNumber)
Case "BusinessFaxNumber"
item.BusinessFaxNumber = ReformatedNumber(item.BusinessFaxNumber)
Case "BusinessTelephoneNumber"
item.BusinessTelephoneNumber =
ReformatedNumber(item.BusinessTelephoneNumber)
Case "CallbackTelephoneNumber"
item.CallbackTelephoneNumber =
ReformatedNumber(item.CallbackTelephoneNumber)
Case "CarTelephoneNumber"
item.CarTelephoneNumber = ReformatedNumber(item.CarTelephoneNumber)
Case "CompanyMainTelephoneNumber"
item.CompanyMainTelephoneNumber =
ReformatedNumber(item.CompanyMainTelephoneNumber)
Case "Home2TelephoneNumber"
item.Home2TelephoneNumber= ReformatedNumber(item.Home2TelephoneNumber)
Case "HomeFaxNumber"
item.HomeFaxNumber = ReformatedNumber(item.HomeFaxNumber)
Case "HomeTelephoneNumber"
item.HomeTelephoneNumber = ReformatedNumber(item.HomeTelephoneNumber)
Case "ISDNNumber"
item.ISDNNumber = ReformatedNumber(item.ISDNNumber)
Case "MobileTelephoneNumber"
item.MobileTelephoneNumber =
ReformatedNumber(item.MobileTelephoneNumber)
Case "OrganizationalIDNumber"
item.OrganizationalIDNumber =
ReformatedNumber(item.OrganizationalIDNumber)
Case "OtherFaxNumber"
item.OtherFaxNumber = ReformatedNumber(item.OtherFaxNumber)
Case "OtherTelephoneNumber"
item.OtherTelephoneNumber =
ReformatedNumber(item.OtherTelephoneNumber)
Case "PagerNumber"
item.PagerNumber = ReformatedNumber(item.PagerNumber)
Case "PrimaryTelephoneNumber"
item.PrimaryTelephoneNumber =
ReformatedNumber(item.PrimaryTelephoneNumber)
Case "RadioTelephoneNumber"
item.RadioTelephoneNumber =
ReformatedNumber(item.RadioTelephoneNumber)
Case "TelexNumber"
item.TelexNumber = ReformatedNumber(item.TelexNumber)
Case "TTYTDDTelephoneNumber"
item.TTYTDDTelephoneNumber =
ReformatedNumber(item.TTYTDDTelephoneNumber)
End Select
End Sub

Function ReformatedNumber(phoneNumber)
Dim NNLen
Dim CCode
Dim NNumber

phoneNumber = purePhoneNumber(phoneNumber)

If Len(phoneNumber) >= 4 Then
If Left(phoneNumber, 1) = "+" And IsNumeric(Right(Left(phoneNumber,
3), 2)) Then
NNLen = Len(phoneNumber) - 3
CCode = Left(phoneNumber, 3)
NNumber = Right(phoneNumber, NNLen)
phoneNumber = CCode & " " & NNumber
ElseIf Left(phoneNumber, 2) = "00" And
IsNumeric(Right(Left(phoneNumber, 2), 2)) Then
NNLen = Len(phoneNumber) - 4
CCode = Left(phoneNumber, 4)
NNumber = Right(phoneNumber, NNLen)
phoneNumber = "+" & Right(CCode, 2) & " " & NNumber
ElseIf Left(phoneNumber, 1) = "0" Then
NNLen = Len(phoneNumber) - 1
CCode = Left(phoneNumber, 1)
NNumber = Right(phoneNumber, NNLen)
phoneNumber = "+" & "44 " & NNumber
Else
phoneNumber = "+" & "44 " & "20" & phoneNumber
End If
If Mid(phoneNumber, 2,1) = "1" Then
phoneNumber = NANP_CCodeReformated(phoneNumber)
End If

End If
ReformatedNumber = phoneNumber

End Function

Function NANP_CCodeReformated(phoneNumber)
phoneNumber = Join(Split(phoneNumber), "")
If Len(phoneNumber) > 5 Then
phoneNumber = Left(phoneNumber, 2) & " " & _
Mid(phoneNumber, 3,3) & " " & _
Mid(phoneNumber, 6,Len(phoneNumber)-5)
Else
phoneNumber = Left(phoneNumber, 2) & " " & _
Mid(phoneNumber, 3,Len(phoneNumber)-2)
End If
NANP_CCodeReformated = phoneNumber
End Function
 
S

Sue Mosher [MVP-Outlook]

If the form doesn't run code after you have sent or saved an item using the published form, you probably have done something to "one-off" the form. Outlook 2003, Outlook 2002, Outlook 2000 SP2 and Outlook 2000 or 98 with the Email Security Update will not run code on one-off forms; see http://www.outlookcode.com/d/secforms.htm for more information on this issue.

GovernmentIDNumber is a built-in property, not a custom property. You should access it with Item.GovernmentIDNumber, not through the UserProperties collection.

FYI, there is a newsgroup specifically for Outlook forms issues "down the hall" at microsoft.public.outlook.program_forms or, via web interface, at http://www.microsoft.com/office/com...spx?dg=microsoft.public.outlook.program_forms

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers


j1313 said:
If I open a new form then save it its all good but if i then try to edit this
new contact, the VB script in the form is disabled and events do not take
place (eg at contact item write).

Some things I've tried:
* I Opened the newly saved contact where the scripts don't work and went to
tools/forms/design this form, and then in the form design window to form/view
code and the script code is all there. I go to form/run this form and the
form runs with the scripts.


I've narrowed things down to using method: "item.UserProperties.Find"



in
Function Item_Write()
...
item.UserProperties.Find("GovernmentIDNumber", False)


#############################################

Const MAX_RAND_SIZE = 1000000000
Const MIN_RAND_SIZE = 100000000
Const FOLDER_L0_MAILBOX = "Public Folders"
Const FOLDER_L1 = "All Public Folders"
Const FOLDER_L2 = "Contacts Database"


Dim cloneBunnyLoopProtection

Function MyRand()
Dim r1
r1 = (Rnd * MAX_RAND_SIZE) Mod MAX_RAND_SIZE
If r1 < 100000000 Then
MyRand = MyRand()
Else
MyRand = r1
End If
End Function

'just for mailboxes
Function getMailboxFolder(mbName)
For Each mailbox In Application.Session.Folders
If InStr(mailbox.Name, mbName) Then
Set getMailboxFolder = mailbox
End If
Next
End Function

'generic folder search function
Function getFolderWithStringInFolder(folderName, parentFolder)
For Each Folder In parentFolder.Folders
If InStr(Folder.Name, folderName) Then
Set getFolderWithStringInFolder = Folder
End If
Next
End Function

Function Item_Open()
cloneBunnyLoopProtection = True
End Function

Function Item_Read()
End Function

Function Item_Write()
Dim currFolder
Dim pubFolder
Dim pubContact
Dim currGovID
Dim pubGovID
msgbox "form - write"
If cloneBunnyLoopProtection then
set currGovID = item.UserProperties.Find("GovernmentIDNumber", False)
If currGovID.Value = "" Then 'new contact
Randomize
currGovID.Value = "id" & MyRand()
Randomize
currGovID.Value = currGovID.Value & MyRand()
Randomize
currGovID.Value = currGovID.Value & MyRand()
Else 'else find the public copy of contact
Set currFolder = getMailboxFolder(FOLDER_L0_MAILBOX )
Set currFolder = getFolderWithStringInFolder(FOLDER_L1,
currFolder)
Set pubFolder = getFolderWithStringInFolder(FOLDER_L2, currFolder)
set pubContact = pubFolder.Items.Find("[GovernmentIDNumber] = "
& currGovID.Value)
If VarType(pubContact) = 8 Then
set pubGovID =
pubContact.UserProperties.Find("GovernmentIDNumber", False)
If pubGovID.Value = currGovID.Value Then
pubContact.Delete
End if
End if
End If
CreateCensoredContact()
End If
cloneBunnyLoopProtection = False
End Function

Sub CreateCensoredContact()
Dim currFolder
Dim pubFolder
Dim ncc

Set currFolder = getMailboxFolder(FOLDER_L0_MAILBOX )
Set currFolder = getFolderWithStringInFolder(FOLDER_L1, currFolder)
Set pubFolder = getFolderWithStringInFolder(FOLDER_L2, currFolder)

Set ncc = item.copy

CensorContactFields(ncc)
CensorCustomFields(ncc)
ncc.Move pubFolder
End Sub

Sub CensorContactFields(ncc)
ncc.Account = ""
ncc.AssistantName = ""
ncc.AssistantTelephoneNumber = ""
ncc.BillingInformation = ""
'ncc.Body = "" 'Notes Field
'ncc.Business2TelephoneNumber = ""
'ncc.BusinessAddress = ""
'ncc.BusinessAddressCity = ""
'ncc.BusinessAddressCountry = ""
'ncc.BusinessAddressPostalCode = ""
'ncc.BusinessAddressPostOfficeBox = ""
'ncc.BusinessAddressState = ""
'ncc.BusinessAddressStreet = ""
'ncc.BusinessFaxNumber = ""
'ncc.BusinessHomePage = ""
'ncc.BusinessTelephoneNumber = ""
ncc.CallbackTelephoneNumber = ""
ncc.CarTelephoneNumber = ""
'ncc.Categories = ""
ncc.Children = ""
'ncc.Companies = ""
ncc.CompanyMainTelephoneNumber = ""
'ncc.CompanyName = ""
ncc.ComputerNetworkName = ""
'ncc.CustomerID = ""
'ncc.Department = ""
'ncc.Email1Address = ""
'ncc.Email1AddressType = ""
'ncc.Email1DisplayName = ""
ncc.Email2Address = ""
ncc.Email2AddressType = ""
ncc.Email2DisplayName = ""
ncc.Email3Address = ""
ncc.Email3AddressType = ""
ncc.Email3DisplayName = ""
'ncc.FileAs = ""
'ncc.FirstName = ""
ncc.FormDescription = ""
ncc.FTPSite = ""
'ncc.FullName = " "
'ncc.GovernmentIDNumber = ""


ncc.Hobby = ""
ncc.Home2TelephoneNumber = ""
ncc.HomeAddress = ""
ncc.HomeAddressCity = ""
ncc.HomeAddressCountry = ""
ncc.HomeAddressPostalCode = ""
ncc.HomeAddressPostOfficeBox = ""
ncc.HomeAddressState = ""
ncc.HomeAddressStreet = ""
ncc.HomeFaxNumber = ""
ncc.HomeTelephoneNumber = ""
ncc.IMAddress = ""
'ncc.Initials = ""
ncc.InternetFreeBusyAddress = ""
ncc.ISDNNumber = ""
'ncc.JobTitle = ""
ncc.Language = ""
'ncc.LastName = ""
'ncc.MailingAddress = ""
'ncc.MailingAddressCity = ""
'ncc.MailingAddressCountry = ""
'ncc.MailingAddressPostalCode = ""
'ncc.MailingAddressPostOfficeBox = ""
'ncc.MailingAddressState = ""
'ncc.MailingAddressStreet = ""
ncc.ManagerName = ""
ncc.MessageClass = ""
'ncc.MiddleName = ""
ncc.Mileage = ""
ncc.MobileTelephoneNumber = ""
ncc.NetMeetingAlias = ""
ncc.NetMeetingServer = ""
'ncc.NickName = ""

ncc.OfficeLocation = ""
ncc.OrganizationalIDNumber = ""
ncc.OtherAddress = ""
ncc.OtherAddressCity = ""
ncc.OtherAddressCountry = ""
ncc.OtherAddressPostalCode = ""
ncc.OtherAddressPostOfficeBox = ""
ncc.OtherAddressState = ""
ncc.OtherAddressStreet = ""
ncc.OtherFaxNumber = ""
ncc.OtherTelephoneNumber = ""
ncc.PagerNumber = ""
ncc.PersonalHomePage = ""
ncc.PrimaryTelephoneNumber = ""
'ncc.Profession = ""
ncc.RadioTelephoneNumber = ""
ncc.ReferredBy = ""
ncc.Spouse = ""
'ncc.Subject = ""
'ncc.Suffix = ""
ncc.TelexNumber = ""
'ncc.Title = ""
ncc.TTYTDDTelephoneNumber = ""
ncc.User1 = ""
ncc.User2 = ""
ncc.User3 = ""
ncc.User4 = ""
'ncc.UserCertificate = ""
ncc.WebPage = ""
ncc.YomiCompanyName = ""
ncc.YomiFirstName = ""
ncc.YomiLastName = ""
End Sub

Sub CensorCustomFields(ncc)
ncc.UserProperties.Find("Private Notes").Value = ""
'ncc.UserProperties.Find("Project").Value = ""
'ncc.UserProperties.Find("Project Role").Value = ""

ncc.UserProperties.Find("Christmas card 2001 sent").Value = False
ncc.UserProperties.Find("Christmas card 2001 received").Value = False
ncc.UserProperties.Find("Christmas card 2002 sent").Value = False
ncc.UserProperties.Find("Christmas card 2002 received").Value = False
ncc.UserProperties.Find("Christmas card 2003 sent").Value = False
ncc.UserProperties.Find("Christmas card 2003 received").Value = False
ncc.UserProperties.Find("Christmas card 2004 sent").Value = False
ncc.UserProperties.Find("Christmas card 2004 received").Value = False
ncc.UserProperties.Find("Christmas card 2005 sent").Value = False
ncc.UserProperties.Find("Christmas card 2005 received").Value = False
ncc.UserProperties.Find("Christmas card 2006 sent").Value = False
ncc.UserProperties.Find("Christmas card 2006 received").Value = False
ncc.UserProperties.Find("Christmas card 2007 sent").Value = False
ncc.UserProperties.Find("Christmas card 2007 received").Value = False
ncc.UserProperties.Find("Christmas card 2008 sent").Value = False
ncc.UserProperties.Find("Christmas card 2008 received").Value = False
ncc.UserProperties.Find("Christmas card 2009 sent").Value = False
ncc.UserProperties.Find("Christmas card 2009 received").Value = False
ncc.UserProperties.Find("Christmas card 2010 sent").Value = False
ncc.UserProperties.Find("Christmas card 2010 received").Value = False
ncc.UserProperties.Find("Christmas card 2011 sent").Value = False
ncc.UserProperties.Find("Christmas card 2011 received").Value = False
ncc.UserProperties.Find("Christmas card 2012 sent").Value = False
ncc.UserProperties.Find("Christmas card 2012 received").Value = False
ncc.UserProperties.Find("Christmas card 2013 sent").Value = False
ncc.UserProperties.Find("Christmas card 2013 received").Value = False
ncc.UserProperties.Find("Christmas card 2014 sent").Value = False
ncc.UserProperties.Find("Christmas card 2014 received").Value = False
ncc.UserProperties.Find("Christmas card 2015 sent").Value = False
ncc.UserProperties.Find("Christmas card 2015 received").Value = False

ncc.UserProperties.Find("HSBC").Value = False
ncc.UserProperties.Find("Vents").Value = False
ncc.UserProperties.Find("CraftsC").Value = False
ncc.UserProperties.Find("V&A").Value = False
ncc.UserProperties.Find("Sci-M").Value = False
ncc.UserProperties.Find("F&T").Value = False
ncc.UserProperties.Find("Sitooterie").Value = False
ncc.UserProperties.Find("DesignM").Value = False
ncc.UserProperties.Find("Zipbags").Value = False

ncc.UserProperties.Find("Aberystwyth").Value = False
ncc.UserProperties.Find("Bleigiessen").Value = False
ncc.UserProperties.Find("Blue Carpet").Value = False
ncc.UserProperties.Find("B of the Bang").Value = False
ncc.UserProperties.Find("Guy's Hospital").Value = False
ncc.UserProperties.Find("Little Hampton").Value = False
ncc.UserProperties.Find("Longchamp NY").Value = False
ncc.UserProperties.Find("Rolling Bridge").Value = False

End Sub


'##########################################################
' Phone number reformating
'##########################################################



Function purePhoneNumber(markedNumber)
markedNumber = Join(Split(markedNumber, " "), "")
markedNumber = Join(Split(markedNumber, "("), "")
markedNumber = Join(Split(markedNumber, "-"), "")
markedNumber = Join(Split(markedNumber, "."), "")
purePhoneNumber = Join(Split(markedNumber, ")"), "")
End Function

Sub Item_PropertyChange(ByVal Name)
Select case Name
Case "AssistantTelephoneNumber"
item.AssistantTelephoneNumber =
ReformatedNumber(item.AssistantTelephoneNumber)
Case "Business2TelephoneNumber"
item.Business2TelephoneNumber =
ReformatedNumber(item.Business2TelephoneNumber)
Case "BusinessFaxNumber"
item.BusinessFaxNumber = ReformatedNumber(item.BusinessFaxNumber)
Case "BusinessTelephoneNumber"
item.BusinessTelephoneNumber =
ReformatedNumber(item.BusinessTelephoneNumber)
Case "CallbackTelephoneNumber"
item.CallbackTelephoneNumber =
ReformatedNumber(item.CallbackTelephoneNumber)
Case "CarTelephoneNumber"
item.CarTelephoneNumber = ReformatedNumber(item.CarTelephoneNumber)
Case "CompanyMainTelephoneNumber"
item.CompanyMainTelephoneNumber =
ReformatedNumber(item.CompanyMainTelephoneNumber)
Case "Home2TelephoneNumber"
item.Home2TelephoneNumber= ReformatedNumber(item.Home2TelephoneNumber)
Case "HomeFaxNumber"
item.HomeFaxNumber = ReformatedNumber(item.HomeFaxNumber)
Case "HomeTelephoneNumber"
item.HomeTelephoneNumber = ReformatedNumber(item.HomeTelephoneNumber)
Case "ISDNNumber"
item.ISDNNumber = ReformatedNumber(item.ISDNNumber)
Case "MobileTelephoneNumber"
item.MobileTelephoneNumber =
ReformatedNumber(item.MobileTelephoneNumber)
Case "OrganizationalIDNumber"
item.OrganizationalIDNumber =
ReformatedNumber(item.OrganizationalIDNumber)
Case "OtherFaxNumber"
item.OtherFaxNumber = ReformatedNumber(item.OtherFaxNumber)
Case "OtherTelephoneNumber"
item.OtherTelephoneNumber =
ReformatedNumber(item.OtherTelephoneNumber)
Case "PagerNumber"
item.PagerNumber = ReformatedNumber(item.PagerNumber)
Case "PrimaryTelephoneNumber"
item.PrimaryTelephoneNumber =
ReformatedNumber(item.PrimaryTelephoneNumber)
Case "RadioTelephoneNumber"
item.RadioTelephoneNumber =
ReformatedNumber(item.RadioTelephoneNumber)
Case "TelexNumber"
item.TelexNumber = ReformatedNumber(item.TelexNumber)
Case "TTYTDDTelephoneNumber"
item.TTYTDDTelephoneNumber =
ReformatedNumber(item.TTYTDDTelephoneNumber)
End Select
End Sub

Function ReformatedNumber(phoneNumber)
Dim NNLen
Dim CCode
Dim NNumber

phoneNumber = purePhoneNumber(phoneNumber)

If Len(phoneNumber) >= 4 Then
If Left(phoneNumber, 1) = "+" And IsNumeric(Right(Left(phoneNumber,
3), 2)) Then
NNLen = Len(phoneNumber) - 3
CCode = Left(phoneNumber, 3)
NNumber = Right(phoneNumber, NNLen)
phoneNumber = CCode & " " & NNumber
ElseIf Left(phoneNumber, 2) = "00" And
IsNumeric(Right(Left(phoneNumber, 2), 2)) Then
NNLen = Len(phoneNumber) - 4
CCode = Left(phoneNumber, 4)
NNumber = Right(phoneNumber, NNLen)
phoneNumber = "+" & Right(CCode, 2) & " " & NNumber
ElseIf Left(phoneNumber, 1) = "0" Then
NNLen = Len(phoneNumber) - 1
CCode = Left(phoneNumber, 1)
NNumber = Right(phoneNumber, NNLen)
phoneNumber = "+" & "44 " & NNumber
Else
phoneNumber = "+" & "44 " & "20" & phoneNumber
End If
If Mid(phoneNumber, 2,1) = "1" Then
phoneNumber = NANP_CCodeReformated(phoneNumber)
End If

End If
ReformatedNumber = phoneNumber

End Function

Function NANP_CCodeReformated(phoneNumber)
phoneNumber = Join(Split(phoneNumber), "")
If Len(phoneNumber) > 5 Then
phoneNumber = Left(phoneNumber, 2) & " " & _
Mid(phoneNumber, 3,3) & " " & _
Mid(phoneNumber, 6,Len(phoneNumber)-5)
Else
phoneNumber = Left(phoneNumber, 2) & " " & _
Mid(phoneNumber, 3,Len(phoneNumber)-2)
End If
NANP_CCodeReformated = phoneNumber
End Function
Click to expand...
 
J

jkos13

Using "Item.GovernmentIDNumber" instead of
"Item.UserProperties.Find("GovernmentIDNumber", False)" has worked.

many thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Word macro that can call cells out of Excel for a find and replace 0
VBA Coding Help for Beginner 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
type mismatch error in macro in excel 0
please help.is it possible re-edit macro to convert text like1530-2200 to time format automatically? 1
Sql dbSeeChanges error 4
Key Card Software (Auric) 7
WORD VBA Search and switch to advanced ... 1

Top