Import From Email

[ilanray]

Hi
I have a table called ImportEmailsTBL with id, SubjectMail, MSgBody.
at outlook: I save my emails to a folder called - TempEmail

I would like to scan the folder and import every email to my table (ImportEmailsTBL), the subject of the email will go SubjectMail Field and the body of the maill will go to MsgBody field
then move the email to another folder called "donEmails"

is there away to do it?

 

[ebs17]

Of course.
Break your task down into subtasks.

The emails are saved as an Msg file? Open a file, analyze it and write desired information into your spreadsheet.

The second part is a loop over all files in the folder. Copied from elsewhere without name adjustment:

Sub DoIt()
    Dim sPath As String
    Dim sFile As String

    sPath = "C:\Testing\"             
    sFile = Dir(sPath & "TEST*.csv")                     
    Do While sFile > vbNullString         
        'Debug.Print sFile, sPath & sFile                         

        ' somehow use the file
    
        ' if necessary, delete the file (VBA.Kill) or move the file to an archive (VBA.Name)

        sFile = Dir                                 
    Loop
End Sub

 

[ilanray]

What do you mean by analyze it? how can I know what is the subject and what is the body?

 

[mike60smart]

Hi
I have a table called ImportEmailsTBL with id, SubjectMail, MSgBody.
at outlook: I save my emails to a folder called - TempEmail

I would like to scan the folder and import every email to my table (ImportEmailsTBL), the subject of the email will go SubjectMail Field and the body of the maill will go to MsgBody field
then move the email to another folder called "donEmails"

is there away to do it?

Hi
In Access you can setup a Link to your TempEMail Folder.

 

[ilanray]

How can I do it?

 

[CJ_London]

on the ribbon - external data > outlook> follow the prompts

 

[mike60smart]

How can I do it?

Did you manage to setup the Link?

 

[ilanray]

this is not what i mean/ I am saving the msg file on a folder then I would like to create a code that import the data to a table and split it to body and subject in a different fields

 

[CJ_London]

The linked table as described, splits the message into multiple fields - including body and subject. So all you need to do is use an append query (perhaps with appropriate criteria) to populate your other table with the required fields. Note that outlook does not have ID fields, so your destination table will need an autonumber PK.

To save emails to disk and move emails to another folder (is that your saved file on disk or email to another email folder in outlook?), just google your question. Suggest try search terms 'access vba save outlook message to file' and 'access vba move outlook message to another email folder'

 

[mike60smart]

this is not what i mean/ I am saving the msg file on a folder then I would like to create a code that import the data to a table and split it to body and subject in a different fields

As CJ has said, the linked folder in Access will show all data in the TempEMails Folder.

You can then create queries based on the Linked Folder as required.

 

[ilanray]

maybe I didn't understood you . When I click on external data it pop up a window of getting data from another source, in this case from outlook. The next step is to import to access table and then is display the outlook folder. this is now what I wanted unless i am missing something

 

[mike60smart]

maybe I didn't understood you . When I click on external data it pop up a window of getting data from another source, in this case from outlook. The next step is to import to access table and then is display the outlook folder. this is now what I wanted unless i am missing something

No you should have gone for the option to Link as shown in the attached screenshot.

Or if you need to see this data on a regular basis then you can do as you said and just Import the Data on a regular basis.

 

[Pat Hartman]

You cannot manipulate the data in the email until you read it. Linking to the message gives you access to the text of your message. You can write a VBA procedure to open the linked table to the record you are interested in and then write whatever code you need to parse it and load it into your permanent tables.

 

[ilanray]

I tried to do it. It ask me to link to outlook. I don't want to link it . I would like to look for a msg file in the folder and extract the data to a table

 

[mike60smart]

I tried to do it. It ask me to link to outlook. I don't want to link it . I would like to look for a msg file in the folder and extract the data to a table

If you link to the Outlook folder the table in Access will look like that shown in the screenshot attached.

You can then use a query to filter to specific data and then export or create a Report based on the results.

 

[Gasman]

this is not what i mean/ I am saving the msg file on a folder then I would like to create a code that import the data to a table and split it to body and subject in a different fields

Good luck with that.
Here is what it looks like in Notepad++

 

[ebs17]

Good luck with that.

Search for msg2html or similar.
There seem to be ways to convert the files into something that can be accessed and evaluated.

I don't know much about it and I don't have Outlook running either.

I would like to create a code

I don't want to disturb you either.

 

[Gasman]

Can't see why you cannot just use automation to read the contents direct from the email?

 

[CJ_London]

You can save a readable file called a .mht

 

[Pat Hartman]

is there away to do it?

You have been told how to do it with little code but you keep telling us that you don't want to do that. WHAT is your aversion to doing this the no/low code way?