?
Solved

Sending mail attachments from Access 2000 through Outlook Express

Posted on 2003-03-25
9
Medium Priority
?
1,090 Views
Last Modified: 2007-12-19
Hi guys,

I've been trying to send some Access 2K reports to some of our associates and got stuck in a problem. I got the reports' generation working and now I need to send them as attachments in Excel format.

I've been searching for a possible solution and found this:

http://www.experts-exchange.com/Databases/MS_Access/Q_20293395.html

that sends simple OE messages, without attachments.

I need to send the reports as attachments.  Now, maybe I could use Outlook instead of OE, but keep getting a "can't open mail session" error and found no cure for that. I'm working on W2K Pro, Office2KPro, OE6.

Any help would be greatly appreciated!

Thanks in advance.

Mario
0
Comment
Question by:pipo_tin
[X]
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
9 Comments
 
LVL 5

Expert Comment

by:Higor
ID: 8207985
Use de docmd

this is a nice fonction
look like this

DoCmd.SendObject acSendNoObject, , (format), "adress", , , "subject", body, edit(false or true)

DoCmd.SendObject acSendNoObject,"roger.xls" , acFormatXLS, "roger@mail.com", , , "Hi", "hello honey", False
0
 
LVL 5

Expert Comment

by:Higor
ID: 8207995
some more info here

this link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/html/output/F1/D2/S5A20E.asp

or read hehe :) :


SendObject Method
Applies To   DoCmd object.

Description

The SendObject method carries out the SendObject action in Visual Basic. For more information on how the action and its arguments work, see the action topic.

Syntax

DoCmd.SendObject [objecttype][, objectname][, outputformat][, to][, cc][, bcc]
z[, subject][, messagetext][, editmessage][, templatefile]

The SendObject method has the following arguments.

Argument
 Description

 

 
objecttype
 One of the following intrinsic constants:
 

 acSendForm
acSendModule
acSendNoObject (default)
acSendQuery
acSendReport
acSendTable
 
objectname
 A string expression that's the valid name of an object of the type selected by the objecttype argument. If you want to include the active object in the mail message, specify the object's type with the objecttype argument and leave this argument blank. If you leave both the objecttype and objectname arguments blank (the default constant, acSendNoObject, is assumed for the objecttype argument), Microsoft Access sends a message to the electronic mail application without an included database object.
 

 If you run Visual Basic code containing the SendObject method in a library database, Microsoft Access looks for the object with this name first in the library database, then in the current database.
 
outputformat
 One of the following intrinsic constants:
 

 acFormatHTML
acFormatRTF
acFormatTXT
acFormatXLS
 

 If you leave this argument blank, Microsoft Access prompts you for the output format.
 
to
 A string expression that lists the recipients whose names you want to put on the To line in the mail message.
 

 Separate the recipient 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 recipient names aren't recognized by the mail application, the message isn't sent.
 

 If you leave this argument blank, Microsoft Access prompts you for the recipients.
 
cc
 A string expression that lists the recipients whose names you want to put on the Cc line in the mail message. If you leave this argument blank, the Cc line in the mail message is blank.
 
bcc
 A string expression that lists the recipients whose names you want to put on the Bcc line in the mail message. If you leave this argument blank, the Bcc line in the mail message is blank.
 



(continued)

subject
 A string expression containing the text you want to put on the Subject line in the mail message. If you leave this argument blank, the Subject line in the mail message is blank.
 
messagetext
 A string expression containing the text you want to include in the body of the mail message, after the object. If you leave this argument blank, the object is all that's included in the body of the mail message.
 
editmessage
 Use True (1) to open the electronic mail application immediately with the message loaded, so the message can be edited. Use False (0) to send the message without editing it. If you leave this argument blank, the default (True) is assumed.
 
templatefile
 A string expression that's the full name, including the path, of the file you want to use as a template for an HTML file.
 


Remarks   Modules can be sent only in MS-DOS Text format, so if you specify acSendModule for the objecttype argument, you must specify acFormatTXT for the outputformat argument.

You can leave an optional argument blank in the middle of the syntax, but you must include the argument's comma. If you leave a trailing argument blank, don't use a comma following the last argument you specify.
See Also   OutputTo method.

Example

The following example includes the Employees table in a mail message in Microsoft Excel format and specifies To, Cc, and Subject lines in the mail message. The mail message is sent immediately, without editing.
0
 
LVL 28

