Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    attach a file, csv file email (access 97)

    I am trying to email a csv file straight from an access query. Using the send object it only allows txt or xls format. I can however create a file on the hdd with the csv extension. How can I either
    a) send a csv file with a specified name direct form a query
    [img]/forums/images/smilies/cool.gif[/img] automatically attach a file from the hdd to an email
    I enclose the code I am working withDoCmd.TransferText acExportDelim, , "SendTXTData", "C:baeCalibration" & Format(Date, "ddmmyy") & ".csv", True
    DoCmd.TransferText acImportDelim, "BAECalibration", "Calibration" & Format Date, "ddmmyy"), "C:baeCalibration" & Format(Date, "ddmmyy") & ".csv"
    DoCmd.SendObject acSendTable, "Calibration" & Format(Date, "ddmmyy"), acFormatXLS, "david@thechameleongroup.co.uk", , , "Calibration Data " & Date, , False
    DoCmd.DeleteObject acTable, "Calibration" & Format(Date, "ddmmyy")

    Any new directions would be appreciated

    Thank you

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: attach a file, csv file email (access 97)

    I use the following function to send a file by e-mail using Outlook.
    You need to add a reference to the Outlook nn type library (where nn is the version you use). You can do this in any module by selecting Tools/References...

    The function takes 4 string arguments:
    Recipient is a list of e-mail adresses separated by semicolons ;
    Subject will become the subject of the e-mail
    Message will become the body of the e-mail
    Attachment is the full path and file name of the file you want to attach.

    The function returns True if it succeeds, False if it fails.

    Function SendMail( _
    Recipient As String, _
    Subject As String, _
    Message As String, _
    Attachment As String) As Boolean

    Dim objOL As Outlook.Application
    Dim objMI As Outlook.MailItem
    Dim blnNotActive As Boolean
    Dim intPos1 As Integer, intPos2 As Integer

    SysCmd acSysCmdSetStatus, "A moment please. Your e-mail message is being sent."
    DoCmd.Hourglass True

    ' Check whether Outlook is active
    On Error Resume Next
    Set objOL = GetObject(, "Outlook.Application")
    blnNotActive = (Err <> 0)

    If blnNotActive Then
    ' If not, we start Outlook
    Err.Clear
    Set objOL = CreateObject("Outlook.Application")
    End If

    On Error GoTo Err_Mail

    ' Create e-mail message
    Set objMI = objOL.CreateItem(olMailItem)
    With objMI
    intPos2 = 1
    intPos1 = InStr(intPos2, Recipient, ";")
    Do While intPos1 > 0
    .Recipients.Add Mid$(Recipient, intPos2, intPos1 - intPos2)
    intPos2 = intPos1 + 1
    intPos1 = InStr(intPos2, Recipient, ";")
    Loop
    .Recipients.Add Mid$(Recipient, intPos2)
    .Subject = Subject
    .Body = Message
    .Attachments.Add Attachment, olByValue
    .Send
    End With
    SendMail = True

    Exit_Mail:
    ' Release object memory
    On Error Resume Next
    If Not (objMI Is Nothing) Then objMI.Close olDiscard
    Set objMI = Nothing
    If blnNotActive And Not (objOL Is Nothing) Then objOL.Quit
    Set objOL = Nothing
    SysCmd acSysCmdClearStatus
    DoCmd.Hourglass False
    Exit Function

    Err_Mail:
    SendMail = False
    Resume Exit_Mail
    End Function

  3. #3
    New Lounger
    Join Date
    Feb 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: attach a file, csv file email (access 97)

    Dear Hans
    Thank you for your prompt reply.
    I have entered the code and tested it -it works but a couple of things I cannot figure out
    1. blnNotActive = (Err <> 0) I changed to blnNotActive = (Err.Number <> 0) or blnNotActive = (Err.Number = 0) as what is "<&lt " ?? This checks to see if outlook is open (Version 9) If it is it will send the email then close outlook. If it is not open then it will not send the mail if (Err.Number = 0).
    I would like for Outlook to be open all the time and not be closed after sending the email, but if it not open then it should still send the email.
    Sorry to appear thick but I do appreciate you help
    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: attach a file, csv file email (access 97)

    Hello David,

    It is OK to replace Err by Err.Number, but not essential. Number is the default property of the Err object, so just writing Err has the same result as Err.Number.

    If you get an ampersand ( & ) in the code copied from my post, the browser has become confused over the use of less-than ( < ) and greater-than ( > ) signs; HTML also uses these to delimit tags. I will attach a small picture of the line as it shows in my Access window. You can also replace it by

    blnNotActive = (objOL Is Nothing)

    which has the same effect.

    As it stands, the code checks whether Outlook is active.
    If so, then the e-mail is sent and Outlook is left open.
    If not, then Outlook is started, the e-mail is sent and Outlook gets closed.

    If you would like Outlook to remain open in all cases, all you need to do is remove the line

    If blnNotActive And Not (objOL Is Nothing) Then objOL.Quit

    (or make it into a comment)

    I hope this is clear enough. If not, holler.

    Regards,
    Hans
    Attached Images Attached Images

  5. #5
    New Lounger
    Join Date
    Feb 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: attach a file, csv file email (access 97)

    Hello Hans

    What can I say but thanks - you are a bit of a whizz.
    All seems to be well so far, have changed the code to read blnNotActive = (objOL Is Nothing) and remarked that other line.

    Thanks for your time

    regards

    David <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •