Solved

Using a For/Next loop to scan email inbox, every other record is skipped if the delete option is set

Posted on 2007-12-05
3
362 Views
Last Modified: 2013-12-05
First of all, apologies for the cryptic title! I couldn't really think of a way to put it concisely!

So, I have an Access ADP project (which many of you will feel you know intimately now!!) and one of its functions is to scan an email inbox looking for email from certain recipients, it then calls a stored procedure that inserts the data it finds into the SQL 2000 database.

This procedure works very well but I have the option on the form that calls the routine to delete the email when it has been imported into the database. When this is checked the routine only scans 50% of the emails (see the code snippet to see the line that does this) so I can kind of see what it is doing (when it looks for "next itm" the last one has been deleted so it gets confused I guess) but haven't a clue how to resolve it.

The function that does it is very long but the relevant bits (I guess) are: (I have omitted declarations etc)

Has anyone else come across a similar problem or any suggestions as to the solution?

Thanks.
Set appOutlook = GetObject(, "Outlook.Application")
Set nms = appOutlook.GetNamespace("MAPI")
 
If SelectFolder = True Then
    Set fld = nms.PickFolder
end if
 
lngItemCount = fld.Items.Count
 
For Each itm In fld.Items
    If itm.Class = 43 Then '43 is the constant olMail
            Set Safeitm = CreateObject("Redemption.SafeMailItem")
            Safeitm.Item = itm
                For Each att In Safeitm.Attachments
                    att.SaveAsFile strFilename
                
                    Set stm = New ADODB.Stream
                    With stm
                        .Type = adTypeBinary
                        .Open
                        .LoadFromFile strFilename
                    End With
                Next att
 
'THE BIT THAT OPENS THE WORD ATTACHMENT, EXTRACTS THE DATA AND CALLS THE STORED PROCEDURE
 
    end if
If Me.chkDeleteEmail True Then Safeitm.Delete
Set Safeitm = Nothing
next itm

Open in new window

0
Comment
Question by:pauldonson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 20413462
I ran into something similar when dealing with deleting from recordsets in the past.

Essentially the pointer gets "lost" when you delete the current record.

Maybe if you do something like build a recordset of the ones you imported and then go back and do a delete as a separate loop.

That was the way I ended up solving the problem.

0
 
LVL 6

Accepted Solution

by:
CRJ2000 earned 500 total points
ID: 20503647
You need to loop through the items backwards...

lngItemCount = fld.Items.Count
 
For count = lngItemCount to 1 step -1
  set itm = fld.items(Count)
  ... add your code here...
next

Another option, if you are going to delete all of the items in the folder, then you could simply look at the first item each time... this would only work properly if you are going to delete every item.

For count = 1 to lngItemCount
  set itm = fld.items(1)
  ... add your code here...
  ...Delete The Item Here...
next
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question