Accepted Solution

by:
TextReport earned 1000 total points
ID: 8208487
For this I alsways use MAPI rather than Outlook. Paste the code into a new module then you can call the TextReport_SendMail functiojn with multiple filenames seperated by ;

Cheers, Andrew

Option Compare Database
Option Explicit

'------------------------------------------------------------------------
'
'    MAPI32.TXT -- MAPI32 API Declarations for Visual Basic
'
'              Copyright (C) 1994 Microsoft Corporation
'
'
'  This file contains only the Const, Type,
'  and Declare statements for  MAPI32 APIs.
'
'  You have a royalty-free right to use, modify, reproduce and distribute
'  this file (and/or any modified version) in any way you find useful,
'  provided that you agree that Microsoft has no warranty, obligation or
'  liability for its contents.
'
'------------------------------------------------------------------------

'***************************************************
'   MAPI Message holds information about a message
'***************************************************

Private Type MAPIMessage
    reserved As Long
    Subject As String
    NoteText As String
    MessageType As String
    DateReceived As String
    ConversationID As String
    Flags As Long
    RecipCount As Long
    FileCount As Long
End Type


'************************************************
'   MAPIRecip holds information about a message
'   originator or recipient
'************************************************

Private Type MAPIRecip
    reserved As Long
    RecipClass As Long
    Name As String
    Address As String
    EIDSize As Long
    EntryID As String
End Type


'******************************************************
'   MapiFile holds information about file attachments
'******************************************************

Private Type MAPIFile
    reserved As Long
    Flags As Long
    Position As Long
    PathName As String
    filename As String
    FileType As String
End Type


'***************************
'   FUNCTION Declarations
'***************************

Private Declare Function MAPILogon Lib "MAPI32.DLL" (ByVal UIParam&, ByVal User$, ByVal Password$, _
                                             ByVal Flags&, ByVal reserved&, Session& _
                                            ) As Long
Private Declare Function MAPILogoff Lib "MAPI32.DLL" (ByVal Session&, ByVal UIParam&, ByVal Flags&, ByVal reserved&) As Long
Private Declare Function BMAPIReadMail Lib "MAPI32.DLL" (lMsg&, nRecipients&, nFiles&, ByVal Session&, _
                                                 ByVal UIParam&, MessageID$, ByVal Flag&, ByVal reserved& _
                                                ) As Long
Private Declare Function BMAPIGetReadMail Lib "MAPI32.DLL" (ByVal lMsg&, Message As MAPIMessage, _
                                                    Recip() As MAPIRecip, File() As MAPIFile, _
                                                    Originator As MAPIRecip _
                                                   ) As Long
Private Declare Function MAPIFindNext Lib "MAPI32.DLL" Alias "BMAPIFindNext" (ByVal Session&, ByVal UIParam&, _
                                                                      MsgType$, SeedMsgID$, ByVal Flag&, _
                                                                      ByVal reserved&, MsgID$ _
                                                                     ) As Long
Private Declare Function MAPISendDocuments Lib "MAPI32.DLL" (ByVal UIParam&, ByVal DelimStr$, ByVal FilePaths$, _
                                                     ByVal FileNames$, ByVal reserved& _
                                                    ) As Long
Private Declare Function MAPIDeleteMail Lib "MAPI32.DLL" (ByVal Session&, ByVal UIParam&, ByVal MsgID$, ByVal Flags&, ByVal reserved&) As Long
Private Declare Function MAPISendMail Lib "MAPI32.DLL" Alias "BMAPISendMail" (ByVal Session&, ByVal UIParam&, Message As MAPIMessage, Recipient() As MAPIRecip, File() As MAPIFile, ByVal Flags&, ByVal reserved&) As Long
Private Declare Function MAPISaveMail Lib "MAPI32.DLL" Alias "BMAPISaveMail" (ByVal Session&, ByVal UIParam&, Message As MAPIMessage, Recipient() As MAPIRecip, File() As MAPIFile, ByVal Flags&, ByVal reserved&, MsgID$) As Long
Private Declare Function BMAPIAddress Lib "MAPI32.DLL" (lInfo&, ByVal Session&, ByVal UIParam&, Caption$, ByVal nEditFields&, Label$, nRecipients&, Recip() As MAPIRecip, ByVal Flags&, ByVal reserved&) As Long
Private Declare Function BMAPIGetAddress Lib "MAPI32.DLL" (ByVal lInfo&, ByVal nRecipients&, Recipients() As MAPIRecip) As Long
Private Declare Function MAPIDetails Lib "MAPI32.DLL" Alias "BMAPIDetails" (ByVal Session&, ByVal UIParam&, Recipient As MAPIRecip, ByVal Flags&, ByVal reserved&) As Long
Private Declare Function MAPIResolveName Lib "MAPI32.DLL" Alias "BMAPIResolveName" (ByVal Session&, ByVal UIParam&, ByVal username$, ByVal Flags&, ByVal reserved&, Recipient As MAPIRecip) As Long



'**************************
'   CONSTANT Declarations
'**************************
'

Const SUCCESS_SUCCESS = 0
Const MAPI_USER_ABORT = 1
Const MAPI_E_USER_ABORT = MAPI_USER_ABORT
Const MAPI_E_FAILURE = 2
Const MAPI_E_LOGIN_FAILURE = 3
Const MAPI_E_LOGON_FAILURE = MAPI_E_LOGIN_FAILURE
Const MAPI_E_DISK_FULL = 4
Const MAPI_E_INSUFFICIENT_MEMORY = 5
Const MAPI_E_BLK_TOO_SMALL = 6
Const MAPI_E_TOO_MANY_SESSIONS = 8
Const MAPI_E_TOO_MANY_FILES = 9
Const MAPI_E_TOO_MANY_RECIPIENTS = 10
Const MAPI_E_ATTACHMENT_NOT_FOUND = 11
Const MAPI_E_ATTACHMENT_OPEN_FAILURE = 12
Const MAPI_E_ATTACHMENT_WRITE_FAILURE = 13
Const MAPI_E_UNKNOWN_RECIPIENT = 14
Const MAPI_E_BAD_RECIPTYPE = 15
Const MAPI_E_NO_MESSAGES = 16
Const MAPI_E_INVALID_MESSAGE = 17
Const MAPI_E_TEXT_TOO_LARGE = 18
Const MAPI_E_INVALID_SESSION = 19
Const MAPI_E_TYPE_NOT_SUPPORTED = 20
Const MAPI_E_AMBIGUOUS_RECIPIENT = 21
Const MAPI_E_AMBIG_RECIP = MAPI_E_AMBIGUOUS_RECIPIENT
Const MAPI_E_MESSAGE_IN_USE = 22
Const MAPI_E_NETWORK_FAILURE = 23
Const MAPI_E_INVALID_EDITFIELDS = 24
Const MAPI_E_INVALID_RECIPS = 25
Const MAPI_E_NOT_SUPPORTED = 26

Const MAPI_ORIG = 0
Const MAPI_TO = 1
Const MAPI_CC = 2
Const MAPI_BCC = 3

Const MAPI_W_NO_SERVICE = 262659
Const MAPI_W_ERRORS_RETURNED = 263040
Const MAPI_W_POSITION_CHANGED = 263297
Const MAPI_W_APPROX_COUNT = 263298
Const MAPI_W_CANCEL_MESSAGE = 263552
Const MAPI_W_PARTIAL_COMPLETION = 263808

