Import contacts to a shared mailbox

Status
Not open for further replies.

Sdg8481

New Member
Outlook version
Outlook 2013 32 bit
Email Account
IMAP
Hi,

I have the following macro that takes a list of email addresses in Excel and creates/updates an outlook distribution list under the 'My Contacts' section in Outlook - and this works perfectly.
However, how can i adapt this code so that it creates/updates the contacts in a shared mailbox called "Shared Test" instead of just on my mailbox. Any help would be greatly appreciated. Stu

Const DISTLISTNAME As String = "Test"
Const olDistributionListItem = 7
Const olFolderContacts = 10

Sub test() 'Worksheet_Change(ByVal Target As Range)

Dim outlook As Object ' Outlook.Application
Dim contacts As Object ' Outlook.Items
Dim myDistList As Object ' Outlook.DistListItem
Dim newDistList As Object ' Outlook.DistListItem
Dim objRcpnt As Object ' Outlook.Recipient
Dim arrData() As Variant
Dim rng As Excel.Range
Dim numRows As Long
Dim numCols As Long
Dim i As Long
Dim msg As String

msg = "Worksheet has been changed, would you like to update distribution list?"

If MsgBox(msg, vbYesNo) = vbNo Then
Exit Sub
End If

Set outlook = GetOutlookApp
Set contacts = GetItems(GetNS(outlook))

On Error Resume Next
Set myDistList = contacts.Item(DISTLISTNAME)
On Error GoTo 0

If Not myDistList Is Nothing Then
' delete it
myDistList.Delete
End If

' recreate it
Set newDistList = outlook.CreateItem(olDistributionListItem)

With newDistList
.DLName = DISTLISTNAME
.Body = DISTLISTNAME
End With

' loop through worksheet and add each member to dist list
numRows = Range("A1").CurrentRegion.Rows.Count - 1
numCols = Range("A1").CurrentRegion.Columns.Count

ReDim arrData(1 To numRows, 1 To numCols)

' take header out of range
Set rng = Range("A1").CurrentRegion.Offset(1, 0).Resize(numRows, numCols)
' put range into array
arrData = rng.Value

' assume 2 cols (name and emails only)
For i = 1 To numRows
'little variation on your theme ...
Set objRcpnt = outlook.Session.CreateRecipient(arrData(i, 1) & "<" & arrData(i, 2) & ">")
'end of variation
objRcpnt.Resolve
newDistList.AddMember objRcpnt
Next i

newDistList.Save
'newDistList.Display

End Sub

Function GetOutlookApp() As Object
On Error Resume Next
Set GetOutlookApp = CreateObject("Outlook.Application")
End Function

'To My Contacts
Function GetItems(olNS As Object) As Object
Set GetItems = olNS.GetDefaultFolder(olFolderContacts).Items
End Function

Function GetNS(ByRef app As Object) As Object
Set GetNS = app.GetNamespace("MAPI")
End Function
 
Status
Not open for further replies.
Thread starter Similar threads Forum Replies Date
M PST import from Outlook 2007 to 2010 - Address Book contacts all in 1 group Using Outlook 4
C Import Outlook 2016 contacts into to: field Using Outlook 1
Diane Poremsky Batch Import Photos into Outlook Contacts New Slipstick.com Articles 0
Diane Poremsky Macro to Bulk Import Contacts and vCards into Outlook New Slipstick.com Articles 0
G Import pst but Calendar and Contacts don't show. Using Outlook 12
K Import Photos into Contacts Using Outlook 3
F import individual contact info from excel to outlook contacts or vice versa Using Outlook 2
H Import contacts from Outlook.pst file into Outlook 2010 Using Outlook 2
J BCM Import Accounts related to contacts Using Outlook 4
N Import Contacts Excel CSV File into Outlook 2010 Using Outlook 2
J Import Outlook Contacts and Calendar from XP Home to Windows 7 Outlook 2010 Using Outlook 1
D Outlook will not import backup copy of contacts. I have tried file-import-etc. but nothing happens. Using Outlook 3
K How do I import e-mails, folders with saved e-mails, contacts and calender to Office 2010. Using Outlook 2
Q How to import contacts, and email from Microsoft Office Outlook 2007 to Windows Live Mail? Using Outlook 5
A How can I import my Contacts from the Windows Contacts Folder to my Outlook? Using Outlook 2
N Export contacts to .csv to import into Hotmail Using Outlook 15
S how do I import my contacts from windows mail to office 2010. I am using vista Using Outlook 1
D Import Contacts .pst problem Using Outlook 2
D Need to export contacts but the export/import option does not show in the advanced options tab as the HELP option says it will Using Outlook 4
C How to import the contacts into Outlook Using Outlook 2
I Outlook 2010 Shutting Down When Trying to Import Contacts to Business Contact Manager Using Outlook 3
S I can't import my Contacts/Address Book from Outlook Express 6 into Outlook 2010. I only receive my portion of the contacts. Using Outlook 1
W unable to import 2007 contacts email, inbox to 2010 outlook programs Using Outlook 1
J how do i retrieve / import / download my old emails and contacts Using Outlook 2
N import contacts from Windows Live to Outlook__ Using Outlook 2
C I need to import my calendar and contacts from gmail - how do I do this? Using Outlook 1
J CSV Import to contacts won't continue... Using Outlook 3
K Business Contact Manager Contact Import of 3000 contacts already in Outlook 2010 Using Outlook 2
M How do I import contacts from Lotus Organizer to MS-Outlook? Using Outlook 2
B Can I import my snail mail contacts into outlook 2003. If yes, how do I do that? Using Outlook 2
J Cannot import contacts from a box running 2003 to a new laptop running 2010 Using Outlook 1
L Import/Export of Contacts from/to Excel does not import E-Mail addresses Using Outlook 2
E Trying to import my outlook 2003 contacts into outlook 2010 Using Outlook 7
C How do you import email addresses from Excel to your Outlook Contacts list? Using Outlook 0
J How do I import my backed up Outlook 2003 info (emails, contacts, etc.) into my new computer with Outlook 2010..?? Using Outlook 0
M Import calendar and contacts from Palm Desktop to Outlook Using Outlook 6
N Outlook 2010 - Import emails and contacts (address book) from Thunderbird 3.0 Using Outlook 7
S How do I Import my contacts located in a .pst file in to Windows live mail! Using Outlook 6
K How to import BCM data to Outlook Contacts? BCM (Business Contact Manager) 1
K Update contacts in BCM via new import BCM (Business Contact Manager) 1
C Way to import contacts from Excel into BCM Contacts? BCM (Business Contact Manager) 1
I Outlook 365 - import/attach PST file that used POP3 Using Outlook.com accounts in Outlook 0
M Export-Import .pst file problems Using Outlook 2
N .pst archive from work will not open/import on Microsoft 365 Exchange Server Administration 0
L How to Import Exchange OST file into Outlook? Using Outlook 3
Christopher M Import Exchange Server Administration 1
R PST->Outlook.com (Import vs Drag-n-Drop methods Using Outlook.com accounts in Outlook 2
DoctorJellybean Import accounts\files Using Outlook 1
P Import Categories from Outlook 2003 Using Outlook 8
avant-guvnor Import csv problem Using Outlook 7
Similar threads


















































Top