Hi
Forgot, you need to set a reference to the Outlook object library so
that the code can recognize the objects. In the editor do Tools,
References and click on the outlook one (10, 11, ...). close and open
excel if you havn't already (to reset public variables)
regards
Paul
On Jun 26, 3:58*pm, RyanH <Ry...@discussions.microsoft.com> wrote:
> This a may take me a minute to figure this out to my application. *I am
> getting an error with the Outlook.Application is not a User Defined Type,is
> it?
> --
> Cheers,
> Ryan
>
>
>
> "paul.robin...@it-tallaght.ie" wrote:
> > Hi
> > The declarations section here and the first two subs can be used to
> > connect & disconnect with Outlook from Excel which you will be able to
> > use as is.
> > The last sub I use to create a Contacts folder from information in
> > excel (i.e. Excel TO Outlook). It takes information from four columns
> > starting from A1 (and assumes first row is headers). You should be
> > able to reverse the write bit
> > i.e. With objStudent
> > * * * * * * * * .LastName = MailInfo(i + 1, 1)
> > * * * * * * * * .FirstName = MailInfo(i + 1, 2)
> > * * * * * * * * .Email1Address = MailInfo(i + 1, 3)
> > * * * * * * * * .BusinessAddress = MailInfo(i + 1, 4)
> > * * * * * * * * .Save
> > * * * End With
>
> > to go from Outlook to Excel by reading the info from objFolder =
> > objNamespace.GetDefaultFolder(olFolderContacts)
>
> > regards
> > Paul
>
> > ***CODE****
> > Option Explicit
> > Option Base 1
> > Public objOutlook As Outlook.Application
> > Public objNamespace As Outlook.Namespace
> > Public OutlookWasRunning As Boolean
>
> > 'Interface with Outlook objects required will be through this sub.
> > 'It is called by any sub requiring access to Outlook.
> > Public Sub DeclareOutlookObjects()
> > Application.ScreenUpdating = False
> > * * On Error Resume Next
> > * * * * Err.Clear
> > * * * * Set objOutlook = GetObject(, "Outlook.Application") 'If
> > Outlook is already open, flag it with Boolean
> > * * * * If Err.Number <> 0 Then OutlookWasRunning = False Else
> > OutlookWasRunning = True
> > * * On Error GoTo 0
> > * * Err.Clear * 'keep err tidy
> > * * If Not OutlookWasRunning Then
> > * * * * Set objOutlook = CreateObject("Outlook.Application") * 'fresh
> > version of Outlook
> > * * End If
> > * * Set objNamespace = objOutlook.GetNamespace("MAPI")
> > * * objNamespace.Logon
> > End Sub
>
> > 'Releases Public variable objects after Outlook session
> > Public Sub ReleaseOutlookObjects()
> > Application.ScreenUpdating = False
> > * * objNamespace.Logoff
> > * * Set objNamespace = Nothing
> > * * If Not OutlookWasRunning Then objOutlook.Quit
> > * * Set objOutlook = Nothing
> > End Sub
>
> > Public Sub MakeContactsFolders()
> > Dim EmailFolderName As String
> > Dim objFolder As MAPIFolder, objNewFolder As MAPIFolder, objStudent As
> > Outlook.ContactItem
> > Dim Delegates As Long, i As Long
> > Dim MailInfo As Variant
> > Application.ScreenUpdating = False
> > * * EmailFolderName = InputBox("What is the email folder name?", "Make
> > an Outlook Contacts Folder")
> > * * If EmailFolderName = "" Then Exit Sub
> > * * DeclareOutlookObjects
>
> > * * 'DeclareOutlookObjects() already run to Connect up to Outlook and
> > creates objNameSpace
> > * * 'create a new contacts folder in the Contacts Folders, if that
> > exists
> > * * 'need to delete it first if it already exists
>
> > * * Set objFolder = objNamespace.GetDefaultFolder(olFolderContacts)
> > * * On Error Resume Next * *'see if "My Contacts" exists
> > * * * * Set objNewFolder = objFolder.Folders(EmailFolderName)
> > * * On Error GoTo 0
> > * * 'If folder already exists, it is last years so delete it
> > * * If Not objNewFolder Is Nothing Then objNewFolder.Delete
> > * * Set objNewFolder = objFolder.Folders.Add(EmailFolderName,
> > olFolderContacts)
> > * * 'get the contact info
> > * * MailInfo = ActiveSheet.Range("A1").CurrentRegion.Value
> > * * Delegates = UBound(MailInfo, 1) - 1 'not header
> > * * 'Add the contact info to the created folder
>
> > * * * * For i = 1 To Delegates * * 'all attendees
> > * * * * Set objStudent = objNewFolder.Items.Add(olContactItem)
> > * * * * * * With objStudent
> > * * * * * * * * .LastName = MailInfo(i + 1, 1)
> > * * * * * * * * .FirstName = MailInfo(i + 1, 2)
> > * * * * * * * * .Email1Address = MailInfo(i + 1, 3)
> > * * * * * * * * .BusinessAddress = MailInfo(i + 1, 4)
> > * * * * * * * * .Save
> > * * * * * * End With
> > * * * * Next i
> > * * * * objNewFolder.ShowAsOutlookAB = True 'see it in address book
> > * * * * If objNewFolder.Items.Count = 0 Then objNewFolder.Delete
> > 'not required if empty
>
> > * * MsgBox "Your email folder is a subfolder of your Contacts Folder",
> > vbOKOnly
> > * * Set objStudent = Nothing
> > * * Set objNewFolder = Nothing *'GroupContacts
> > * * Set objFolder = Nothing 'contacts folder
> > * * ReleaseOutlookObjects * *'disconnect from Outlook
> > End Sub
> > On Jun 26, 8:02 am, RyanH <Ry...@discussions.microsoft.com> wrote:
> > > Is it possible to get information from the Users Outlook contacts in my Excel
> > > Userform. *For example, when the Userform is initialized in Excel it will
> > > have list of all the Company Names in the Users Outlook Contacts. *When the
> > > user selects a company, things like the address, phone number will
> > > automatically be filled in the Userform. *Is this possible?
>
> > > Anything to get me going would help?
> > > --
> > > Cheers,
> > > Ryan- Hide quoted text -
>
> - Show quoted text -
|
Similar Threads