Const MAPI_E_INTERFACE_NOT_SUPPORTED = -2147467262
Const MAPI_E_CALL_FAILED = -2147467259
Const MAPI_E_NO_SUPPORT = -2147221246
Const MAPI_E_BAD_CHARWIDTH = -2147221245
Const MAPI_E_STRING_TOO_LONG = -2147221243
Const MAPI_E_UNKNOWN_FLAGS = -2147221242
Const MAPI_E_INVALID_ENTRYID = -2147221241
Const MAPI_E_INVALID_OBJECT = -2147221240
Const MAPI_E_OBJECT_CHANGED = -2147221239
Const MAPI_E_OBJECT_DELETED = -2147221238
Const MAPI_E_BUSY = -2147221237
Const MAPI_E_NOT_ENOUGH_DISK = -2147221235
Const MAPI_E_NOT_ENOUGH_RESOURCES = -2147221234
Const MAPI_E_NOT_FOUND = -2147221233
Const MAPI_E_VERSION = -2147221232
Const MAPI_E_LOGON_FAILED = -2147221231
Const MAPI_E_SESSION_LIMIT = -2147221230
Const MAPI_E_USER_CANCEL = -2147221229
Const MAPI_E_UNABLE_TO_ABORT = -2147221228
Const MAPI_E_NETWORK_ERROR = -2147221227
Const MAPI_E_DISK_ERROR = -2147221226
Const MAPI_E_TOO_COMPLEX = -2147221225
Const MAPI_E_BAD_COLUMN = -2147221224
Const MAPI_E_EXTENDED_ERROR = -2147221223
Const MAPI_E_COMPUTED = -2147221222
Const MAPI_E_CORRUPT_DATA = -2147221221
Const MAPI_E_UNCONFIGURED = -2147221220
Const MAPI_E_FAILONEPROVIDER = -2147221219
Const MAPI_E_UNKNOWN_CPID = -2147221218
Const MAPI_E_UNKNOWN_LCID = -2147221217
Const MAPI_E_PASSWORD_CHANGE_REQUIRED = -2147221216
Const MAPI_E_PASSWORD_EXPIRED = -2147221215
Const MAPI_E_INVALID_WORKSTATION_ACCOUNT = -2147221214
Const MAPI_E_INVALID_ACCESS_TIME = -2147221213
Const MAPI_E_ACCOUNT_DISABLED = -2147221212
Const MAPI_E_END_OF_SESSION = -2147220992
Const MAPI_E_UNKNOWN_ENTRYID = -2147220991
Const MAPI_E_MISSING_REQUIRED_COLUMN = -2147220990
Const MAPI_E_BAD_VALUE = -2147220735
Const MAPI_E_INVALID_TYPE = -2147220734
Const MAPI_E_TYPE_NO_SUPPORT = -2147220733
Const MAPI_E_UNEXPECTED_TYPE = -2147220732
Const MAPI_E_TOO_BIG = -2147220731
Const MAPI_E_DECLINE_COPY = -2147220730
Const MAPI_E_UNEXPECTED_ID = -2147220729
Const MAPI_E_UNABLE_TO_COMPLETE = -2147220480
Const MAPI_E_TIMEOUT = -2147220479
Const MAPI_E_TABLE_EMPTY = -2147220478
Const MAPI_E_TABLE_TOO_BIG = -2147220477
Const MAPI_E_INVALID_BOOKMARK = -2147220475
Const MAPI_E_WAIT = -2147220224
Const MAPI_E_CANCEL = -2147220223
Const MAPI_E_NOT_ME = -2147220222
Const MAPI_E_CORRUPT_STORE = -2147219968
Const MAPI_E_NOT_IN_QUEUE = -2147219967
Const MAPI_E_NO_SUPPRESS = -2147219966
Const MAPI_E_COLLISION = -2147219964
Const MAPI_E_NOT_INITIALIZED = -2147219963
Const MAPI_E_NON_STANDARD = -2147219962
Const MAPI_E_NO_RECIPIENTS = -2147219961
Const MAPI_E_SUBMITTED = -2147219960
Const MAPI_E_HAS_FOLDERS = -2147219959
Const MAPI_E_HAS_MESSAGES = -2147219958
Const MAPI_E_FOLDER_CYCLE = -2147219957
Const MAPI_E_AMBIGUOUS_RECIP = -2147219712
Const MAPI_E_INVALID_PARAMETER = -2147024809
Const MAPI_E_NOT_ENOUGH_MEMORY = -2147024882
Const MAPI_E_NO_ACCESS = -2147024891


'***********************
'   FLAG Declarations
'***********************

'* MAPILogon() flags *

Const MAPI_LOGON_UI = &H1
Const MAPI_NEW_SESSION = &H2
Const MAPI_FORCE_DOWNLOAD = &H1000

'* MAPILogoff() flags *

Const MAPI_LOGOFF_SHARED = &H1
Const MAPI_LOGOFF_UI = &H2

'* MAPISendMail() flags *

Const MAPI_DIALOG = &H8

'* MAPIFindNext() flags *

Const MAPI_UNREAD_ONLY = &H20
Const MAPI_GUARANTEE_FIFO = &H100

'* MAPIReadMail() flags *

Const MAPI_ENVELOPE_ONLY = &H40
Const MAPI_PEEK = &H80
Const MAPI_BODY_AS_FILE = &H200
Const MAPI_SUPPRESS_ATTACH = &H800

'* MAPIDetails() flags *

Const MAPI_AB_NOMODIFY = &H400

'* Attachment flags *

Const MAPI_OLE = &H1
Const MAPI_OLE_STATIC = &H2

'* MapiMessage flags *

