Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Importing / Linking / June 2005

Tip: Looking for answers? Try searching our database.

Accessing Outlook Pulic Folders

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Klatuu - 19 May 2005 18:26 GMT
I have a need to get an Execl workbook from an Outlook Public Folder.  My
preference would be to import data directly from the workbook in the Public
Folder, but I will settle for copying it to another location if necessary.  I
assume this would be using Outlook objects much like I do with Excel objects.
This is, however, a new adventure for me and I would appreciate any guidance
you could provide.
John Nurick - 20 May 2005 14:40 GMT
No direct help, I'm afraid. But take a look at www.outlookcode.com and
www.slipstick.com

>I have a need to get an Execl workbook from an Outlook Public Folder.  My
> preference would be to import data directly from the workbook in the
[quoted text clipped - 6 lines]
> guidance
> you could provide.
Klatuu - 20 May 2005 14:50 GMT
Thanks, John.  I'll check these sites.  I could probably guess my way through
it, but I am finding the Outlook Object Model is a little different from the
rest.

> No direct help, I'm afraid. But take a look at www.outlookcode.com and
> www.slipstick.com
[quoted text clipped - 9 lines]
> > guidance
> > you could provide.
Joe Fallon - 23 May 2005 01:45 GMT
Here is some sample Outlook code that may help:

In a code module, use Tools References to set one for Outlook.
Then modify this code to do what you need.

Public Sub SaveAttachment(strPath As String)
On Error GoTo Err_SaveAttachment

 Dim ol As New Outlook.Application
 Dim ns As Outlook.NameSpace
 Dim MyInbox As Outlook.Items
 Dim fldr As Outlook.MAPIFolder
 Dim itm As Outlook.MailItem
 Dim mFile As String, NumAttachments As Integer, i As Integer, NumEmails As
Integer, strTo As String

 Set ns = ol.GetNamespace("MAPI")
 Set MyInbox = ns.GetDefaultFolder(olFolderInbox).Items

 'set a reference to a folder to move the items to
 Set fldr = ns.Folders("Personal Folders").Folders("Saved
Messages").Folders("Bids")

 For Each itm In MyInbox
   'Debug.Print itm.Subject, itm.To, itm.SenderName
   If itm.Subject Like "*Bid*" Then
     NumAttachments = itm.Attachments.Count
     i = 1  'attachment number
     Do While i <= NumAttachments
       mFile = itm.Attachments.Item(i).filename
       itm.Attachments.Item(i).SaveAsFile strPath & mFile
       i = i + 1
     Loop
   Else
     'Debug.Print "Not a Bid"
   End If
 Next

 'In order to move all messages from one folder to another, you must loop
backwards through the index
 NumEmails = MyInbox.Count
 For i = NumEmails To 1 Step -1
   If MyInbox.Item(i).Subject Like "*Bid*" Then
     Set itm = MyInbox.Item(i)
     'Get e-mail address for the acknowledgment from the Body of the
original message
     strTo = GetAddress(itm.Body)
     Call SendEmailMessage("This is to acknowledge that your Bid has been
received and will be processed shortly.", "This is the body of your message
to us:" & vbCRLF & itm.Body, strTo)
     itm.Move fldr
   End If
 Next i

Exit_SaveAttachment:
 Set itm = Nothing
 Set MyInbox = Nothing
 Set ns = Nothing
 Set ol = Nothing
 Exit Sub

Err_SaveAttachment:
 MsgBox ("Error # " & str(Err.Number) & " was generated by " & Err.Source &
Chr(13) & Err.Description)
 Resume Exit_SaveAttachment

End Sub

Sub SendEmailMessage(strSubject As String, strBody As String, strTo As
String)
On Error GoTo Err_SendEmailMessage
 Dim ol As New Outlook.Application
 Dim ns As Outlook.NameSpace
 Dim newMail As Outlook.MailItem

 Set ns = ol.GetNamespace("MAPI")
 Set newMail = ol.CreateItem(olMailItem)
 With newMail
   .Subject = strSubject
   .Body = strBody & vbCRLF
   With .Recipients.Add(strTo)
     .Type = olTo
   End With
   .Send
 End With

Exit_SendEmailMessage:
 Set ol = Nothing
 Set ns = Nothing
 Set newMail = Nothing
 Exit Sub

Err_SendEmailMessage:
 MsgBox ("Error # " & str(Err.Number) & " was generated by " & Err.Source &
Chr(13) & Err.Description)
 Resume Exit_SendEmailMessage

End Sub

You may need to use a program named ClickYes! in order to use the
SendEmailMessage code.
Oultook security now pops up dialog boxes that you can't program around.
ClickYes! looks for them and "clicks the Yes button" for your code.

http://www.express-soft.com/mailmate/clickyes.html

Signature

Joe Fallon
Access MVP

>I have a need to get an Execl workbook from an Outlook Public Folder.  My
> preference would be to import data directly from the workbook in the
[quoted text clipped - 6 lines]
> guidance
> you could provide.
Klatuu - 23 May 2005 18:10 GMT
Thanks, Joe. I will work with this, but I think it will do what I need.  This
task has been delayed a couple of weeks, so I wont have a chance to try it
for a while.

> Here is some sample Outlook code that may help:
>
[quoted text clipped - 112 lines]
> > guidance
> > you could provide.
brupp24_6 - 29 Jun 2005 12:48 GMT
Hi Joe (or anyone)...  

I was attempting something similiar and attempted this code...  
I keep getting a break on the line:
 Set MyInbox = ns.GetDefaultFolder(olFolderInbox).Items
with the error: Method 'Items' of object 'MAPIfolder' failed

I'm racking my brain here.  Any help would be appreciated.
I've already checked references, etc...

> Here is some sample Outlook code that may help:
>
[quoted text clipped - 112 lines]
> > guidance
> > you could provide.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.