Over my head and Majorly Lost (1 Viewer)

[js061256]

Ultimately, I need to import a range of Excel spreadsheets that are attached to Outlook emails in a shared public folder--all from Access--because I am not (and cannot be) the owner of the account who could run internal Outlook VBA.

One thing at a time: first, I need to get hold of these attachments and don't know how. I have some code I stole, but I don't know what it means or what it does.

   ' Set up DAO objects (uses existing "ProcessSheets" table)
   Dim rst As DAO.Recordset
   Set rst = CurrentDb.OpenRecordset("ProcessSheets")


   ' Set up Outlook objects.
   Dim ol As New Outlook.Application
   Dim olns As Outlook.NameSpace
   Dim cf As Outlook.MAPIFolder
   Dim cMail As Outlook.MailItem
   Dim cAtch As Outlook.Attachments
   Dim objItems As Outlook.Items
   Dim of As Outlook.Folder
   Dim ofSub As Outlook.Folder

   Set olns = ol.GetNamespace("MAPI")
   '--- (5) ---
   'Would eventually be nice to allow a user to select a folder. Folderpicker? Lowest     priority.

   Set of = olns.GetDefaultFolder(XE_IPP)
   '--- (1) ---
   'Loop only searches one level down. I will need all subfolders. Most examples I saw     call external Sub? Recursive?
   For Each ofSub In of.Folders
   Set objItems = ofSub.Items
   iNumMessages = objItems.Count
   If iNumMessages <> 0 Then
      For i = 1 To iNumMessages
         If TypeName(objItems(i)) = "MailItem" Then
            Set cMail = objItems(i)
            rst.AddNew
            rst!EntryID = cMail.EntryID
            rst!ConversationID = cMail.ConversationID
            rst!Sender = cMail.Sender
            rst!SenderName = cMail.SenderName
            rst!SentOn = cMail.SentOn
            rst!To = cMail.To
            rst!CC = cMail.CC
            rst!BCC = cMail.BCC
            rst!Subject = cMail.Subject
            '--- (3) ---
            'Code only inserts first attachment. Code Also inserts embedded     attachments.
            'Need code to insert all user selected attachments (ex. PDF Document) and     no embedded attachments.
            Set cAtch = cMail.Attachments
            cntAtch = cAtch.Count
                If cntAtch > 0 Then
                    For j = cntAtch To 1 Step -1
                    strAtch = cAtch.Item(j).FileName
                    rst!Attachments = strAtch
                    Next
                Else
                    rst!Attachments = "No Attachments"
                End If
            rst!Count = cMail.Attachments.Count
            rst!Body = cMail.Body
            rst!HTMLBody = cMail.HTMLBody
            rst!Importance = cMail.Importance
            rst!Size = cMail.Size
            rst!CreationTime = cMail.CreationTime
            rst!ReceivedTime = cMail.ReceivedTime
            rst!ExpiryTime = cMail.ExpiryTime
            '--- (2) ---
            ' Solved - Figured out how to call folder location into databse.
            rst!EmailLocation = ofSub.Name
            rst.Update
         End If
      Next i
   End If
   Next
   '--- (4) ---
   'Still need code to append Access database with only new records.
   'Duplicate email can exist in differenc subfolders but not same subfolder.
End Sub

I don't expect anyone to hold my hand, just get me pointed in the right direction. I just don't even know where to start. Thanks.

 

[Twincam]

I'd start thinking about this in a different way (if I've understood the requirement).

If you google "automatically saving attachments in outlook" you will find examples. That can be set up to store attachments from specific senders in specific folders (not Access folders, shared drive folders).

Once that's in place, you then need to check those folders and deal with additions and you can automate that from MS Access.

That may not be exactly what you need, but the principle is to work with Outlook to get the data you need stored in a way that allows MS Access to get to it, rather than diving into Outlook from Access which sounds like a steep learning curve with some potentially nasty security issues to me!

 

[js061256]

Thanks for that. I realize my statement is broadly rhetorical and not the kind of inquiry that would engender a specific response. The real mystery to me is how to reference the public folder inbox (or any Outlook inbox for that matter) and what the code I posted would do.

Does it say that it's putting the email in an Access table, or importing the attachment to the table. I just don't Understand the code. I don't see the distinction between the "GetDefaultFolder" method, the other permutations of it, and which one might be the right one. There are indeed a lot of code samples to choose from, but I'm not smart enough to even identify good candidates.

My (limited) experience is in Access VBA, so the whole Outlook thing has me alternating between pounding my head against the wall and chasing my tail. Really, your willingness to be a lifeline did wonders for my morale. Thanks again!

 

[Twincam]

To grasp the concepts, start with something more familiar. look at creating a new Excel spreadsheet from within Access VBA. There's less risk involved - tread carefully when you are accessing a mail system - see if you can get access to a test/dev one. You really dont want to bring your company's mail system down!

Public xlApp As Excel.Application
Public xlWbk As Excel.Workbook
Set xlApp = New Excel.Application
Set xlWbk = xlApp.Workbooks.Open( excel template )
xlWbk.SaveCopyAs filename
xlWbk.Close False
xlApp.Quit
Set xlWbk = Nothing
Set xlApp = Nothing

That basic structure should allow you to create and save a spreadsheet from within Access. Essentially you are firing up an Excel App (as though you did it from Windows). You create the objects (they start out as = Nothing) Then you populate them (via Set new, or from another object - above xlapp/open is used to populate xlwbk. So you chain from object to object until you get to where you want to go. Then (to be safe/professional) you go backwards cleaning up after you - some can be just set to nothing again, some ought to be closed/ended in some way, esp the app itself.

the "for each" loops step though lists of a type of object, use xlwbk, find the list of worksheets, set up a worksheet object, step through them finding the name of each worksheet.

You'll soon get the hang of it, but experiment with access/excel, then use that to help understand the code you have supplied above.

What the code is doing is chaining through Outlook to find folders, then each folder, then mailitems, then each mailitem, then copying data from the mailitem into a recordset here...

rst!EntryID = cMail.EntryID

 

[js061256]

Wow, such generosity! I hope you had all that in the can somewhere and just pulled it out for me instead of writing it from scratch.

Got it. It wasn't wasted. You're the best thing since sliced bread.

 

[Twincam]

I pulled the code lines out of an app, and I had a spare few minutes A good tip is while you're stepping through the code, hover the cursor over the name of each object - it will tell you if it's empty (Nothing). Work from the outside in. Get your code running just at the application open/close level. Then go to the workbook open/close level. then worksheets... use for.....each don't mess about with counters, don't dim as object, dim as the correct object type.

Try not to Whoop too loud when it works.

 

[Twincam]

If you have outlook up and running (and you may HAVE to for this to work as I think it may assume that as the top level object is not "new") you will end up in your own folders. If you have access to some public folders, you should be able to navigate to them. I may end up doing something very similar to this very soon. With Lotus Notes.