Const MAPI_UNREAD = &H1
Const MAPI_RECEIPT_REQUESTED = &H2
Const MAPI_SENT = &H4

'*************************************************************
' FUNCTION NAME: TextReport_SendMail
'
' PURPOSE:
'   This is the front-end function to the MAPISendMail function. You
'   pass a semicolon-delimited list of To and CC recipients, a
'   subject, a message, and a delimited list of file attachments.
'   This function prepares MapiRecip and MapiFile structures with the
'   data parsed from the information provided using the ParseRecord
'   sub. Once the structures are prepared, the MAPISendMail function
'   is called to send the message.
'
' INPUT PARAMETERS:
'   sSubject: The text to appear in the subject line of the message
'   sTo:      Semicolon-delimited list of names to receive the
'             message
'   sCC:      Semicolon-delimited list of names to be CC'd

'   sAttach:  Semicolon-delimited list of files to attach to
'             the message
' RETURN
'   SUCCESS_SUCCESS if successful, or a MAPI error if not.

'*********************************************************** **
Function TextReport_SendMail(sSubject As String, sTo As String, sCC As String, sAttach As String, sMessage As String, Optional pbooSilent As Boolean)
Dim i, cTo, cCC, cAttach          ' variables holding counts
Dim booSilent As Boolean
Dim lngSendMail As Long
Dim strMsg As String

