Solved

Import Spreadsheet from Outlook Attachment

Posted on 2002-07-24
23
429 Views
Last Modified: 2010-05-18
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
Comment
Question by:Lennie2000
23 Comments
 
LVL 5

Expert Comment

by:KMAN
ID: 7174754
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
 

Author Comment

by:Lennie2000
ID: 7174774
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
 
LVL 1

Expert Comment

by:__Holly__
ID: 7175887
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
 

Author Comment

by:Lennie2000
ID: 7177211
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
 
LVL 54

Accepted Solution

by:
nico5038 earned 300 total points
ID: 7222827
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
 

Author Comment

by:Lennie2000
ID: 7222972
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
 
LVL 54

Expert Comment

by:nico5038
ID: 7223007
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
 

Author Comment

by:Lennie2000
ID: 7223361
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
 
LVL 54

Expert Comment

by:nico5038
ID: 7223392
Just use e.g.:
("c:\temp\x_" & objCurrentItem.Attachments.Item(intI)& hour(timefield) & minute(timefield) & second(timefield))

Nic;o)
0
 

Author Comment

by:Lennie2000
ID: 7223428
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
 

Author Comment

by:Lennie2000
ID: 7223488
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 54

Expert Comment

by:nico5038
ID: 7223509
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
 

Author Comment

by:Lennie2000
ID: 7224689
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
 
LVL 54

Expert Comment

by:nico5038
ID: 7224742
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
 

Author Comment

by:Lennie2000
ID: 7224838
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
 
LVL 54

Expert Comment

by:nico5038
ID: 7224870
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
 

Author Comment

by:Lennie2000
ID: 7225046
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
 

Author Comment

by:Lennie2000
ID: 7225066
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
 
LVL 54

Expert Comment

by:nico5038
ID: 7225109
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
 

Author Comment

by:Lennie2000
ID: 7225410
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
 
LVL 54

Expert Comment

by:nico5038
ID: 7225664
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
 

Author Comment

by:Lennie2000
ID: 7228473
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
 
LVL 54

Expert Comment

by:nico5038
ID: 7228709
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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now