Programing Outlook using Excel

  • Thread starter Thread starter V. Roe
  • Start date Start date
V

V. Roe

I am using Outlook 2000 with Excel 97

I have been experimenting with code found at
http://www.dicks-clicks.com/excel/olAutomating.htm
I use the code to loop through emails in the Inbox, save the attachment, run
code to paste information into two workbooks, and then move the email to
another folder.

My modified code is working, but now I need to make sure that the emails in
the inbox are grouped by subject before saving the attachments (the emails
must be processed in date order and the date is part of the subject line).
Several people use this machine and can change the grouping.

I would appreciate any help if it is possible to change the grouping in
Outlook from code in excel. I have also listed the code I am using below.
Thanks
Valerie

Sub OpenAttachment()
'My testing to open the attachment and run macro then
'move to another folder
Application.EnableEvents = False
Dim olApp As Outlook.Application
Dim olNs As NameSpace
Dim Fldr As MAPIFolder
Dim MoveToFldr As MAPIFolder
Dim olMi As MailItem
Dim olAtt As Attachment
Dim MyPath As String
Dim i As Long

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.GetDefaultFolder(olFolderInbox)
Set MoveToFldr = Fldr.Folders("Daily Sales")
MyPath = "C:\My Documents\Test\Daily Sales.xls"
For i = Fldr.Items.Count To 1 Step -1
Set olMi = Fldr.Items(i)
If olMi.Subject Like ("Daily Sales *") Then
For Each olAtt In olMi.Attachments
If olAtt.FileName = "Store Register Email v20.xls" Then
olAtt.SaveAsFile MyPath
Workbooks.Open FileName:=MyPath

'calls macro that copies information to correct workbooks
Call ToDailyRegister

End If
Next olAtt
olMi.Save
olMi.Move MoveToFldr
End If
Next i
Kill MyPath
Set olAtt = Nothing
Set olMi = Nothing
Set Fldr = Nothing
Set MoveToFldr = Nothing
Set olNs = Nothing
Set olApp = Nothing
Application.EnableEvents = True
End Sub
 
news://msnews.microsoft.com/microsoft.public.outlook.program_vba

maybe be a better palce to post this. Once you have a reference to the
Outlook application, you are working with the outlook object model.
 
Will do.
Thanks

Tom Ogilvy said:
news://msnews.microsoft.com/microsoft.public.outlook.program_vba

maybe be a better palce to post this. Once you have a reference to the
Outlook application, you are working with the outlook object model.
Click to expand...
 
Valerie

My modified code is working, but now I need to make sure that the emails in
the inbox are grouped by subject before saving the attachments (the emails
must be processed in date order and the date is part of the subject line).
Several people use this machine and can change the grouping.
Click to expand...

Instead of looping through the MAPIFolder and accessing its Item property,
create a new Items collection object variable. That way you can use the
Sort method on it.
Sub OpenAttachment()
'My testing to open the attachment and run macro then
'move to another folder
Application.EnableEvents = False
Dim olApp As Outlook.Application
Dim olNs As NameSpace
Dim Fldr As MAPIFolder
Dim MoveToFldr As MAPIFolder
Dim olMi As MailItem
Dim olAtt As Attachment
Dim MyPath As String
Dim i As Long
Click to expand...

Dim olItms as Outlook.Items
Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.GetDefaultFolder(olFolderInbox)
Click to expand...

Set olItms = Fldr.Items
olItms.Sort "Subject"
Set MoveToFldr = Fldr.Folders("Daily Sales")
MyPath = "C:\My Documents\Test\Daily Sales.xls"
For i = Fldr.Items.Count To 1 Step -1
Set olMi = Fldr.Items(i)
Click to expand...

'Replace the above with
For i = olItms.Count to 1 Step -1
Set olMi = olItms.Item(i)
If olMi.Subject Like ("Daily Sales *") Then
For Each olAtt In olMi.Attachments
If olAtt.FileName = "Store Register Email v20.xls" Then
olAtt.SaveAsFile MyPath
Workbooks.Open FileName:=MyPath

'calls macro that copies information to correct workbooks
Call ToDailyRegister

End If
Next olAtt
olMi.Save
olMi.Move MoveToFldr
End If
Next i
Kill MyPath
Set olAtt = Nothing
Set olMi = Nothing
Set Fldr = Nothing
Set MoveToFldr = Nothing
Set olNs = Nothing
Set olApp = Nothing
Application.EnableEvents = True
End Sub
Click to expand...

Creating an Items collection object variable will allow you to manipulate
the order of the items, but it will not change the order of the items in the
Explorer view - which is preferable I think. Post back if you need more
details.
 
Dick
Thanks
I created the items collection. It is confusing to see them in the wrong
order in Outlook, but I can tell it is looping through the items in the
correct order. With my lack of knowledge in this area I'm just glad it
works : )
Valerie
 
Glad to hear it's working, Valerie

If you want to sort the user interface, you can do that with a View. You'll
need to set up the View manually (I don't know how to do it in code). Go to
Views - Define Views and copy whichever view you normally use, then Modify
that new one - call it SubjectSort - to sort the way you want.

Then you can use code like this to change the actual sort

Sub SortOutlook()

Dim olApp As Outlook.Application
Dim olNs As Outlook.NameSpace
Dim olExp As Outlook.Explorer

Set olApp = GetObject(, "Outlook.Application")
Set olNs = olApp.GetNamespace("MAPI")
Set olExp = olApp.ActiveExplorer

Set olExp.CurrentFolder = olNs.GetDefaultFolder(olFolderInbox)

olExp.CurrentView = "SubjectSort"

End Sub

Note that changing the sort will not get rid of the need to use the
Items.Sort method that you have now. You still need that for what you want
to do, but you can add this little bit to make it less confusing if you
like.
 
Back
Top