Dim MAPI_Message As MAPIMessage

    If IsMissing(pbooSilent) Then
       booSilent = False
    Else
       booSilent = pbooSilent
    End If
   
    ' Count the number of items in each piece of the mail message
    cTo = CountTokens(sTo, ";")
    cCC = CountTokens(sCC, ";")
    cAttach = CountTokens(sAttach, ";")

    ' Create arrays to store the semicolon delimited mailing
    ' .. information after it is parsed
    ReDim rTo(0 To cTo) As String
    ReDim rCC(0 To cCC) As String
    ReDim rAttach(0 To cAttach) As String
    ' Parse the semicolon delimited information into the arrays.
    ParseTokens rTo(), sTo, ";"
    ParseTokens rCC(), sCC, ";"
    ParseTokens rAttach(), sAttach, ";"

    ' Create the MAPI Recip structure to store all the To and CC
    ' .. information to be passed to the MAPISendMail function
    ReDim MAPI_Recip(0 To cTo + cCC - 1) As MAPIRecip


    ' Setup the "TO:" recipient structures
    For i = 0 To cTo - 1
        MAPI_Recip(i).Name = rTo(i)
        MAPI_Recip(i).RecipClass = MAPI_TO
    Next i

    ' Setup the "CC:" recipient structures
    For i = 0 To cCC - 1
        MAPI_Recip(cTo + i).Name = rCC(i)
        MAPI_Recip(cTo + i).RecipClass = MAPI_CC
    Next i
 
    ' Create the MAPI File structure to store all the file attachment
    ' .. information to be passed to the MAPISendMail function
    ReDim MAPI_File(0 To cAttach) As MAPIFile

    ' Setup the file attachment structures
    MAPI_Message.FileCount = cAttach
    For i = 0 To cAttach - 1
        MAPI_File(i).Position = -1
        MAPI_File(i).PathName = rAttach(i)
    Next i

    ' Set the mail message fields
    MAPI_Message.Subject = sSubject
    MAPI_Message.NoteText = sMessage
    MAPI_Message.RecipCount = cTo + cCC


    ' Send the mail message
   
    lngSendMail = MAPISendMail(0&, 0&, MAPI_Message, MAPI_Recip(), MAPI_File(), MAPI_LOGON_UI, 0&)
   
    If Not booSilent Then
       Select Case lngSendMail
           Case SUCCESS_SUCCESS
           Case MAPI_USER_ABORT: strMsg = "The User Aborted the Message"
           Case MAPI_E_FAILURE: strMsg = "MAPI Failure"
           Case MAPI_E_LOGIN_FAILURE: strMsg = "MAPI Login Failure"
           Case MAPI_E_DISK_FULL: strMsg = "Disk Full"
           Case MAPI_E_INSUFFICIENT_MEMORY: strMsg = "Insufficient Memory """
           Case MAPI_E_BLK_TOO_SMALL: strMsg = "MAPI Block Too Small"
           Case MAPI_E_TOO_MANY_SESSIONS: strMsg = "MAPI Too Many Sessions """
           Case MAPI_E_TOO_MANY_FILES: strMsg = "MAPI Too Many Files"
           Case MAPI_E_TOO_MANY_RECIPIENTS: strMsg = "MAPI Too Many Recipients """
           Case MAPI_E_ATTACHMENT_NOT_FOUND: strMsg = "MAPI Attachment Not Found"
           Case MAPI_E_ATTACHMENT_OPEN_FAILURE: strMsg = "MAPI Unable To Open Attachment"
           Case MAPI_E_ATTACHMENT_WRITE_FAILURE: strMsg = "MAPI Attachment Write Failure"
           Case MAPI_E_UNKNOWN_RECIPIENT: strMsg = "MAPI Unknown Recipient """
           Case MAPI_E_BAD_RECIPTYPE: strMsg = "MAPI Bad Recipient Type"
           Case MAPI_E_NO_MESSAGES: strMsg = "MAPI No Messages"
           Case MAPI_E_INVALID_MESSAGE: strMsg = "MAPI Invalid Message"
           Case MAPI_E_TEXT_TOO_LARGE: strMsg = "MAPI Text Too Large"
           Case MAPI_E_INVALID_SESSION: strMsg = "MAPI Invalid Session"
           Case MAPI_E_TYPE_NOT_SUPPORTED: strMsg = "MAPI Type Not Supported"
           Case MAPI_E_AMBIGUOUS_RECIPIENT: strMsg = "MAPI Ambiguous Recipient"
           Case MAPI_E_MESSAGE_IN_USE: strMsg = "MAPI Message In Use"
           Case MAPI_E_NETWORK_FAILURE: strMsg = "MAPI Network Failure"
           Case MAPI_E_INVALID_EDITFIELDS: strMsg = "MAPI Invalid EditFields"
           Case MAPI_E_INVALID_RECIPS: strMsg = "MAPI Invalid Recips"
           Case MAPI_E_NOT_SUPPORTED: strMsg = "MAPI Not Supported"
           
           Case MAPI_W_NO_SERVICE: strMsg = "MAPI_W_NO_SERVICE"
           Case MAPI_W_ERRORS_RETURNED: strMsg = "MAPI_W_ERRORS_RETURNED"
           Case MAPI_W_POSITION_CHANGED: strMsg = "MAPI_W_POSITION_CHANGED"
           Case MAPI_W_APPROX_COUNT: strMsg = "MAPI_W_APPROX_COUNT"
           Case MAPI_W_CANCEL_MESSAGE: strMsg = "MAPI_W_CANCEL_MESSAGE"
           Case MAPI_W_PARTIAL_COMPLETION: strMsg = "MAPI_W_PARTIAL_COMPLETION"
           
           Case MAPI_E_PASSWORD_EXPIRED: strMsg = "MAPI_E_PASSWORD_EXPIRED"
           Case MAPI_E_INVALID_WORKSTATION_ACCOUNT: strMsg = "MAPI_E_INVALID_WORKSTATION_ACCOUNT"
           Case MAPI_E_INVALID_ACCESS_TIME: strMsg = "MAPI_E_INVALID_ACCESS_TIME"
           Case MAPI_E_ACCOUNT_DISABLED: strMsg = "MAPI_E_ACCOUNT_DISABLED"
           Case MAPI_E_END_OF_SESSION: strMsg = "MAPI_E_END_OF_SESSION"
           Case MAPI_E_UNKNOWN_ENTRYID: strMsg = "MAPI_E_UNKNOWN_ENTRYID"
           Case MAPI_E_MISSING_REQUIRED_COLUMN: strMsg = "MAPI_E_MISSING_REQUIRED_COLUMN"
           Case MAPI_E_BAD_VALUE: strMsg = "MAPI_E_BAD_VALUE"
           Case MAPI_E_INVALID_TYPE: strMsg = "MAPI_E_INVALID_TYPE"
           Case MAPI_E_TYPE_NO_SUPPORT: strMsg = "MAPI_E_TYPE_NO_SUPPORT"
           Case MAPI_E_UNEXPECTED_TYPE: strMsg = "MAPI_E_UNEXPECTED_TYPE"
           Case MAPI_E_TOO_BIG: strMsg = "MAPI_E_TOO_BIG"
           Case MAPI_E_DECLINE_COPY: strMsg = "MAPI_E_DECLINE_COPY"
           Case MAPI_E_UNEXPECTED_ID: strMsg = "MAPI_E_UNEXPECTED_ID"
           Case MAPI_E_UNABLE_TO_COMPLETE: strMsg = "MAPI_E_UNABLE_TO_COMPLETE"
           Case MAPI_E_TIMEOUT: strMsg = "MAPI_E_TIMEOUT"
           Case MAPI_E_TABLE_EMPTY: strMsg = "MAPI_E_TABLE_EMPTY"
           Case MAPI_E_TABLE_TOO_BIG: strMsg = "MAPI_E_TABLE_TOO_BIG"
           Case MAPI_E_INVALID_BOOKMARK: strMsg = "MAPI_E_INVALID_BOOKMARK"
           Case MAPI_E_WAIT: strMsg = "MAPI_E_WAIT"
           Case MAPI_E_CANCEL: strMsg = "MAPI_E_CANCEL"
           Case MAPI_E_NOT_ME: strMsg = "MAPI_E_NOT_ME"
           Case MAPI_E_CORRUPT_STORE: strMsg = "MAPI_E_CORRUPT_STORE"
           Case MAPI_E_NOT_IN_QUEUE: strMsg = "MAPI_E_NOT_IN_QUEUE"
           Case MAPI_E_NO_SUPPRESS: strMsg = "MAPI_E_NO_SUPPRESS"
           Case MAPI_E_COLLISION: strMsg = "MAPI_E_COLLISION"
           Case MAPI_E_NOT_INITIALIZED: strMsg = "MAPI_E_NOT_INITIALIZED"
           Case MAPI_E_NON_STANDARD: strMsg = "MAPI_E_NON_STANDARD"
           Case MAPI_E_NO_RECIPIENTS: strMsg = "MAPI_E_NO_RECIPIENTS"
           Case MAPI_E_SUBMITTED: strMsg = "MAPI_E_SUBMITTED"
           Case MAPI_E_HAS_FOLDERS: strMsg = "MAPI_E_HAS_FOLDERS"
           Case MAPI_E_HAS_MESSAGES: strMsg = "MAPI_E_HAS_MESSAGES"
           Case MAPI_E_FOLDER_CYCLE: strMsg = "MAPI_E_FOLDER_CYCLE"
           Case MAPI_E_AMBIGUOUS_RECIP: strMsg = "MAPI_E_AMBIGUOUS_RECIP"
           Case MAPI_E_INVALID_PARAMETER: strMsg = "MAPI_E_INVALID_PARAMETER"
           Case MAPI_E_NOT_ENOUGH_MEMORY: strMsg = "MAPI_E_NOT_ENOUGH_MEMORY"
           Case MAPI_E_NO_ACCESS: strMsg = "MAPI_E_NO_ACCESS"
           Case Else
                strMsg = "Unknown Error"
       End Select
       If strMsg <> "" Then
          MsgBox "TextReport_SendMail Failed with the error " & strMsg & " (" & lngSendMail & ")", vbCritical
       End If
    End If
    TextReport_SendMail = lngSendMail
   End Function

