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


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

LVL 11
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
Which tables do you want to send?

Do you want them all to be sent as attachments to the same message?
Wilder1626Author Commented:
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.
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
	oWks.name = vItem
	oWks.range("A1").CopyFromRecordset rs
oWkb.close true
set oWkb = nothing
set oXL = Nothing

Open in new window

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.


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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
Wilder1626Author Commented:
This is very good. thanks again for all the help. Now able to send an xls in attachment with only for tables i need.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.