Solved

Access 97 Sending object

Posted on 2001-06-20
12
286 Views
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?
0
Comment
Question by:TheClicker
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 3

Expert Comment

by:strykker
Comment Utility
try using an outlook object instead of the sendto.  That will allow you to add an attachment, such as an excel workbook.  

HTH
Mike
0
 
LVL 6

Expert Comment

by:simonbennett
Comment Utility
Hi

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.

HTH

Simon
0
 
LVL 6

Expert Comment

by:simonbennett
Comment Utility
Hmmm

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

Simon
0
 
LVL 14

Expert Comment

by:mgrattan
Comment Utility
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])
0
 
LVL 6

Expert Comment

by:PsychoDazey
Comment Utility
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.
0
 
LVL 14

Expert Comment

by:mgrattan
Comment Utility
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?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:TheClicker
Comment Utility
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.
0
 
LVL 6

Expert Comment

by:PsychoDazey
Comment Utility
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
            objOutlookRecip.Resolve
        Next
       
        ' Should we display the message before sending?
        If DisplayMsg Then
            .Display
        Else
            .Send
        End If

    End With
    Set objOutlook = Nothing
End Sub
0
 
LVL 6

Expert Comment

by:PsychoDazey
Comment Utility
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
            Else
                strRecipients = frm!txtCC
                ' Parse recipients and add them to objects
                ' Recipients collection.
                Do
                    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
            Else
                strAttachments = frm!txtAttachments
                ' Parse Attachments and add them to the Attachments collection.
                Do
                    .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
            .Display
        Else
            .Send
           
        End If
    End With
    DoCmd.Close
       
    SendNewMail = True
   
SendMail_Bye:
    DoCmd.Hourglass False
    Exit Function
SendMail_Err:
    SendNewMail = False
    MsgBox "Error " & Err.Number & vbCrLf & Err.Description
    Resume SendMail_Bye
End Function
0
 

Author Comment

by:TheClicker
Comment Utility
Psycho,

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.

Thanks,

TheClicker
0
 
LVL 6

Accepted Solution

by:
PsychoDazey earned 200 total points
Comment Utility
Clicker-
Change this line:
Set objOutlookRecip = .Recipients.Add("Nancy Davolio")
To:
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.
0
 

Author Comment

by:TheClicker
Comment Utility
Sorry this took so long - I have been avoiding this app since I graduated!  Thanks for your help!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

743 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

12 Experts available now in Live!

Get 1:1 Help Now