'*************************************************************
   ' FUNCTION NAME: CountTokens
   '
   ' PURPOSE:
   '   Given a string of delimited items and the delimiter, the number
   '   of tokens in the string will be returned. This function is useful
   '   for dimensioning an array to store the delimited items prior to
   '   calling ParseTokens.
   '
   ' INPUT PARAMETERS:
   '   sSource: A delimited list of tokens
   '   sDelim:  The delimiter used to delimit sSource
   '
   ' RETURN
   '   The number of tokens in sSource, which is the number of delimiters
   '   plus 1. If sSource is empty, 0 is returned.

'*************************************************************
Private Function CountTokens(ByVal sSource As String, ByVal sDelim As String)
Dim iDelimPos As Integer
Dim iCount As Integer

   ' Number of tokens = 0 if the source string is empty
   If sSource = "" Then
      CountTokens = 0

   ' Otherwise number of tokens = number of delimiters + 1
   Else
      iDelimPos = InStr(1, sSource, sDelim)
      Do Until iDelimPos = 0
         iCount = iCount + 1
         iDelimPos = InStr(iDelimPos + 1, sSource, sDelim)
      Loop
      CountTokens = iCount + 1
   End If
End Function

'*************************************************************
' FUNCTION NAME: GetToken
'
' PURPOSE:
'   Given a string of delimited items, the first item will be
'   removed from the list and returned.
'
' INPUT PARAMETERS:
'   sSource: A delimited list of tokens
'   sDelim:  The delimiter used to delimit sSource
'
' RETURN
'   sSource will have the first token removed. The function
'   returns the token removed from sSource.

