item moved or deleted error message when sending email from vba in access

sardel
sardel used Ask the Experts™
on
I have looked at the answers to this question and attempted to use the code, but I still get the error message appearing in Word after the email has been sent.  Please could someone look at my code and see where I am going wrong.  It is a very annoying message, and I do not want to roll out the database to colleagues until this is resolved.

Thanks.   Here is the code.


Function msgSendEmail_Attachment(ReportID As String, strSubject As String, strMessageBody As String, strAttachment As String) As Boolean

Dim dbs As Database
Set dbs = CurrentDb

Dim sqlRecipients, strDistributionList, strAttach      As String
Dim rsConf, RSPath     As Recordset
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Dim sPDFPath As String

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)


sPDFPath = "select OutputFolder from tblReports where ReportID = " & ReportID
Set RSPath = dbs.OpenRecordset(sPDFPath, dbOpenDynaset, dbSeeChanges)
strDistributionList = ""

sPDFPath = RSPath!OutputFolder

strAttach = sPDFPath & strAttachment & " (as at " & Format(DATE, "dd mmm yyyy") & ").zip"

sqlRecipients = "Select person, userid from tblDistribution where ReportID = " & ReportID

Set rsConf = dbs.OpenRecordset(sqlRecipients, dbOpenDynaset, dbSeeChanges)

With rsConf
 .MoveFirst
Do Until rsConf.EOF = True
    strDistributionList = strDistributionList & " " & !UserID & ";"
    .MoveNext
Loop

End With

strDistributionList = Left(strDistributionList, Len(strDistributionList) - 1)

         Set appOutLook = GetObject(, "Outlook.Application")
            If Err <> 0 Then 'Outlook isn't running
                'So fire it up
                Set appOutLook = CreateObject("Outlook.Application")
                bStarted = True
            End If
        'Open a new e-mail message
        Set MailOutLook = appOutLook.CreateItem(olMailItem)
        With MailOutLook 'and add the detail to it
 
         
            'Create the recipients TO
                .To = strDistributionList
            'Set the message SUBJECT
                .Subject = strSubject
           'Set the message BODY
                .Body = strMessageBody
            'set attachment
            If Left(strAttach, 1) = "\" Then
                .Attachments.Add (strAttach)
            End If
           .Send 'No return value since the message will remain in the outbox if it fails to send
 
          End With

   
Set MailOutLook = Nothing
Set appOutLook = Nothing

Set rsConf = Nothing
Set dbs = Nothing

End Function
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
At the start of your sub you have the two lines:

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

A bit further down you have:

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

This duplication DOes not seem related to your issue .. I simply mention it in passing.

Can you supply the code which calls msgSendEmail_Attachment as teh function itself looks ok at first sight and I presume you can call it ok and perhaps it is aftyer the call that it falls over?

Chris
Top Expert 2009

Commented:
Here is a better way of dealing with Outlook instances -- use an error handler to create a new instance of Outlook only if Outlook is not running:
Dim appOutlook As Outlook.Application

   Set appOutlook = GetObject(, "Outlook.Application")

   'Your code here

ErrorHandlerExit:
   Set appOutlook = Nothing
   Exit Sub

ErrorHandler:
   'Outlook is not running; open Outlook with CreateObject
   If Err.Number = 429 Then
      Set appOutlook = CreateObject("Outlook.Application")
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number _
         & " in *** procedure" _
         & "; Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

Open in new window

Top Expert 2009

Commented:
Where is your code running?  From a Word template a UserForm, or what?  Can you post a screen shot of the error message?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
I agree with Helen regarding trying GetObject and then CreateObject if Outlook is not already running.  I also generally add a boolean variable bOutlookWasOpen, set it to true initially, and then change it to false in the error handler.

Then, in the segment titled "ErrorHandlerExit", I add a line to quit Outlook if it was not open when you called this routine.

Dale
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
Helen / Fyed

For your information, as I suggested the setting of appOutLook would not seem to be related to the root problem identified hence my request for more information on the calling environment.

It is also not necessary in the case of outlook to use the get/create check since outlook is a single instance application and the createobject maps onto the outlook instance if it exists and only if it does not does it create an instance.  Hence it is more efficient to simply skip the getobject method in the case of Outlook.

Chris

Author

Commented:
Hi and thanks for all your suggestions.  Chris here is the code that calls the function:
Private Sub cmdDistributionList_Click()
On Error GoTo Err_cmdDistributionList_Click

Run msgSendEmail_Attachment(5, "Monthly Report ", "Please find attached the current Monthly report ", "test1")

Exit_cmdDistributionList_Click:
    Exit Sub

Err_cmdDistributionList_Click:
    If Err.Number = 2517 Then
        Exit Sub
    Else
    MsgBox Err.Description
    End If
    Resume Exit_cmdDistributionList_Click

End Sub

Author

Commented:
Helen
My code is running from within MS Access.  I have Word set up as my default editor for Outlook 2003.  Hope that helps

Author

Commented:
Helen

I just tried your code and it still comes up with the error message. :-(
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
What is the error message, and what line does it fail on?
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
Still cannot see an obvious cause ... for a test replace .send with .display and do you still get the error message?

Chris

Author

Commented:
It happens a few minutes after the email has been sent.  The error message is The Item has been moved or deleted.  

Chris I have tried running the function with .display instead of .send and it still sends the email.  Is that what it is meant to do?  I have noticed that when I run this function in the immediate window, the return value is FALSE.  Could that have something to do with it, as it is asking for a boolean answer?

Author

Commented:
here is the screen shot of the error.  Word opens and has this dialog box (even though I have not opened Word before this occurrence, I assume that it is opened because it is the default editor for Outlook)


errormessage.JPG
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
Nope with display it does not send the file ... But if I understand the error is triggered always a few minutes after the sub is called.

1. Monitor for at least that long without calling the sub and see if it triggered.
2. Call the sub twice in succession ... what happens i.e. how many error reports and timing.

Chris

Author

Commented:
I have tried calling the function twice and it gives a message for each instance called
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
Can you search through your code for any other references to outlook as something somewhere must be trying to do something and if the timing is so regular it must be some kind of timed event or action you are taking as a follow up to the send, (Display).

Chris
Commented:
I think I have found the solution.  It has nothing to do with vba, but instead it is the setting of HTML in Outlook combined with using Word as the default editor.  When I change the 'compose message in HTML format' to 'compose message in Rich Text Format', the error does not appear.  Thanks for all your help!
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
I would still imagine there is something at the root of the problem but given the difficulties of long distance diagnosis i'm just happy you seem to have a way forward.

CHris

Author

Commented:
thanks for your persistence Chris. It is greatly appreciated.
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
I suggest you close the thread referring to your post at 26458541 as the answer - I think it will be useful for future reference.

Chris

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial