Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding Outlook attachment - can't find this file (XP Office 2003)

    This one is driving me crazy!! I've got a little bit of VBA behind a command button on an Access form. Click it and what should happen is that a Shell command creates a zip file, and then Outlook is invoked to attach the zip file and send it out. Seems easy enough! However, what happens is that the '.Attachments.Add' statement in the Outlook code consistently gives the error "Can't find this file" - so you hit 'Debug' then PF5, and voila! all is well, the file was there all along. I've even now got WaitForSingleObject and a infinite loop for FileSystemObject.FileExists in front to make sure the file exists - yet still the Outlook.Attachments.Add statement fails. Here is the code:

    process_id = Shell(WinZipStart & strPassword & " " & strZipFile & " " & strSourceFile, vbHide)
    process_handle = OpenProcess(Synchronize, 0, process_id)
    If process_handle <> 0 Then
    WaitForSingleObject process_handle, INFINITE
    CloseHandle process_handle
    End If

    Do
    Loop Until fs.FileExists(strZipFile)

    Set objOutlook = GetObject(, "Outlook.application")
    If TypeName(objOutlook) = "Empty" Then Set objOutlook = CreateObject("Outlook.application")
    Set objEmail = objOutlook.CreateItem(olMailItem)
    With objEmail
    .To = strFullAddress
    .Subject = "Incident " & strRNum & "/" & strINum & " | " & strDate & " | " & strIncName & SRRtext
    .Body = strMessage
    .Attachments.Add strZipFile <<-- error occurs here
    .Send
    End With

    Everything works as expected, EXCEPT that the 'can't find this file' error pops up, but then magically disappears when in Debug mode!

    Does anyone have any ideas why this might be?

    TIA

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

    Re: Adding Outlook attachment - can't find this file (XP Office 2003)

    Hard to say, but I'd add DoEvents to the loop, to give other programs a chance:

    Do
    DoEvents
    Loop Until fs.FileExists(strZipFile)

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Outlook attachment - can't find this file (XP Office 2003)

    Thanks, Hans! In tests so far, it's working, although the Winzip invocation is occasionally failing on a password error - but that's a different matter. Still don't understand why the WaitForSingleEvent didn't delay things enough though. But thanks. Much appreciated.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Outlook attachment - can't find this file (XP Office 2003)

    Later..... Actually, Hans, it only works sometimes, but more often not! Very strange! However, I have devised a loop that DOES seem to work, and it is :

    Do
    objMail.Attachments.Add strZipFile
    Loop Until objMail.Attachments.Count > 0

    so far, in tests, it hasn't failed yet. Keep your fingers crossed! Thanks for your input though.

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

    Re: Adding Outlook attachment - can't find this file (XP Office 2003)

    Are you using an error handler, or suppressing errors? Otherwise, I'd expect the code to break at

    objMail.Attachments.Add strZipFile

    if the file isn't available.

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Outlook attachment - can't find this file (XP Office 2003)

    Yes, sorry, I did also code :

    On Error Resume Next

    Only way to do it!

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

    Re: Adding Outlook attachment - can't find this file (XP Office 2003)

    Thanks for clearing that up!

Posting Permissions

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