Automatic email export to access VBA

Hi, I know how to manually export the emails from the inbox, into a Microsoft access file called in my case "TimeForYou.mdb". However, I would like a VBA code to firstly, sync all emails (so download them all) and secondly, export the emails to the database. The database is password protected and during the manual export I have to type a password in 3 times.

I have looked for other examples but they never seem to work. Also can this VBA run immediately as outlook is opened. I am new to outlook.

Thanks in advance, Dan.

Welcome to the forums Dan :wave:

Quote:

---

Also can this VBA run immediately as outlook is opened.

---

Yes, It can. However I would advice you not to do that... as it will slow down your outlook. What I would suggest is create a macro which you may run on a specific date or on weekends...

Quote:

---

I have looked for other examples but they never seem to work.

---

What code(s) have you tried?

Hi koolsid,

I like you're idea about running it on the weekends or selected dates, and also thought if it ran when i turned the database on, say it opens access and runs the macro - that's just an idea.

Here are the URL's I went on with the code I have tried, but I am unsure completely on what happens in them or anything really. I would be very grateful with your help. I will come back to the post tomorrow with more information if you need it.

Thanks a lot, Dan.

Quote:

---

Here are the URL's I went on with the code I have tried,

---

URL's missing...

Oh Sorry, I forgot to add them.

Here they are:

http://blogs.techrepublic.com.com/ho...&tag=col1;col1
http://support.microsoft.com/?kbid=290792
http://www.blueclaw-db.com/read_emai...ss_outlook.htm

There are a few, I tried. I think some are VBA from outlook and some are from access.
Thanks alot, and sorry for the mistake,
Dan.

Ok

1) The first link should work.
2) Ignore the 2nd and the 3rd link. It's not updated. I can make it out as it still suggests using DAO :)

Let me check the First link...

Edit:

The first link will work. What is the problem that you are facing if you have tried that code?

OK, thank you. If you got try it out, and just explain exactly where i put the code (in access or outlook) and other information that I may trip up on. Thanks a lot.

Dan, It will go in Outlook and not Access. It is mentioned in that site as well :)

Quote:

---

Automating the process via a macro (a VBA function) is more complicated but certainly doable. In Outlook, press Alt+F11 to launch the Visual Basic Editor (VBE). Choose Module from the Insert menu and then enter the code shown in Listing A.

---

Just follow the instructions mentioned in that link and it will just work fine. Be very patient when you go through the link ;)

If it still doesn't work then show me the actual code that you tried and we will take it from there...

OK, I will try it now. Thanks

I am using windows 7 and it explains how to create a DSN on XP. I know this is similar but this is where I think I go wrong? Any help?

As the link mentions, there are two ways.. either through DSN or through connection string :)

For DSN, the world is at your feet ;)

http://www.google.co.in/#hl=en&sourc...bb1200aec65a16

For connection string see this link :)

http://connectionstrings.com/

Thank you, I'm starting the DNS now.
I've noted the code that I have used, Just to see if I have missed anything out. If you could explain what some of the words are, it would be great.

Quote:

---

Sub ExportMailByFolder()
'Export specified fields from each mail
'item in selected folder.
Dim ns As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Set ns = GetNamespace("MAPI") 'WHAT IS THE "MAPI"? Unsure?
Set objFolder = ns.PickFolder
Dim adoConn As ADODB.Connection 'THE "ADODB"? Unsure of this Also?
Dim adoRS As ADODB.Recordset 'THE "ADODB"? Unsure of this Also?
Dim intCounter As Integer
Set adoConn = CreateObject("ADODB.Connection")
Set adoRS = CreateObject("ADODB.Recordset")
'DSN and target file must exist.
adoConn.Open "DSN=OutlookData;" 'DO NOT KNOW HOW TO CREATE A DSN
adoRS.Open "SELECT * FROM email", adoConn, _
adOpenDynamic, adLockOptimistic
'Cycle through selected folder.
For intCounter = objFolder.Items.Count To 1 Step -1
With objFolder.Items(intCounter)
'Copy property value to corresponding fields
'in target file.
If .Class = olMail Then
adoRS.AddNew
adoRS("Subject") = .Subject
adoRS("Body") = .Body
adoRS("FromName") = .SenderName
adoRS("ToName") = .To
adoRS("FromAddress") = .SenderEmailAddress
adoRS("FromType") = .SenderEmailType
adoRS("CCName") = .CC
adoRS("BCCName") = .BCC
adoRS("Importance") = .Importance
adoRS("Sensitivity") = .Sensitivity
adoRS ("EmailID")
adoRS.Update
End If
End With
Next
adoRS.Close
Set adoRS = Nothing
Set adoConn = Nothing
Set ns = Nothing
Set objFolder = Nothing
End Sub

