RE: Excel File to Email

Don't assume that these excel sheets are very transportable. I'm pretty darn
sure that the Excel query (it is not an AS400 query stored in excel btw,
duh)
is stored on the users disk and is not a part of the sheet itself, and the
autoupdate function uses ODBC, and a specific driver name, so that if you
did send it to another user, they would have to have the same ODBC driver
name in order to update the spreadsheet, and that's assuming no query.
Now then, if you were to update it locally (using auto-open?) and have full
Adobe Acrobat and were to use Adobe's "convert to PDF and email"
function..............that might have possibilities..............

Dale


-----Original Message-----
From: Bill [mailto:brobins3d@subdimension.com]
Sent: Thursday, August 15, 2002 3:21 PM
To: midrange-l@midrange.com
Subject: Re: Excel File to Email


> > Do you know that an Excel spreadsheet can contain a Query Definition
and
> > can be programmed to automatically refresh the data downloaded and then
> > email itself out?
>
> Do tell?  Show me.

I assume you want the email portion.  Here is some basic code.

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

'
'   sndWithOE - Mail through the Default Mail Program (Outlook Express)
'
Sub sndWithOE(strSubject As String, strFileName As String)
    Dim wbkNewWorkbook As Workbook

    Workbooks.Open FileName:=strFileName
    Set wbkNewWorkbook = ActiveWorkbook
    ActiveWorkbook.SendMail Recipients:=getToEmails, Subject:=strSubject,
returnreceipt:=False
    ActiveWorkbook.Close

End Sub

'
'   sndWithOL - Mail through Outlook Object
'
Sub sndWithOL(strSubject As String, strExcelName As String, strFileName As
String)

    Dim olkApp As Outlook.Application
    Dim olkNameSpace As Outlook.NameSpace
    Dim olkOutBox As Outlook.MAPIFolder
    Dim olkMailItem As Outlook.MailItem

    Set olkApp = CreateObject("Outlook.Application")
    Set olkNameSpace = olkApp.GetNamespace("Mapi")
    Set olkOutBox = olkNameSpace.GetDefaultFolder(olFolderOutbox)
    Set olkMailItem = olkApp.CreateItem(olMailItem)

    With olkMailItem
        .To = getToEmails
        .Subject = strSubject
        .Attachments.Add strExcelName, olByValue, , strFileName
        .DeleteAfterSubmit = True
        .Send
    End With

    Set olkMailItem = Nothing
    Set olkOutBox = Nothing
    Set olkNameSpace = Nothing
    Set olkApp = Nothing

End Sub

'
' getToEmails - The addresses are stored on the spreadsheet.
'

Function getToEmails() As String
    Dim rngEmails As Range

    getToEmails = ""
    For Each rngEmails In
ThisWorkbook.Worksheets("shtProcess").Range("D3..D32")
        If rngEmails.Value > " " Then
            getToEmails = getToEmails & ";" & rngEmails.Value
        End If
    Next rngEmails
    getToEmails = Mid(getToEmails, 2) ' Drop the first semi-colon

End Function

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

Bill

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.