Import From Email (1 Viewer)

ilanray

Member
Local time
Today, 09:34
Joined
Jan 3, 2023
Messages
116
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

Well-known member
Local time
Today, 08:34
Joined
Feb 7, 2020
Messages
1,982
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:
Code:
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

Member
Local time
Today, 09:34
Joined
Jan 3, 2023
Messages
116
What do you mean by analyze it? how can I know what is the subject and what is the body?
 

mike60smart

Registered User.
Local time
Today, 07:34
Joined
Aug 6, 2017
Messages
1,916
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.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 07:34
Joined
Feb 19, 2013
Messages
16,668
on the ribbon - external data > outlook> follow the prompts
 

ilanray

Member
Local time
Today, 09:34
Joined
Jan 3, 2023
Messages
116
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

Super Moderator
Staff member
Local time
Today, 07:34
Joined
Feb 19, 2013
Messages
16,668
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

Registered User.
Local time
Today, 07:34
Joined
Aug 6, 2017
Messages
1,916
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

Member
Local time
Today, 09:34
Joined
Jan 3, 2023
Messages
116
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

Registered User.
Local time
Today, 07:34
Joined
Aug 6, 2017
Messages
1,916
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.
 

Attachments

  • link.png
    link.png
    76 KB · Views: 33

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:34
Joined
Feb 19, 2002
Messages
43,484
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

Member
Local time
Today, 09:34
Joined
Jan 3, 2023
Messages
116
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

Registered User.
Local time
Today, 07:34
Joined
Aug 6, 2017
Messages
1,916
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.
 

Attachments

  • mail.png
    mail.png
    24.2 KB · Views: 32

Gasman

Enthusiastic Amateur
Local time
Today, 07:34
Joined
Sep 21, 2011
Messages
14,447
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++
1707939530964.png
 

ebs17

Well-known member
Local time
Today, 08:34
Joined
Feb 7, 2020
Messages
1,982
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

Enthusiastic Amateur
Local time
Today, 07:34
Joined
Sep 21, 2011
Messages
14,447
Can't see why you cannot just use automation to read the contents direct from the email?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 07:34
Joined
Feb 19, 2013
Messages
16,668
You can save a readable file called a .mht
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:34
Joined
Feb 19, 2002
Messages
43,484
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?
 

Users who are viewing this thread

Top Bottom