---

Thank you

Right, I have started creating a DSN, but i get to a point that it doesn't show the Ms access driver .mdb

Quote:

---

In the next window, choose MS Access Driver (.mdb) and click Finish.

---

Instead it gives me "SQL Server" - That's the only driver I have on option.

OK, I think I have done the DSN, by not creating a new one, but by using the name of the one that comes up in the manual export. I have changed the name outlookdata to DVZ_DSN which is the one that comes up in the manual export.. Now all i need is some help with the code itself?

Quote:

---

Sub ExportMailByFolder()
'Export specified fields from each mail
'item in selected folder.
Dim ns As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Set ns = GetNamespace("MAPI") 'WHAT IS THE "MAPI"? Unsure?
Set objFolder = ns.PickFolder
Dim adoConn As ADODB.Connection 'THE "ADODB"? Unsure of this Also?
Dim adoRS As ADODB.Recordset 'THE "ADODB"? Unsure of this Also?
Dim intCounter As Integer
Set adoConn = CreateObject("ADODB.Connection")
Set adoRS = CreateObject("ADODB.Recordset")
'DSN and target file must exist.
adoConn.Open "DSN=OutlookData;" 'DO NOT KNOW HOW TO CREATE A DSN
adoRS.Open "SELECT * FROM email", adoConn, _
adOpenDynamic, adLockOptimistic
'Cycle through selected folder.
For intCounter = objFolder.Items.Count To 1 Step -1
With objFolder.Items(intCounter)
'Copy property value to corresponding fields
'in target file.
If .Class = olMail Then
adoRS.AddNew
adoRS("Subject") = .Subject
adoRS("Body") = .Body
adoRS("FromName") = .SenderName
adoRS("ToName") = .To
adoRS("FromAddress") = .SenderEmailAddress
adoRS("FromType") = .SenderEmailType
adoRS("CCName") = .CC
adoRS("BCCName") = .BCC
adoRS("Importance") = .Importance
adoRS("Sensitivity") = .Sensitivity
adoRS ("EmailID")
adoRS.Update
End If
End With
Next
adoRS.Close
Set adoRS = Nothing
Set adoConn = Nothing
Set ns = Nothing
Set objFolder = Nothing
End Sub

---

I actually think there may be a chance of getting this to work - hopefully! Thank you.

Quote:

---

I actually think there may be a chance of getting this to work - hopefully! Thank you.

---

In that case half of the battle is already won ;) And I must say that you are doing good!

Example 1

Quote:

---

'WHAT IS THE "MAPI"? Unsure?

---

http://www.google.co.in/search?hl=en...g&ved=0CBUQkAE

Example 2

Quote:

---

'THE "ADODB"? Unsure of this Also?

---

I can give you the answer or a link but would you like to try finding it like I did above? ;) What if my connection died or VBF is down at the moment... what will you do :D?

Yes, I will. So the code that I have just shown you, do I need to customise it anymore? When I have tried running the macro it shows the ADODB and says an error such as defined user not predefined or something like that. I'll look at them now, but could you tell me what parts of the code I need to change for my situation as I have no clue.

It highlights Dim adoConn As ADODB.Connection and says the Compile Error: User-Define Type not Defined?

Ok, Here is a sample that I created. It is not tested. Please read the comments in green before trying out the code...

Code:

---

