(Bear with me here as I will probably just be spitballing at the start). So I have a list of names that gets updated every so often with contact names and email addresses for companies. Instead of having to do an import, I am trying to automate the process of importing using VBA.
Firstly, I want to be able to navigate to certain folders within contacts to add the contacts from excel to these different folders. Contacts from one sheet will be added to one folder and ones from a second sheet will be added to a second folder.
Secondly, I will be wanting to delete all contacts at least once every few months and do a full re-import. The re-import should be easy enough, but not sure about deleting contacts within a folder
Finally I will be looking at a third folder (which will be full of email addresses who have unsubscribed to our catalog) which I will want to look at sheets 1 and 2 and if the name/email address appear in those sheets, move that row to sheet 3 so it does not get reimported into the Outlook folders.
So, now to the code. I am basically wanting to know if this whole lot would be possible. I assume it should be, but wanted to actually make sure of this before starting and muddling through.
To add to a set folder, can I just run something like:
or would that not work? Another thing I have to think of is some of the people using this have 2 mailboxes. One from mail@domain.com and one as their own. Would I need to set the folder to refer to "mail@domain.com\Contacts\Set 1" in that case?
For deleting, I found this bit of code:
If I set a MyFolder variable with the folder I need and put in a for each myContacts in MyFolder instead of the myItems in myContacts, I presume it would work OK... am I correct? Would the same olitem as above serve for this purpose? Also, the GetNamespace("MAPI"), what exactly is that for?
Finally, If I looked in Outlook.Application.Folders("Contacts\Unsub").Items and ran a for each on ContactItem, am I correct in thinking I should be able to extract the Name, Company and Email as variables using ContactItem.Name, ContactItem.Comapny and ContactItem.Email1Address?
Thanks for reading through my mini-mammoth post. I thought I would at least seek advice before doing this so as to not waste a day or so trying to code and bug-test it only to find out it doesnt work!
Cheers
Ben
Firstly, I want to be able to navigate to certain folders within contacts to add the contacts from excel to these different folders. Contacts from one sheet will be added to one folder and ones from a second sheet will be added to a second folder.
Secondly, I will be wanting to delete all contacts at least once every few months and do a full re-import. The re-import should be easy enough, but not sure about deleting contacts within a folder
Finally I will be looking at a third folder (which will be full of email addresses who have unsubscribed to our catalog) which I will want to look at sheets 1 and 2 and if the name/email address appear in those sheets, move that row to sheet 3 so it does not get reimported into the Outlook folders.
So, now to the code. I am basically wanting to know if this whole lot would be possible. I assume it should be, but wanted to actually make sure of this before starting and muddling through.
To add to a set folder, can I just run something like:
Code:
set olitem = Outlook.Application.Folders("Contacts\Set 1").CreateItem(olContactItem)
with olitem
.name="Joe Bloggs"
.Email1Address = "joe@bloggs.com"
end with
or would that not work? Another thing I have to think of is some of the people using this have 2 mailboxes. One from mail@domain.com and one as their own. Would I need to set the folder to refer to "mail@domain.com\Contacts\Set 1" in that case?
For deleting, I found this bit of code:
Code:
[COLOR=#7f0055][FONT=monospace][B]Sub [/B][/FONT][/COLOR][COLOR=#000000][COLOR=#000000][FONT=monospace]DeleteaContact[/FONT][/COLOR][/COLOR][COLOR=#000000][COLOR=#000000][FONT=monospace]()[/FONT][/COLOR][/COLOR]
[COLOR=#ffffff][FONT=monospace] [/FONT][/COLOR][COLOR=#7f0055][FONT=monospace][B]Dim [/B][/FONT][/COLOR][COLOR=#000000][COLOR=#000000][FONT=monospace]myOutlook [/FONT][/COLOR][/COLOR][COLOR=#7f0055][FONT=monospace][B]As [/B][/FONT][/COLOR][COLOR=#000000][COLOR=#000000][FONT=monospace]Outlook.Application[/FONT][/COLOR][/COLOR]
[COLOR=#ffffff][FONT=monospace] [/FONT][/COLOR][COLOR=#7f0055][FONT=monospace][B]Dim [/B][/FONT][/COLOR][COLOR=#000000][COLOR=#000000][FONT=monospace]myInformation [/FONT][/COLOR][/COLOR][COLOR=#7f0055][FONT=monospace][B]As [/B][/FONT][/COLOR][COLOR=#000000][COLOR=#000000][FONT=monospace]Namespace[/FONT][/COLOR][/COLOR]
[COLOR=#ffffff][FONT=monospace] [/FONT][/COLOR][COLOR=#7f0055][FONT=monospace][B]Dim [/B][/FONT][/COLOR][COLOR=#000000][COLOR=#000000][FONT=monospace]myContacts [/FONT][/COLOR][/COLOR][COLOR=#7f0055][FONT=monospace][B]As [/B][/FONT][/COLOR][COLOR=#000000][COLOR=#000000][FONT=monospace]Items[/FONT][/COLOR][/COLOR]
[COLOR=#ffffff][FONT=monospace] [/FONT][/COLOR][COLOR=#7f0055][FONT=monospace][B]Dim [/B][/FONT][/COLOR][COLOR=#000000][COLOR=#000000][FONT=monospace]myItems [/FONT][/COLOR][/COLOR][COLOR=#7f0055][FONT=monospace][B]As [/B][/FONT][/COLOR][COLOR=#000000][COLOR=#000000][FONT=monospace]ContactItem[/FONT][/COLOR][/COLOR]
[COLOR=#ffffff][FONT=monospace][/FONT][/COLOR]
[COLOR=#ffffff][FONT=monospace] [/FONT][/COLOR][COLOR=#000000][COLOR=#000000][FONT=monospace]Set myOutlook = CreateObject[/FONT][/COLOR][/COLOR][COLOR=#000000][COLOR=#000000][FONT=monospace]([/FONT][/COLOR][/COLOR][COLOR=#2a00ff][FONT=monospace]"Outlook.Application"[/FONT][/COLOR][COLOR=#000000][COLOR=#000000][FONT=monospace])[/FONT][/COLOR][/COLOR]
[COLOR=#ffffff][FONT=monospace] [/FONT][/COLOR][COLOR=#000000][COLOR=#000000][FONT=monospace]Set myInformation = myOutlook.GetNamespace[/FONT][/COLOR][/COLOR][COLOR=#000000][COLOR=#000000][FONT=monospace]([/FONT][/COLOR][/COLOR][COLOR=#2a00ff][FONT=monospace]"MAPI"[/FONT][/COLOR][COLOR=#000000][COLOR=#000000][FONT=monospace])[/FONT][/COLOR][/COLOR]
[COLOR=#ffffff][FONT=monospace] [/FONT][/COLOR][COLOR=#000000][COLOR=#000000][FONT=monospace]Set myContacts = myInformation.GetDefaultFolder[/FONT][/COLOR][/COLOR][COLOR=#000000][COLOR=#000000][FONT=monospace]([/FONT][/COLOR][/COLOR][COLOR=#000000][COLOR=#000000][FONT=monospace]olFolderContacts[/FONT][/COLOR][/COLOR][COLOR=#000000][COLOR=#000000][FONT=monospace])[/FONT][/COLOR][/COLOR][COLOR=#000000][COLOR=#000000][FONT=monospace].Items[/FONT][/COLOR][/COLOR]
[COLOR=#ffffff][FONT=monospace][/FONT][/COLOR]
[COLOR=#ffffff][FONT=monospace] [/FONT][/COLOR][COLOR=#000000][COLOR=#000000][FONT=monospace]For Each myItems In myContacts[/FONT][/COLOR][/COLOR]
[COLOR=#ffffff][FONT=monospace] [/FONT][/COLOR][COLOR=#000000][COLOR=#000000][FONT=monospace]myItems.Delete[/FONT][/COLOR][/COLOR]
[COLOR=#ffffff][FONT=monospace] [/FONT][/COLOR][COLOR=#7f0055][FONT=monospace][B]End If[/B][/FONT][/COLOR]
[COLOR=#ffffff][FONT=monospace] [/FONT][/COLOR][COLOR=#000000][COLOR=#000000][FONT=monospace]Next[/FONT][/COLOR][/COLOR]
[COLOR=#7f0055][FONT=monospace][B]End Sub[/B][/FONT][/COLOR]
If I set a MyFolder variable with the folder I need and put in a for each myContacts in MyFolder instead of the myItems in myContacts, I presume it would work OK... am I correct? Would the same olitem as above serve for this purpose? Also, the GetNamespace("MAPI"), what exactly is that for?
Finally, If I looked in Outlook.Application.Folders("Contacts\Unsub").Items and ran a for each on ContactItem, am I correct in thinking I should be able to extract the Name, Company and Email as variables using ContactItem.Name, ContactItem.Comapny and ContactItem.Email1Address?
Thanks for reading through my mini-mammoth post. I thought I would at least seek advice before doing this so as to not waste a day or so trying to code and bug-test it only to find out it doesnt work!
Cheers
Ben