I am openning Outlook by calling createobject, which I expected to open a new instance of the application. However it appears to use the one open on my desktop. This means that my quit statement closes my e-mail. How should I amend the below to leave my original e-mail session open
Also I will be replacing the debug.print statement with code to work with the attachment to copy data into my database. I am intending to save the file to a temporary directory, so that I can open it with Excel. Is there a smarter way to open the file and work with it
Finally, the below only writes the filename to the debug window; there are approx. 50 mail items in the directory; this code is very slow to run. Can I speed up the below in anyway
Private Sub Import_Click()
Dim olApp As Outlook.Application
Dim OlItems As Outlook.Items
Dim olItem As Outlook.MailItem
Dim olFolder As Outlook.MAPIFolder
Dim olNameSpace As Outlook.NameSpace
Dim X, y As Long
Dim strFileName As String
Set olApp = CreateObject("Outlook.application")
Set olNameSpace = olApp.GetNamespace("MAPI")
Set olFolder = olNameSpace.GetDefaultFolder(olFolderInbox).Folders("PPS")
Set OlItems = olFolder.Items
For Each olItem In OlItems
X = olItem.Attachments.Count
If X > 0 Then
For y = 1 To X
strFileName = olItem.Attachments.Item(y).FileName
If Right$(strFileName, 3) = "xls" Then
Debug.Print strFileName
End If
Next y
End If
Next
Set olFolder = Nothing
Set olNameSpace = Nothing
Set olFolder = Nothing
Set OlItems = Nothing
olApp.Quit
Set olApp = Nothing
End Sub
Thanks in advance
ADG
Help with using Outlook from Access
Andrew Whiddett
Had a look at the Outlook help and I don't think you'll be able to open the attachment directly from the e-mail. There doesn't appear to be a property of the Attachment object (olItem.Attachments.Item) to get at the data. Think that might be because any file can be an attachment. Looks like you'll need to save it.
Reads like a bit of a nightmare to be honest Outlook to Excel to Database.
Are you based in the UK
womalley
ADG,
Found this link that you might find interesting...
http://www.awprofessional.com/articles/article.asp p=366892&seqNum=1
etaardvark
Many thanks for the link, it was quite interesting. I think I need to look into what is available in the API for future reference.
To answer your previous question I am based in sunny Shropshire in the Midlands.
Nightmare problem now cracked, speed problem has also gone away, I think it was a random machine thing. So I only have the simple Access bit to do now.
Regards
ADG
bmellow
How you doing ADG,
I think there can only be one instance of Outlook running on a computer. If you start Excel a couple of times you get different instances, but starting Outlook a couple of times seems to only use the same instance. That will be programmed into Outlook.
At the start of your code you could use the Windows API to determine if Outlook is already open and if it isn't then the code knows it must close the instance it created and if it Outlook was running then your code knows to leave it alone. Have a look online for code to determine if an application instance is running.
There could be a better way of sending the file to the database but it will depend on how similar the data in the sheet is to the structure of the table. There is a nice way to import spreadsheets using the same means to write data to a database but it will depend. Post a little bit more information on the data structure.
Not very familiar with Outlook so I don't know if there is a way to speed up your processing. On the Outlook UI you can do a search for emails in a folder that contain attachments, you could apply this search and only loop over the ones that have attachments.
Hope that helps..
RaguV
Hi Derek
Thanks for the reply. I did a search and found some code to detect Excel which I modified to find Outlook, it appears to work ( Windows API is a mystery to me).
Public Function DetectOutlook() As Boolean
Dim MyoL As Object
On Error Resume Next
DetectOutlook = True
Set MyoL = GetObject(, "Outlook.Application")
If Err.Number <> 0 Then DetectOutlook = False
Err.Clear
End Function
The format of my data in Excel is ugly! I need to open the Excel workbooks and use VBA to detect the data. My question really is how best to open the worksheet. To back track, I have a rule set up in Outlook which puts any incoming price list e-mails into a directory called PPS. I manually transfer out old supplier e-mails to an archive folder. So my folder contains 50 - 60 e-mails each containing an excel spreadsheet. The only way I can think of to work with the Excel files is to save the atttachment to my hard drive then use Excel.application to open them from there. Can Excel.application open an excel file directly from an e-mail is probably what I need to know.
Regards