Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Access 97 Sending object

Posted on 2001-06-20
Medium Priority
Last Modified: 2008-02-01
I am sending a table in excel format through e-mail.  The table will then be linked into another database (don't ask - client preference).  I am using Access 97.  The send to function works fine, but when we save the table and try to import it is says it is in the wrong format.  I know access had a problem with the outputto command doing this and the workaround was to use the transferspreadsheet option - does anyone know of something similar I can use to e-mail a table in the correct format?  Can the sendto add an external table to an e-mail?
Question by:TheClicker
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2

Expert Comment

ID: 6210583
try using an outlook object instead of the sendto.  That will allow you to add an attachment, such as an excel workbook.  


Expert Comment

ID: 6210680

Have you tried sendobject (excuse the formatting?

The SendObject action has the following arguments.

Action argument Description
Object Type The type of object to include in the mail message. Click Table (for a table datasheet), Query (for a query datasheet), Form (for a form or form datasheet), Report, Module, or Data Access Page, Server View, or Stored Procedures in the Object Type box in the Action Arguments section of the Macro window. You can't send a macro. If you want to include the active object, select its type with this argument, but leave the Object Name argument blank.  
Object Name The name of the object to include in the mail message. The Object Name box shows all objects in the database of the type selected by the Object Type argument. If you leave both the Object Type and Object Name arguments blank, Microsoft Access sends a message to the mail application without any database object.
  If you run a macro containing the SendObject action in a library database, Microsoft Access looks for the object with this name first in the library database, then in the current database.
Output Format The type of format you want used for the included object. You can click HTML (*.html), Microsoft Excel (*.xls), MS-DOS Text (*.txt), Rich Text Format (*.rtf), or Microsoft Access Data Access Page (*.html) in the Output Format box. Modules can be sent only in MS-DOS text format. Data access pages can only be sent in HTML format. If you leave this argument blank, Microsoft Access prompts you for the output format.
  Note   You can't include objects in Microsoft Active Server (*.asp) or Microsoft IIS (*.htx, *.idc) format, although these selections are displayed in the list  
To The recipients of the message whose names you want to put on the To line in the mail message. If you leave this argument blank, Microsoft Access prompts you for the recipients' names.
  Separate the recipients' names you specify in this argument and in the Cc and Bcc arguments with a semicolon (;) or with the list separator set on the Number tab of the Regional Settings Properties dialog box in Windows Control Panel. If the mail application can't identify the recipients' names, the message isn't sent and an error occurs.
Cc The recipients of the message whose names you want to put on the Cc ("carbon copy") line in the mail message. If you leave this argument blank, the Cc line in the mail message is blank.
Bcc The recipients of the message whose names you want to put on the Bcc ("blind carbon copy") line in the mail message. If you leave this argument blank, the Bcc line in the mail message is blank.
Subject The subject of the message. This text appears on the Subject line in the mail message. If you leave this argument blank, the Subject line in the mail message is blank.
Message Text Any text you want to include in the message in addition to the database object. This text appears in the main body of the mail message, after the object. If you leave this argument blank, no additional text is included in the mail message. If you leave the Object Type and Object Name arguments blank, you can use this argument to send a mail message without a database object.
Edit Message Specifies whether the message can be edited before it's sent. If you select Yes, the electronic mail application starts automatically, and the message can be edited. If you select No, the message is sent without providing a chance to edit the message. The default is No.
Template File The path and file name of a file you want to use as a template for an .html file. The template file is a file containing HTML tags.



Expert Comment

ID: 6210708

How about an exmaple

docmd.SendObject acSendTable,"<your tablename here>","Microsoft Excel (*.xls)","whoever@whereever.com",,,"<Your subject here>","PLease find attached a spreadsheet",false

Good luck

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

LVL 14

Expert Comment

ID: 6210906
My preference would be to use the DoCmd.TransferSpreadsheet command.  This command allows you to specify the version of Excel to use for the output file.

docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel97,"TableName","FileName.xls",[Has field names, true/false],[Range(used for Import only])

Expert Comment

ID: 6211029
I agree that using outlook may be the best method.  I have an application that will recieve e-mails, save the attachments as files and transfer the files into tables.  Let me know if you are interested in the code.
LVL 14

Expert Comment

ID: 6211092
Since you want to have it attached to an email I think you will want to use the object library of your email program.  If you're using Outlook, then I would agree that it would be the best bet to accomplish your goals.  My previous suggestion of using TransferSpreadsheet will get you the Excel file in a compatible format but it doesn't address the issue of attaching it to an email.

Are you using Outlook or some other email program?

Author Comment

ID: 6212931
I would LOVE to have the outlook code to do this.  It is one of the strangest errors I have ever seen, and the current way they are fixing it is to open the table from the attachment in excel, hit save, and then it is fine.  Strange.  Using transferspreadsheet won't work alone, but it would work if I could use an outlook call to attach an external file to an e-mail.  Any help is greatly greatly appreciated.

Expert Comment

ID: 6214208
This code sends an e-mail, withan optional attachment:
Sub SendMessage(DisplayMsg As Boolean, Optional AttachmentPath)
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
    ' 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("Nancy Davolio")
         objOutlookRecip.Type = olTo
         ' Add the CC recipient(s) to the message.
         Set objOutlookRecip = .Recipients.Add("Michael Suyama")
         objOutlookRecip.Type = olCC
        ' Add the BCC recipient(s) to the message.

         Set objOutlookRecip = .Recipients.Add("Andrew Fuller")
         objOutlookRecip.Type = olBCC
        ' Set the Subject, Body, and Importance of the message.
        .Subject = "This is an Automation test with Microsoft Outlook"
        .Body = "This is the body of the message." & vbCrLf & vbCrLf
        .Importance = olImportanceHigh  'High importance
        ' Add attachments to the message.
        If Not IsMissing(AttachmentPath) Then

            Set objOutlookAttach = .Attachments.Add(AttachmentPath)
        End If
        ' Resolve each Recipient's name.
        For Each objOutlookRecip In .Recipients
        ' Should we display the message before sending?
        If DisplayMsg Then
        End If

    End With
    Set objOutlook = Nothing
End Sub

Expert Comment

ID: 6214223
Here is another method for sending an attachment.  What I did next was use the transfer text method to read throughthe e-mails and import into a table.  If you would like this code also, Let me know.
Dim objNewMail As MailItem
    Dim objRecip As Recipient
    Dim strRecipients As String
    Dim strAttachments As String
    Dim blnResolved As Boolean
    Dim query
    On Error GoTo SendMail_Err
    DoCmd.Hourglass True
    ' Set global Application and NameSpace
    ' object variables, if necessary.
    If golApp Is Nothing Then
        If InitializeOutlook = False Then
            MsgBox "Unable to initialize Outlook Application or Namespace object variables!"
        End If
    End If
    ' Create new MailItem object.
    Set objNewMail = golApp.CreateItem(olMailItem)
    ' Add recipients to MailItem object's Recipients collection.
    With objNewMail
        .Recipients.Add frm!txtTo
         If Not IsNull(frm!txtCC) Then
            If InStr(frm!txtCC, ";") = 0 Then
                Set objRecip = .Recipients.Add(frm!txtCC)
                objRecip.Type = olCC
                strRecipients = frm!txtCC
                ' Parse recipients and add them to objects
                ' Recipients collection.
                    Set objRecip = .Recipients.Add(Left(strRecipients, InStr(strRecipients, ";") - 1))
                    objRecip.Type = olCC
                    strRecipients = Trim(Mid(strRecipients, InStr(strRecipients, ";") + 1))
                Loop While InStr(strRecipients, ";") <> 0
                If Len(strRecipients) > 0 Then
                    Set objRecip = .Recipients.Add(strRecipients)
                    objRecip.Type = olCC
                End If
            End If
        End If
        ' Let Outlook verify that these are valid recipients.
        blnResolved = .Recipients.ResolveAll
        ' Add attachments to MailItem object's Attachment's collection.
        If Not IsNull(frm!txtAttachments) Then
            If InStr(frm!txtAttachments, ";") = 0 Then
                .Attachments.Add frm!txtAttachments
                strAttachments = frm!txtAttachments
                ' Parse Attachments and add them to the Attachments collection.
                    .Attachments.Add Left(strAttachments, InStr(strAttachments, ";") - 1)
                    strAttachments = Trim(Mid(strAttachments, InStr(strAttachments, ";") + 1))
                Loop While InStr(strAttachments, ";") <> 0
                If Len(strAttachments) > 0 Then .Attachments.Add strAttachments
            End If
        End If
        ' Set MailItem object's Subject and Body properties.
        .Subject = Nz(frm!txtSubject, "")
        .Body = Nz(frm!txtMessage, "")
        ' Set MailItem object's Importance property.
        Select Case frm!ctlImportance
            Case olImportanceHigh
                .Importance = olImportanceHigh
            Case olImportanceNormal
                .Importance = olImportanceNormal
            Case olImportanceLow
                .Importance = olImportanceLow
        End Select
        ' Send or Display MailItem depending on user's choice.
        If frm!ctlViewMail.Value = 1 Or blnResolved = False Then
        End If
    End With
    SendNewMail = True
    DoCmd.Hourglass False
    Exit Function
    SendNewMail = False
    MsgBox "Error " & Err.Number & vbCrLf & Err.Description
    Resume SendMail_Bye
End Function

Author Comment

ID: 6226227

I like the first one, but I need it to run without adding specific recipients, they may send it to different people at different times - how do I manage that.  I am SO new to calling Outlook in code - also, the searching of e-mails for a specific attachment interests me very much.



Accepted Solution

PsychoDazey earned 800 total points
ID: 6229738
Change this line:
Set objOutlookRecip = .Recipients.Add("Nancy Davolio")
Set objOutlookRecip = .Recipients.Add(Me!FieldName)
You can use a multiple select list box to pass more than 1 recipient to an e-mail, just make sure you put a ; between each name.
I am travelling this week, but I will check in.

Author Comment

ID: 6414053
Sorry this took so long - I have been avoiding this app since I graduated!  Thanks for your help!

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

618 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