VB6 - Send specific Microsoft access table into XLS file format by email

Posted on 2014-07-13
Last Modified: 2014-07-14

I have the below code that send to email the full Microsoft Access dbase by email. The thing is that i would like to only send specific tables inside the access dbase, and in XLS format.

How can i do this?

If i want to send by email 2 table out of 10, and in xls format, can i send all 5 in one single Excel file with 5 sheets based on the 5 tables or it can only be be separated by 5 different Excel file, one per table name?

Thanks again for your help

Thanks again

This is what i use now:
        Dim patha As String
        Dim result As Integer
        Dim displaymessage As Boolean
        Dim objOutlook As Outlook.Application
        Dim objOutlookMsg As Outlook.MailItem
        Dim objOutlookRecip As Outlook.Recipient
        Dim objOutlookAttach As Outlook.Attachment
        patha = dta_base_link_audit_tms.Text
        displaymessage = True
        ' Create the Outlook session.
        Set objOutlook = CreateObject("Outlook.Application")

        ' Create the message.
        Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

        With objOutlookMsg
            ' Add the To recipient(s) to the message.
            Set objOutlookRecip = .Recipients.Add("email address")
            objOutlookRecip.Type = olTo

            ' Add the CC recipient(s) to the message.
            'Set objOutlookRecip = .Recipients.Add("")
            ' objOutlookRecip.Type = olCC

            ' Add the BCC recipient(s) to the message.
            '  Set objOutlookRecip = .Recipients.Add("")
            ' objOutlookRecip.Type = olBCC

            ' Set the Subject, Body, and Importance of the message.
            .Subject = "dbase backup on " & Format(Now, "mmm dd yyyy hh:mm")
            .Body = "This is the audit dbase backup updated on " & Format(Now, "mmm dd yyyy hh:mm") & vbCrLf & vbCrLf
            .Importance = olImportanceHigh  'High importance

            ' Add attachments to the message.
            'If Not IsMissing(AttachmentPath) Then
            Set objOutlookAttach = .Attachments.Add(patha)
            'End If

            ' Resolve each Recipient's name.
            For Each objOutlookRecip In .Recipients

            ' Should we display the message before sending?
            If displaymessage Then
            End If
        End With
        Set objOutlook = Nothing

Open in new window

Question by:Wilder1626
    LVL 33

    Expert Comment

    Which tables do you want to send?

    Do you want them all to be sent as attachments to the same message?
    LVL 11

    Author Comment

    out of 10 tables, only 5 of them needs to be sent in an email.

    Table names are:

    If they could be sent in the same email, that would be great. If it can only be 1 file per email, it can also be good, but the email subject will have to have the table name inside.
    LVL 44

    Assisted Solution

    It's going to look something like this
    vTabNames = Array("Employes", "Items", "Sales", "Prices", "Vendors")
    set oXL = CreateObject("Excel.Application")
    set oWkb = ActiveWorkbook
    For Each vItem In vTabNames
    	Set rs = dbEngine(0)(0).OpenRecordset(vItem, dbopendynaset)
    	set oWks = oWkb.Worksheets.add = vItem
    	oWks.range("A1").CopyFromRecordset rs
    oWkb.close true
    set oWkb = nothing
    set oXL = Nothing

    Open in new window

    LVL 9

    Accepted Solution


    Insert this code into Line 33 of your code.  It will open the Access DB , copy the Tables to an Excel sheet, and then close the DB.  you can then send the strXL_Export_File   (Tested code and it works...)

                'Tools --> References --> Microsoft Access ##.0 Object Library
                Dim strDB_Path
                Dim strXL_Export_Path
                Dim strXL_Export_File
                Dim objAccess As Access.Application
                strDB_Path = "C:\Temp\Test.mdb"
                strXL_Export_Path = "C:\Temp\"
                strXL_Export_File = strXL_Export_Path & "Export_" & Format(Date, "yyyyMMdd") & ".xls"
                Set objAccess = CreateObject("Access.Application")
                objAccess.OpenCurrentDatabase strDB_Path, True
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1", strXL_Export_File, True
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table2", strXL_Export_File, True
                Set objAccess = Nothing

    Open in new window

    LVL 9

    Expert Comment

    One more thing,  This code will create one Workbook with each sheet set to the table name so you only have to send one email with one XL file.
    LVL 11

    Author Closing Comment

    This is very good. thanks again for all the help. Now able to send an xls in attachment with only for tables i need.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now