'*************************************************************
Private Function GetToken(sSource As String, ByVal sDelim As String) As String
Dim iDelimPos As Integer

   ' Find the first delimiter
   iDelimPos = InStr(1, sSource, sDelim)

   ' If no delimiter was found, return the existing string and set
   ' .. the source to an empty string.
   If (iDelimPos = 0) Then
      GetToken = Trim$(sSource)
      sSource = ""

   ' Otherwise, return everything to the left of the delimiter and
   ' .. return the source string with it removed.
   Else
      GetToken = Trim$(left$(sSource, iDelimPos - 1))
      sSource = Mid$(sSource, iDelimPos + 1)
   End If
End Function


'*************************************************************
' SUB NAME: ParseTokens
'
' PURPOSE:
'   Extracts information from a delimited list of items and places
'   it in an array.
'
' INPUT PARAMETERS:
'   Array(): A one-dimensional array of strings in which the parsed
'   tokens will be place
'   sTokens: A delimited list of tokens
'   sDelim:  The delimiter used to delimit sTokens
'
' RETURN
'   None
'*************************************************************
Private Sub ParseTokens(MyArray() As String, ByVal sTokens As String, ByVal sDelim As String)
Dim i As Integer
    For i = LBound(MyArray) To UBound(MyArray)
        MyArray(i) = GetToken(sTokens, sDelim)
    Next
End Sub

0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 27

Expert Comment

by:jjafferr
ID: 8208496
I have done a program that does what you want, and here is how:
1-Output your Report to C:\ (for example, I use it):

DoCmd.OutputTo acOutputReport, "YourReportName", acFormatXLS, "c:\YourExcelFile.xls", False


2-The following are Defined earlier
EmailAddress
olCC1
olCC2
SubjectText
MessageText
Add1


    Set Out = CreateObject("Outlook.Application")
    With Out.CreateItem(olMailItem)
           .Recipients.Add EmailAddress
           .Recipients.Add(cc1).Type = olCC1
           .Recipients.Add(cc2).Type = olCC2
           .Subject = SubjectText
           .Body = MessageText
           .Attachments.Add "c:\YourExcelFile.xls"
           .Attachments.Add Add1
           .Send
    End With

Some times we send a few attachments in one go, which is done in a if ..then

Hope it helps
0
 
LVL 1

Expert Comment

by:Joe_Cool
ID: 8215143
If he olny issue your trying to solve is the output to excel write a simple macro in Access using the send object command, specify your output type as excel and your done.
I may be over simplifying your issue if so disregard.
0
 
LVL 1

Expert Comment

by:EmilG
ID: 8218720
Hi pipo_tin,

I was successful adapting the code from Q209948-HOW TO: Use Automation to Send a Microsoft Outlook Message using Access 2000.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q209948

The code is as follows:

Sub SendMessage(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("Andrew Fuller")
      objOutlookRecip.Type = olCC

      ' Set the Subject, Body, and Importance of the message.
      .Subject = "This is an Automation test with Microsoft Outlook"
      .Body = "Last test - I promise." & 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
         If Not objOutlookRecip.Resolve Then
         objOutlookMsg.Display
      End If
      Next
      .Send

   End With
   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing
End Sub

Then type the following in the immediate window:

SendMessage "C:\My Documents\Customers.txt"

I hope this helps.

Emil Gray
0
 

Author Comment

by:pipo_tin
ID: 8219012
First, thanks to all of you. You've been reaaly helpful!

Higor, with the SebdObject command I keep getting "can't open mail session" error.

TextReport, your code works great. After I updated my Outlook, worked pretty good.

jjafferr, I'm affraid TextReport beat you by four minutes! I'm doing test with your recommendation, been having some results too.

EmilG, your code works great too. But opens the mail window and I need to skip that. Thanks anyway!

Thanks again.

Mario - pipo_tin at (no spam) yahoo
0
 

Author Comment

by:pipo_tin
ID: 8219025
Thank you. Been trying to solve the problem for days!

Mario
0
 

Expert Comment

by:Banath
ID: 8238601
TextReport, please collect points from EmilG at:
http://www.experts-exchange.com/Databases/MS_Access/Q_20568788.html

Banath
EE Moderator
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

770 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