I have an excel spreadsheet that we keep our contacts in. Occasionally, we
need to replace one. When this happens, we just run a macro and it creates
the new contact in Outlook. Thanks to Gram Mayors template on this. But,
now we must go to Outlook and delete the old one. Not a problem for most
people, however, most of my co-workers are soon to be "Darwin Award Winners."
So, I've been tasked with developing a macro that will also delete the old
contact and create the new contact. As I said, I've got the code for the new
contact, but any ideas on how to delete a contact from Excel would be
welcomed. Thanks in advance for any help!

Sub TryNow()
Dim objOL As Object
Dim myNS As Outlook.Namespace
Dim ConName As String

ConName = "Mathew"

Set objOL = CreateObject("outlook.application")
Set myNS = objOL.GetNamespace("MAPI")

If myNS.AddressLists("Contacts").AddressEntries(ConNa me).Name = ConName Then
myNS.AddressLists("Contacts").AddressEntries(ConNa me).Delete
End If

Set myNS = Nothing
Set objOL = Nothing

End Sub

If a contact name doesn't exist, then the next one is returned - hence the check...

HTH,
Bernie
MS Excel MVP


"Mathew" wrote in message
...
I have an excel spreadsheet that we keep our contacts in. Occasionally, we
need to replace one. When this happens, we just run a macro and it creates
the new contact in Outlook. Thanks to Gram Mayors template on this. But,
now we must go to Outlook and delete the old one. Not a problem for most
people, however, most of my co-workers are soon to be "Darwin Award Winners."
So, I've been tasked with developing a macro that will also delete the old
contact and create the new contact. As I said, I've got the code for the new
contact, but any ideas on how to delete a contact from Excel would be
welcomed. Thanks in advance for any help!

Bernie: Thanks for the help. However, the code did not function. I get a
funny error: Run-Time Error -2147221233 (8004010f)': when the code reaches
this line:
If myNS.AddressLists("Contacts").AddressEntries(ConNa me).Name = ConName Then
Another wrinkle is that I get a message saying that another program is
trying to access Outlook and ask permission to allow this. I check Ok and
the program still fails. Any ideas??


"Bernie Deitrick" wrote:

Sub TryNow()
Dim objOL As Object
Dim myNS As Outlook.Namespace
Dim ConName As String

ConName = "Mathew"

Set objOL = CreateObject("outlook.application")
Set myNS = objOL.GetNamespace("MAPI")

If myNS.AddressLists("Contacts").AddressEntries(ConNa me).Name = ConName Then
myNS.AddressLists("Contacts").AddressEntries(ConNa me).Delete
End If

Set myNS = Nothing
Set objOL = Nothing

End Sub

If a contact name doesn't exist, then the next one is returned - hence the check...

HTH,
Bernie
MS Excel MVP


"Mathew" wrote in message
...
I have an excel spreadsheet that we keep our contacts in. Occasionally, we
need to replace one. When this happens, we just run a macro and it creates
the new contact in Outlook. Thanks to Gram Mayors template on this. But,
now we must go to Outlook and delete the old one. Not a problem for most
people, however, most of my co-workers are soon to be "Darwin Award Winners."
So, I've been tasked with developing a macro that will also delete the old
contact and create the new contact. As I said, I've got the code for the new
contact, but any ideas on how to delete a contact from Excel would be
welcomed. Thanks in advance for any help!

Bernie: Thanks for the help. I got the problem solved!!



"Mathew" wrote:

Bernie: Thanks for the help. However, the code did not function. I get a
funny error: Run-Time Error -2147221233 (8004010f)': when the code reaches
this line:
If myNS.AddressLists("Contacts").AddressEntries(ConNa me).Name = ConName Then
Another wrinkle is that I get a message saying that another program is
trying to access Outlook and ask permission to allow this. I check Ok and
the program still fails. Any ideas??


"Bernie Deitrick" wrote:

Sub TryNow()
Dim objOL As Object
Dim myNS As Outlook.Namespace
Dim ConName As String

ConName = "Mathew"

Set objOL = CreateObject("outlook.application")
Set myNS = objOL.GetNamespace("MAPI")

If myNS.AddressLists("Contacts").AddressEntries(ConNa me).Name = ConName Then
myNS.AddressLists("Contacts").AddressEntries(ConNa me).Delete
End If

Set myNS = Nothing
Set objOL = Nothing

End Sub

If a contact name doesn't exist, then the next one is returned - hence the check...

HTH,
Bernie
MS Excel MVP


"Mathew" wrote in message
...
I have an excel spreadsheet that we keep our contacts in. Occasionally, we
need to replace one. When this happens, we just run a macro and it creates
the new contact in Outlook. Thanks to Gram Mayors template on this. But,
now we must go to Outlook and delete the old one. Not a problem for most
people, however, most of my co-workers are soon to be "Darwin Award Winners."
So, I've been tasked with developing a macro that will also delete the old
contact and create the new contact. As I said, I've got the code for the new
contact, but any ideas on how to delete a contact from Excel would be
welcomed. Thanks in advance for any help!