B
Bernie
I am developing an application that needs to import excel sheets sent by
email. How do I go about it?
Any comments most welcome.
email. How do I go about it?
Any comments most welcome.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
T
Tom van Stiphout
On Wed, 12 Dec 2007 11:05:02 -0800, Bernie
One option is to write an Outlook add-in that is inspecting each
incoming email, and then handling the attachments. Not a trivial
matter.
-Tom.
One option is to write an Outlook add-in that is inspecting each
incoming email, and then handling the attachments. Not a trivial
matter.
-Tom.
F
frans
I have just started doing something similar myself. I am learning as I go
along, so there might be a more elegant way out there.
If you want to do this in Outlook, use the NewMailEx event.
If you want to do this in Access, the code below wil run through all items
in your Inbox, looking for Excel attachments. If found, it will be saved to
a temporary folder on your hard drive, from where you can import it into
Access and then delete the temporary file.
Dim objOLApp As Outlook.Application
Dim objMsg As Outlook.MailItem
Dim objAttachment As Outlook.Attachment
Dim strFileName As String
Set objOLApp = New Outlook.Application
For Each objMsg In
objOLApp.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Items
For Each objAttachment In objMsg.Attachments
With objAttachment
strFileName = .Filename
If Left$(Mid$(strFileName, InStrRev(strFileName, ".") + 1), 3) =
"xls" Then
strFileName = "c:\xx\" & strFileName
.SaveAsFile strFileName
'
' Your code here to import Excel file into Access
'
Kill strFileName
End If
End With
Next
Next
Set objOLApp = Nothing
Set objMsg = Nothing
Set objAttachment = Nothing
You will probably want to built in some robustness, e.g:
- making sure that you only process the Excel files that you really want
- making sure that you do not import files twice
The above works quite well, but currently I push the button manually in
Access to do it and then I delete the necessary items manually from my Inbox.
Now I want to automate it.
My thinking at this stage is that a combination of Outlook's NewMailEx event
and a permanently open hidden Access form will probably be the best:
Put something like the above code (without the import bit and without the
Kill statement) in Outlook's NewMailEx event, and then set the timer from
Outlook on the hidden Access form. Access will then do the importing and
processing and delete the file.
That way, Outlook can save the necessary sheets as they come in, and Access
can immediately do the actual importing and other processing itself (each to
its own).
One will however have to make sure that the filenames that Outlook save are
unique, because the above code will overwrite files that Access did not have
time to process yet. Probably best to just add a time stamp to the filename
when saving it to the temp folder.
Anyway, that is my plan at this stage. If anybody has a better idea, let me
know.
along, so there might be a more elegant way out there.
If you want to do this in Outlook, use the NewMailEx event.
If you want to do this in Access, the code below wil run through all items
in your Inbox, looking for Excel attachments. If found, it will be saved to
a temporary folder on your hard drive, from where you can import it into
Access and then delete the temporary file.
Dim objOLApp As Outlook.Application
Dim objMsg As Outlook.MailItem
Dim objAttachment As Outlook.Attachment
Dim strFileName As String
Set objOLApp = New Outlook.Application
For Each objMsg In
objOLApp.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Items
For Each objAttachment In objMsg.Attachments
With objAttachment
strFileName = .Filename
If Left$(Mid$(strFileName, InStrRev(strFileName, ".") + 1), 3) =
"xls" Then
strFileName = "c:\xx\" & strFileName
.SaveAsFile strFileName
'
' Your code here to import Excel file into Access
'
Kill strFileName
End If
End With
Next
Next
Set objOLApp = Nothing
Set objMsg = Nothing
Set objAttachment = Nothing
You will probably want to built in some robustness, e.g:
- making sure that you only process the Excel files that you really want
- making sure that you do not import files twice
The above works quite well, but currently I push the button manually in
Access to do it and then I delete the necessary items manually from my Inbox.
Now I want to automate it.
My thinking at this stage is that a combination of Outlook's NewMailEx event
and a permanently open hidden Access form will probably be the best:
Put something like the above code (without the import bit and without the
Kill statement) in Outlook's NewMailEx event, and then set the timer from
Outlook on the hidden Access form. Access will then do the importing and
processing and delete the file.
That way, Outlook can save the necessary sheets as they come in, and Access
can immediately do the actual importing and other processing itself (each to
its own).
One will however have to make sure that the filenames that Outlook save are
unique, because the above code will overwrite files that Access did not have
time to process yet. Probably best to just add a time stamp to the filename
when saving it to the temp folder.
Anyway, that is my plan at this stage. If anybody has a better idea, let me
know.