Re: [AccessDevelopers] E-mail with spreadsheet attachment

Tue, 09 Aug 2005 13:23:40 -0700

Title: E-mail with spreadsheet attachment
Tom, (or others)
 
Is there an easy way to send a report using this CDO method?
 
TIA,
Toby
----- Original Message -----
From: Tom Oakes
Sent: Tuesday, August 09, 2005 11:05 AM
Subject: RE: [AccessDevelopers] E-mail with spreadsheet attachment

I have a client that doesn't have Outlook accounts available on the application server, so we resorted to using the CDO library to send email.  You can essentially do everything you can with Outlook - except edit the email prior to sending.  The CDO library does not raise any type of security warning.
 
My routine looks like this:
(the bolded parts are system option functions - you can replace these with literal strings)
 
'***** code start *****'
Public Function SendCDOMail(strTo As String, strSubject As String, strBody As String, strAttach As String, blnHTML As Boolean) As Boolean
On Error GoTo Failure
 
        Dim objMessage As CDO.Message
        Dim objConfig As CDO.Configuration
        Dim objFields As Variant
       
        Const cboSendUsingPort As Integer = 2
       
        Set objConfig = New CDO.Configuration
        Set objMessage = New CDO.Message
        Set objFields = objConfig.Fields
       
        With objFields
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = GetSMTPServer()  '<-- valid SMTP server
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
            .Update
        End With
       
        With objMessage
            Set .Configuration = objConfig
            .TO = strTo
            .From = GetNTUSER & "@" & GetMailDomain()  '<-- automatically uses current user as "From"
            .Subject = strSubject
            If blnHTML = True Then
                .HTMLBody = strBody
            Else
                .TextBody = strBody
            End If
            If strAttach <> "" Then .AddAttachment (strAttach)
            .Send
        End With
      
       Set objMessage = Nothing
       Set objFields = Nothing
       Set objConfig = Nothing
     
      SendCDOMail = True
     
EXITROUTINE:
    Exit Function
Failure:
    SendCDOMail = False
    MsgBox Err & " " & Err.Description
    Resume EXITROUTINE
End Function
'***** code end *****'
 

Tom Oakes
Personal PC Consultants, Inc.
[EMAIL PROTECTED]
503.230.0911 (O)
402.968.6946 (C)
734.264.0911 (F)



From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Eric Douglas
Sent: Tuesday, August 09, 2005 10:42 AM
To: [email protected]
Subject: RE: [AccessDevelopers] E-mail with spreadsheet attachment

I'll look at that - but I actually don't have Outlook send the message.  Instead, I always set the "Edit Before Sending" option to True, so the user has to press Send to send the e-mail.  It still requires user input, but is nicer than that dialog box.
 
Thanks for the link, I've wondered if there is a good solution to this issue.

From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Lava Boggavarapu
Sent: Tuesday, August 09, 2005 10:40 AM
To: [email protected]
Subject: RE: [AccessDevelopers] E-mail with spreadsheet attachment

When you send an email from Access, Outlook displays a warning informing you that an outside application is trying to send an email and if you want to continue etc.etc..

 

If you don’t want to see this message, wrap your email in a safe email and then send it. Check out Outlook Redemption at http://www.dimastr.com/redemption/.

 

 

Lava

 

------------------------------------------------

Lava Boggavarapu

Lava Trading Inc.

95 Morton Street, 6th Floor

New York, NY 10014

Tel 212.519.9371

Fax 212.519.9283

Mobile 732.668.2935

www.lavatrading.com

-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Eric Douglas
Sent: Tuesday, August 09, 2005 1:34 PM
To: [email protected]
Subject: RE: [AccessDevelopers] E-mail with spreadsheet attachment

 

One thing we do here is to:

 

1) create a temp directory

2) output the query as an Excel spreadsheet, and save it in the temp directory

3) Attach it to an e-mail

 

'put this in the form code:

Const Base_path As String = "C:\Temp123456789"

'put this behind the button to perform the action

    MkDir Base_path

'we have this in its own module

    'Create the OUTLOOK OBJ
    Set myOLApp = CreateObject("Outlook.Application")
    Set myitem = myOLApp.CreateItem(olMailItem)
    'Add Information to the EMAIL
    With myitem
        .To = "" 'To: field here
        .Subject = "" 'Subject of e-mail here
        .Body = "" 'Body of e-mail here
                DoCmd.OutputTo acReport, "YourReportName", acFormatXLS, Base_path & "\FileoutputName.xls", False, "", 0
                .Attachments.Add Base_path & "\FileoutputName.xls"
        .Save  'Puts them in the Draft Box - I think you can do .Send here as well
    End With
    'Clear the OBJ out
    Set myOLApp = Nothing
    Set myitem = Nothing
    RmDir base_path

 

Hope that helps.  There is some extra stuff I took out - error-checking, and where we set the variables for the To, Subject, Body and report name, but I know you can do that stuff easily.


Eric

 

 

From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Hoffman, Tobi K (DYS)
Sent: Tuesday, August 09, 2005 10:24 AM
To: [email protected]
Subject: [AccessDevelopers] E-mail with spreadsheet attachment

I've been searching the archive (and BTW, the search procedure has been much improved since the last time I did this!) to find if anybody ever addressed the issue of sending an e-mail with an attachment via e-mail.  I have generated a spreadsheet to export data, and the client wants it to be sent automatically via e-mail to the central office.  The DoCmd.SendObject method allows you to send an Access object, such as a table or query, but what I need to do is send the spreadsheet that I have just created.  I could send the whole list of queries that went into the spreadsheet, but I'd prefer to do it as a single e-mail with the spreadsheet attached.

Tobi Hoffman
Dept. of Youth Services
Boston, MA




Please zip all files prior to uploading to Files section.



SPONSORED LINKS
Microsoft access database Microsoft access developer Microsoft access
Microsoft access database design Microsoft access database training

YAHOO! GROUPS LINKS



Reply via email to