• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • Last Modified:

Import Spreadsheet from Outlook Attachment

Can anyone help me with some code that will allow me to save an attachment from Outlook and import it into access?
I'm getting about 30-40 e-mails a day that have a .csv attachment.  I want to be able to save this attachment as an excel file and place it in a specified folder.  Then move the e-mail into another folder in outlook.  The e-mails are filtered into a specific folder when I receive them in outlook.  As for importing into access, I've already created something that import the information I need from the excel files in the specified directory that I place it in.  If you need anymore info, let me know.  

LENNIE
0
Lennie2000
Asked:
Lennie2000
1 Solution
 
KMANCommented:
If you open a CSV with Excel, you are presented with a Wizard to determine the mappings.  Is this not what you want.

Also - why no go directly from CSV into Access?  If you setup (one time) a schema.ini for the mapping of the file(s) you can then just run the TransferText methods.

K
0
 
Lennie2000Author Commented:
What I need is an automated function so that when the user pushes the button on a form, it will save the attachement on every e-mail from outlook and then run the import function I already have.  I don't want wizards to pop up or anything.  

Yes I know I can use transfertext for CSV, but I can't remember the reason why I used excel but there was some reason.  Plus I already have something that's been working fine for importing for the last couple of months, so I rather not fiddle with it.  

LENNIE
0
 
__Holly__Commented:
http://www.freevbcode.com/ShowCode.Asp?ID=1154

modify the code to work from Access Automation instead of as an outlook macro; and you're golden.

i ended up leaving the code in outlook cuz it was easier (and then i had outlook autoprocess everyday..

it just seemed easier than doing it via automation with Outlook/Access
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Lennie2000Author Commented:
Holly,

If you don't mind stepping me through this code, I'd really appreciate it.  How do I go about starting to modify the code to work from access automation?  

My attachment is a CSV file and I still need to save it as a XLS file.  Should I add a CreateObject("Excel.Application")and then open it and resave it as XLS?  Is that the simplest way?  Also, I need the saved name to be the date plus a number such as, 07252002-001, 07252002-002...etc. for every file for that date, then for the next day it would start back at 001 (07262002-001).  

LENNIE
0
 
nico5038Commented:
Hi,

Saw I've created in the past a sample that's saving attachments using the outlook code from Access Developer.
Drop me a line in the mail.

Nic;o)
0
 
Lennie2000Author Commented:
Nico,

I keep getting errors when I try to use it.

Error:-2147467259
Cannot save the attachment.  This file name is in use by Outlook for a temporary file.  Rename the item and then try again.

There isn't anything in my c:\temp directory right now.  And I've only put one e-mail with an attachment in the folder for a test.

Lennie
0
 
nico5038Commented:
Just try to change the code for assigning a slightly different name like:
objCurrentItem.Attachments.Item(intI).SaveAsFile ("c:\temp\x_" & objCurrentItem.Attachments.Item(intI))

If that doesn't work, I'll have to dive further into that...

Nic;o)
0
 
Lennie2000Author Commented:
Ok, that worked when I changed directories.  How can I have the file name saved as the received time?  I can't get around the slashes and colons in the time.

Lennie
0
 
nico5038Commented:
Just use e.g.:
("c:\temp\x_" & objCurrentItem.Attachments.Item(intI)& hour(timefield) & minute(timefield) & second(timefield))

Nic;o)
0
 
Lennie2000Author Commented:
In your combo box for "From Folder", can it be modified to select subfolders?  Such as, Instead of Inbox as a selection, have it select the subfolders of the inbox.

Lennie
0
 
Lennie2000Author Commented:
Also, my attachment is in .csv format.  I want to resave into .xls format.  Here's what I have so far, but I don't know how to use the SaveAs function.

strXL=file_name

Set XL = CreateObject("Excel.Application")
    With XL
    .Workbooks.Open strXL
    .Activeworkbook.SaveAs ???
    .Activeworkbook.Close
    .Quit
    end With
Set XL = Nothing

Lennie
0
 
nico5038Commented:
Really wanting to go to the bottom of it ;-)

The function "fncBuildMapFolder" holds the code for the maps and folders:

    Set colMaps = gnspNameSpace.Folders
    For Each fldMap In colMaps
        Set colFolders = fldMap.Folders
        For Each fldFolder In colFolders
            If fldFolder.DefaultItemType = 0 Then
            rst.AddNew
            rst!map = fldMap.Name
            rst!folder = fldFolder.Name
            rst.Update
            End If
        Next fldFolder
    Next fldMap

A similar function is needed to create the subfolders when "inbox" (or another folder) has been choosen.
So you need to add a new level for subfolders of the selected folder as well as an extra combo on the form and a table for folder and subfolder.

Easier probably to place the inbox subfolders on a "higher level" and redirect the outlook rules to those ;-)

Nic;o)
0
 
Lennie2000Author Commented:
Nico, Do you have any suggestion about the SaveAs function for an excel object as I had said in my previous comment? Thanks.

Lennie
0
 
nico5038Commented:
I would say that adding a filename should suffide.
But an alternative might be to import the CSV into a temp table and use the transferspreadsheet command..

Nic;o)
0
 
Lennie2000Author Commented:
I tried adding a filename.xls but when I use SaveAs, it gives me a prompt box asking if I want to change it to .xls format.  Is there a way to get around it?  The only reason why I want to change it to .xls is that I already created another function that use the transferspreadsheet command that will check for formating and correct data in the file.  I'm thinking it may be easier to figure a way out to resave the .csv to .xls rather than change my other function to accommodate .csv.
What do you think?

Lennie
0
 
nico5038Commented:
When typing the .saveas, access asks for the format as the second parameter.

From the helpfile on the FileFormat:

If ActiveWorkbook.FileFormat = xlWK3 Then
    ActiveWorkbook.SaveAs fileFormat:=xlNormal
End If

Nic;o)
0
 
Lennie2000Author Commented:
I can't seem to get this to work.

It won't accept "xlWK3" or "xlNormal", so what I did was find out what the activeworkbook.fileformat was and it turned out to be = 6.  I guess that's what it is for .csv.  So I tried fiddling around with the other numbers to find the .xls, but I got every other format but .xls.  So then I tried opening a .xls file and finding out the activeworkbook.fileformat and the number that came up was -4143.  I tried inputing the two numbers in the appropriate place, but it didn't work.  Any suggestions.  This is what I have:

If .activeworkbook.fileformat = 6 then
   .activeworkbook.SaveAs fileformat:=(-4143)
End if

Lennie
0
 
Lennie2000Author Commented:
Sorry, disregard my last comment.  It actually works!  

What happened was that it saved it another directory, not the same one in which file was opened in.  So that's my next problem, how can I get it to be saved in the same directory.  I tried adding the filename:-file_name along with the fileformat, but it only takes the first command of the two.  So this is what I tried:

.activeworkbook.SaveAs filename:=file_name, fileformat=(-4143)

It only save the file name I wanted, but not the format.

Lennie
0
 
nico5038Commented:
When using the assigned parameters you would need:
.activeworkbook.SaveAs filename:=file_name, fileformat:=(-4143)

basicallÿ:
.activeworkbook.SaveAs file_name, -4143

would have to work as well.

Nic;o)

0
 
Lennie2000Author Commented:
ok, that worked.  Thanks.

Just one last question.  Is it possible for it to mark the e-mails as read when it imports each one into the table?

Also, I couldn't post that other question with 0 points, so if you could post a comment there and can close that question as well. Thanks.

http://www.experts-exchange.com/Databases/MS_%20Access/Q_20336922.html

Lennie
0
 
nico5038Commented:
Personally I use a separate folder "processed" to move the email I did process. The advantage is that you won't have the danger of duplicate processing and they are physically located in another map.

Samplecode from the help file for the item's .move:
Set myNameSpace = Application.GetNameSpace("MAPI")
Set myInbox = myNameSpace.GetDefaultFolder(6)
Set myItems = myInbox.Items
Set myDestFolder = myInbox.Folders("Persoonlijke berichten")
Set myItem = myItems.Find("[SenderName] = 'Mark Nuiten'")
While TypeName(myItem) <> "Nothing"
    myItem.Move myDestFolder
    Set myItem = myItems.FindNext
Wend

Idea ?

Nic;o)
0
 
Lennie2000Author Commented:
The only thing I'm not sure about is what folder does it look for the e-mails.  Is this the line that tells me where it looks for e-mails?  And does the (6) mean the 6th folder in my mailbox?

Set myNameSpace = Application.GetDefaultFolder(6)

Also, where do I place this code?  

Lennie
0
 
nico5038Commented:
The 6 stands for the variable "olFolderInbox" being the input folder as the name of the folder differs between language versions...

The code needs to be added after processing the mailitem.
All needed is effectively the .move and the destination folder.

Nic;o)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now