'~~>                        READ ME FIRST
'
'~~> The Sample assumes that your VBA project has added a reference to
'~~> the ADO object library. You can do this from within the VBE by
'~~> selecting the menu Tools, References and selecting Microsoft
'~~> ActiveX Data Objects x.x Object Library.
'
'~~> This sample also assumes that you have a database with the name
'~~> MyDatabase.mdb which resides in C:\ and has the following fields
'~~> "Subject","Body","FromName","ToName","FromAddress","FromType"
'~~> "CCName","BCCName","Importance" and "Sensitivity"
'
'~~> This sample also assumes that the database has a table called Table1

Dim adoConn As ADODB.Connection, adoRS As ADODB.Recordset
Dim DBFullName As String
Dim ns As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Dim i As Long

Sub ExportToAccess()
    Set ns = GetNamespace("MAPI")
    Set objFolder = ns.PickFolder

    '~~> Replace with your database
    DBFullName = "C:\MyDatabase.mdb"
   
    Set adoConn = New ADODB.Connection
   
    '~~> Open the database using connection string
    adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
    DBFullName & ";"
   
    Set adoRS = New ADODB.Recordset
   
    With adoRS
        '~~> Replace Table1 with the name of the table in your database
        .Open "SELECT * FROM " & Table1, adoConn, , , adCmdText
        For i = objFolder.Items.Count To 1 Step -1
            With objFolder.Items(i)
                If .Class = olMail Then
                    adoRS.AddNew
                    adoRS("Subject") = .Subject
                    adoRS("Body") = .Body
                    adoRS("FromName") = .SenderName
                    adoRS("ToName") = .To
                    adoRS("FromAddress") = .SenderEmailAddress
                    adoRS("FromType") = .SenderEmailType
                    adoRS("CCName") = .CC
                    adoRS("BCCName") = .BCC
                    adoRS("Importance") = .Importance
                    adoRS("Sensitivity") = .Sensitivity
                    adoRS.Update
                End If
            End With
        Next
    End With
   
    '~~> Close and Cleanup
    adoRS.Close
    Set adoRS = Nothing
    Set adoConn = Nothing
    Set ns = Nothing
    Set objFolder = Nothing
End Sub

---

Hope this helps :)

Hi, that is great - however, after I select the folder I want to export from I get a message saying Not a Valid Password. Any Ideas? Also, the table on the database has another field called EmailID which is an autonumber, how do i add this to the VBA? Thank you so much for this. The database has a password, which on the manual export I would type in with the username as admin.

Sorry... I am trying to answer many threads in one go so it is taking sometime for me to get back to this thread... :)

Ok!

1) Is your database ready?
2) Have you amended the above code accordingly?
3) Is the code working? If it is asking for the password then I guess it is working till that past :)
4) Don't worry about autonumber fields. They will be updated automatically.
5) You want to enter the password through the code?

Quote:

---

1) Is your database ready?

---

Yes

Quote:

---

2) Have you amended the above code accordingly?

---

Yes

Quote:

---

3) Is the code working? If it is asking for the password then I guess it is working till that past.

---

No it is not asking for the password and instead straight after I select a folder to export from it says "Run Time Error '-2147217### (80040###)': Not a Valid Password"
When I click Debug it takes me to
adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
DBFullName & ";"

Quote:

---

4) Don't worry about autonumber fields. They will be updated automatically.

---

OK

Quote:

---

5) You want to enter the password through the code?

---

Yes

Don't worry about me waiting, I am very grateful for your help. Thank you.

(I've added #'s over some of the numbers, just in case that is reading a password of some sort.)

Replace

Quote:

---

'~~> Open the database using connection string
adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
DBFullName & ";"

---

with

Code:

---

    '~~> Open the database using connection string
    adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
    DBFullName & ";User Id=<Username>;Password=<Password>;"

---

and try again...

Hi Koolsid,
I have changed the database now so that it doesn't seem to require a password to export into. So now I am still using the first peice of code

Code:

---

'~~> Open the database using connection string
adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
DBFullName & ";"

---

However, it now passes this peice of code fine and the has a syntax error after I select the folder to export from:

Quote:

---

Run-time error '-2147217900 (80040e14)':
Syntax error in FROM clause.

---

When I click debug it takes me to

Quote:

---

.Open "SELECT * FROM " & Email, adoConn, , , adCmdText

