Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Attachments.Add (XL97:SR2)

    I'm having an issue with the following code. The code should attach the activeworkbook to the Outlook Email message. In most cases the activeworkbook will not have been saved and I do not particularly want to save it.

    Sub NewEmailCode()
    Dim objOLook As New Outlook.Application
    Dim objOMail As MailItem
    Dim i As Long

    oFile = ActiveWorkbook.Name

    Set objOLook = New Outlook.Application
    Set objOMail = objOLook.CreateItem(olMailItem)

    With objOMail
    .To = "Recipient"
    .Subject = "Your Subject Here "
    .Body = "File is attached."
    .Attachments.Add "" & oFile & ""

    .Display
    ' .Send
    End With
    Set objOMail = Nothing
    Set objOLook = Nothing
    End Sub

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Attachments.Add (XL97:SR2)

    I think that in order to send an attachment via Outlook automation you need to save the file first. You caould save the file as a temporay measure before executing oFile = ActiveWorkbook.Name and then kill the file after Set objOLook = Nothing.

    Alternatively use

    ActiveWorkbook.SendMail Recipients:="Recipient", Subject:="subject",

    but that does not allow (as far as I know) the inclusion of body text. Another possibilty is to use

    Application.Dialogs(xlDialogSendMail).Show

    and fill in the details interactively.

    Andrew C

  3. #3
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Attachments.Add (XL97:SR2)

    AFAIK, the file indicated as the attachment must be "on disk". Trying to attach
    a file named "Book1" for example should return an error if that file is not on disk.

    You may have to save the file, attach & send, the delete the file. Not too much
    trouble.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Attachments.Add (XL97:SR2)

    Kevin,

    You are correct...the file must be saved prior to attaching. I modified the code to save a file to C:Temp and then put in a kill statement to delete it.

    I thought that there was an equivalent to Activeworkbook.SendMail which does allow one to send the active workbook without having to save it first.

    Thanks,
    John

  5. #5
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Attachments.Add (XL97:SR2)

    I think activeworkbook.sendmail and the Word counterpart activedocument.sendmail actually create a temp file
    on disk which is then attached to the mail message object.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Attachments.Add (XL97:SR2)

    How were you able to delete a file that was still open, or did you also close it first?
    Legare Coleman

Posting Permissions

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