Automatic email export to access VBA-VBForums
Results 1 to 30 of 30

Thread: Automatic email export to access VBA

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Aug 2010
    Posts
    17

    Thumbs up 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.

  2. #2
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    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?
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Aug 2010
    Posts
    17

    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.

  4. #4
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: Automatic email export to access VBA

    Here are the URL's I went on with the code I have tried,
    URL's missing...
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Aug 2010
    Posts
    17

    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.

  6. #6
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    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?
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Aug 2010
    Posts
    17

    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.

  8. #8
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    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...
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Aug 2010
    Posts
    17

    Re: Automatic email export to access VBA

    OK, I will try it now. Thanks

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Aug 2010
    Posts
    17

    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?

  11. #11
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: Automatic email export to access VBA

    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/
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Aug 2010
    Posts
    17

    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

  13. #13

    Thread Starter
    Junior Member
    Join Date
    Aug 2010
    Posts
    17

    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.

  14. #14

    Thread Starter
    Junior Member
    Join Date
    Aug 2010
    Posts
    17

    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 03:00 PM.

  15. #15
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    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 ?
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  16. #16

    Thread Starter
    Junior Member
    Join Date
    Aug 2010
    Posts
    17

    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?

  17. #17
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    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
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  18. #18

    Thread Starter
    Junior Member
    Join Date
    Aug 2010
    Posts
    17

    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.

  19. #19
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: Automatic email export to access VBA

    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?
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  20. #20

    Thread Starter
    Junior Member
    Join Date
    Aug 2010
    Posts
    17

    Thumbs up 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 12:07 PM.

  21. #21
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    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...
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  22. #22

    Thread Starter
    Junior Member
    Join Date
    Aug 2010
    Posts
    17

    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.

  23. #23
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: Automatic email export to access VBA

    The code that I gave was an example...

    Try this

    Code:
    .Open "SELECT * FROM EMAIL", adoConn, , , adCmdText
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  24. #24

    Thread Starter
    Junior Member
    Join Date
    Aug 2010
    Posts
    17

    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
    vb Code:
    1. 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.

  25. #25
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    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
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  26. #26

    Thread Starter
    Junior Member
    Join Date
    Aug 2010
    Posts
    17

    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.

  27. #27
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    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
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  28. #28

    Thread Starter
    Junior Member
    Join Date
    Aug 2010
    Posts
    17

    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

  29. #29
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    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
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  30. #30

    Thread Starter
    Junior Member
    Join Date
    Aug 2010
    Posts
    17

    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
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.