---

I have the table 'Email' which I have used instead of 'Table1', which has the required fields in it.

Thanks for your help, I think it will work soon,
Dan.

The code that I gave was an example...

Try this

Code:

---

.Open "SELECT * FROM EMAIL", adoConn, , , adCmdText

---

I have changed that and then I get an error message saying

Quote:

---

Run Time Error '3251':
Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

---

When I click 'Debug' it highlights

vb Code:

adoRS.AddNew

.

Does this mean a setting on my database does not support updating? Or is it another example code that is not correct. Sorry about this taking longer than it should, but at least now I'm close to the end of the VBCode so there can't be many problems left.

Thanks a lot,
Dan.

If you would have searched Google for that error message then you would already have the answer by now ;)

Check this link

http://support.microsoft.com/kb/289675

Thank you so much as I have managed to get it working now - even though i did need to delete some of the code:

Code:

---

                    adoRS("FromType") = .SenderEmailType
                    adoRS("CCName") = .CC
                    adoRS("BCCName") = .BCC
                    adoRS("Importance") = .Importance
                    adoRS("Sensitivity") = .Sensitivity
                    adoRS("Received") = .Received

---

but that doesn't matter I don't think.

All I ask now is that I can have the macro run when I either get new emails, or exit outlook - whichever is easiest - probably exit outlook would be best.

And also is there a way that it only adds the new emails instead of all, because it adds the same email again and again and it would be good if no duplicates could happen. Maybe if it wasn't add but replace all emails? or just maybe getting it to add only unread emails - this would have to happen as the emails are downloaded though.

Thank you so much for helping me this far and I really appreciate your help on this. I know I must get annoying at times, but I really do appreciate your help on this.

Thank you,
Dan.

Would suggest you to run it the moment the new mail comes.

Check this link ;)

http://www.vbforums.com/showthread.php?t=603677

I've done that. How about only exporting the unread emails? Is there something you can add to the vba. Thank you,
Dan

Quote:

---

Is there something you can add to the vba.

---

You need to use

Code:

---

If Item.UnRead then

---

It says Object Required and goes to

Code:

---

If Items.UnRead Then

---

This is my code now:

Code:

---

'~~>                        READ ME FIRST
'
'~~> The Sample assumes that your VBA project has added a reference to
'~~> the ADO object library. You can do this from within the VBE by
'~~> selecting the menu Tools, References and selecting Microsoft
'~~> ActiveX Data Objects x.x Object Library.
'
'~~> This sample also assumes that you have a database with the name
'~~> MyDatabase.mdb which resides in C:\ and has the following fields
'~~> "Subject","Body","FromName","ToName","FromAddress","FromType"
'~~> "CCName","BCCName","Importance" and "Sensitivity"
'
'~~> This sample also assumes that the database has a table called Table1

Dim adoConn As ADODB.Connection, adoRS As ADODB.Recordset
Dim DBFullName As String
Dim ns As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Dim i As Long

Sub ExportToOutlook()
    Set ns = GetNamespace("MAPI")
    Set objFolder = ns.PickFolder

    '~~> Replace with your database
    DBFullName = "C:\Users\Chris\Desktop\TFY Database.mdb"
   
    Set adoConn = New ADODB.Connection
   
'~~> Open the database using connection string
adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
DBFullName & ";"
   
    Set adoRS = New ADODB.Recordset
            If Items.UnRead Then
    With adoRS
        '~~> Replace Table1 with the name of the table in your database
        .Open "SELECT * FROM EMAIL", adoConn, , adLockOptimistic, adCmdText

        For i = objFolder.Items.Count To 1 Step -1
            With objFolder.Items(i)
                If .Class = olMail Then
                    adoRS.AddNew
                    adoRS("Subject") = .Subject
                    adoRS("Body") = .Body
                    adoRS("FromName") = .SenderName
                    adoRS("ToName") = .To
                    adoRS.Update
                End If
            End With
        Next
    End With
    End If
    '~~> Close and Cleanup
    adoRS.Close
    Set adoRS = Nothing
    Set adoConn = Nothing
    Set ns = Nothing
    Set objFolder = Nothing
End Sub

---