none
How do export e-mail messages to Access using VBA RRS feed

  • Question

  • I'm trying to have emails be automatically exported to Access using a VBA. I found a code on techrepublic.com that looks like the answer, but since I am not familiar with VBA's I'm running into issues. The code is this: 

    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")

        Set objFolder = ns.PickFolder

        Dim adoConn As ADODB.Connection

        Dim adoRS As ADODB.Recordset

        Dim intCounter As Integer

        Set adoConn = CreateObject("ADODB.Connection")

        Set adoRS = CreateObject("ADODB.Recordset")

         'DSN and target file must exist.

        adoConn.Open "DSN=OutlookData;"

        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("Attachments") = .Attachment

                    adoRS.Update

                End If

            End With

        Next

        adoRS.Close

        Set adoRS = Nothing

        Set adoConn = Nothing

        Set ns = Nothing

        Set objFolder = Nothing

    End Sub

    The link for where I got this is here: http://www.techrepublic.com/blog/how-do-i/how-do-i-export-e-mail-messages-to-access-using-vba/

    I have only gotten to the point where I am in Visual Basic for Applications, I went to Insert and selected Module. I then copied and pasted the code listed above. I then tried to run it, but an error comes up that says "Compile Error: User-Defined type not defined." I tried following the instructions exactly. The biggest thing that confuses me is that it says to run the wizard and i have no idea how to do that. If someone could review the instructions and code and then dumbify it down to where I understand and can get it to work that would be amazing.

    I'm trying to do it on my personal computer before doing it on my work computer. I'm doing this because I share an internal email at work with 2 other coworkers and one of the coworkers insists that we print each and every email and our responses, reference them, and then file them. It's ridiculous and it's taking up time, resources, and space. I've tried telling him that everything gets archived but he refuses to listen. So I figure if I meet him halfway and have our emails automatically saved in a file that he can easily access then it will shave down on time, resources, and space. Please help. 

    Thursday, October 5, 2017 2:43 AM

All replies

  • Hello,

    >>Compile Error: User-Defined type not defined

    After clicking Ok, which line would be selected? I think it may highlight "Dim adoConn As ADODB.Connection". If it does, please check if you add reference Microsoft ActiveX Data Objects x.x Library.

    In the VBE, please click Tools -> References.. and then check Microsoft ActiveX Data Objects 6.1 Library.

    Besides, please follow the other steps in the link you found. The DSN file should be created as you are connecting the db using this DSN.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 5, 2017 8:14 AM
    Moderator