-
Aug 10th, 2010, 10:57 AM
#1
Thread Starter
Junior Member
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.
-
Aug 10th, 2010, 05:03 PM
#2
Re: Automatic email export to access VBA
Welcome to the forums Dan
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...
I have looked for other examples but they never seem to work.
What code(s) have you tried?
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Aug 10th, 2010, 05:44 PM
#3
Thread Starter
Junior Member
Re: Automatic email export to access VBA
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.
-
Aug 10th, 2010, 05:53 PM
#4
Re: Automatic email export to access VBA
Here are the URL's I went on with the code I have tried,
URL's missing...
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Aug 11th, 2010, 12:41 PM
#5
Thread Starter
Junior Member
Re: Automatic email export to access VBA
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.
-
Aug 11th, 2010, 12:45 PM
#6
Re: Automatic email export to access VBA
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?
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Aug 11th, 2010, 12:47 PM
#7
Thread Starter
Junior Member
Re: Automatic email export to access VBA
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.
-
Aug 11th, 2010, 12:51 PM
#8
Re: Automatic email export to access VBA
Dan, It will go in Outlook and not Access. It is mentioned in that site as well
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...
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Aug 11th, 2010, 12:53 PM
#9
Thread Starter
Junior Member
Re: Automatic email export to access VBA
OK, I will try it now. Thanks
-
Aug 11th, 2010, 01:00 PM
#10
Thread Starter
Junior Member
Re: Automatic email export to access VBA
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?
-
Aug 11th, 2010, 01:14 PM
#11
-
Aug 11th, 2010, 01:37 PM
#12
Thread Starter
Junior Member
Re: Automatic email export to access VBA
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.
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
-
Aug 11th, 2010, 01:42 PM
#13
Thread Starter
Junior Member
Re: Automatic email export to access VBA
Right, I have started creating a DSN, but i get to a point that it doesn't show the Ms access driver .mdb
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.
-
Aug 11th, 2010, 01:52 PM
#14
Thread Starter
Junior Member
Re: Automatic email export to access VBA
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?
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.
Last edited by DanJames; Aug 11th, 2010 at 02:00 PM.
-
Aug 11th, 2010, 02:12 PM
#15
Re: Automatic email export to access VBA
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
'WHAT IS THE "MAPI"? Unsure?
http://www.google.co.in/search?hl=en...g&ved=0CBUQkAE
Example 2
'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 ?
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Aug 11th, 2010, 02:14 PM
#16
Thread Starter
Junior Member
Re: Automatic email export to access VBA
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?
-
Aug 11th, 2010, 03:08 PM
#17
Re: Automatic email export to access VBA
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
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Aug 11th, 2010, 03:32 PM
#18
Thread Starter
Junior Member
Re: Automatic email export to access VBA
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.
-
Aug 11th, 2010, 04:51 PM
#19
-
Aug 12th, 2010, 03:18 AM
#20
Thread Starter
Junior Member
Re: Automatic email export to access VBA
1) Is your database ready?
Yes
2) Have you amended the above code accordingly?
Yes
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 & ";"
4) Don't worry about autonumber fields. They will be updated automatically.
OK
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.)
Last edited by DanJames; Aug 12th, 2010 at 11:07 AM.
-
Aug 16th, 2010, 05:43 PM
#21
Re: Automatic email export to access VBA
Replace
'~~> 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...
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Aug 17th, 2010, 07:49 AM
#22
Thread Starter
Junior Member
Re: Automatic email export to access VBA
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:
Run-time error '-2147217900 (80040e14)':
Syntax error in FROM clause.
When I click debug it takes me to
.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.
-
Aug 18th, 2010, 04:04 PM
#23
Re: Automatic email export to access VBA
The code that I gave was an example...
Try this
Code:
.Open "SELECT * FROM EMAIL", adoConn, , , adCmdText
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Aug 19th, 2010, 06:36 AM
#24
Thread Starter
Junior Member
Re: Automatic email export to access VBA
I have changed that and then I get an error message saying
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 .
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.
-
Aug 29th, 2010, 06:39 PM
#25
Re: Automatic email export to access VBA
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
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Aug 30th, 2010, 06:38 AM
#26
Thread Starter
Junior Member
Re: Automatic email export to access VBA
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.
-
Aug 30th, 2010, 06:46 AM
#27
Re: Automatic email export to access VBA
Would suggest you to run it the moment the new mail comes.
Check this link
http://www.vbforums.com/showthread.php?t=603677
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Aug 30th, 2010, 02:33 PM
#28
Thread Starter
Junior Member
Re: Automatic email export to access VBA
I've done that. How about only exporting the unread emails? Is there something you can add to the vba. Thank you,
Dan
-
Aug 30th, 2010, 03:03 PM
#29
Re: Automatic email export to access VBA
Is there something you can add to the vba.
You need to use
Code:
If Item.UnRead then
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Aug 30th, 2010, 03:43 PM
#30
Thread Starter
Junior Member
Re: Automatic email export to access VBA
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|