Solved

Convert incoming emails to text files - have code but it's not working

Posted on 2010-11-25
19
372 Views
Last Modified: 2012-08-14
Hi

I borrowed some code from another issue on here but it isn't working properly and I don't know why.

It should create a text file for every email coming in to the Inbox. It does create the text files, but doesn't always fire. For example, I just sent 6 mails to the test account, and only 4 were converted. I repeated the excercise and again only 4 were converted, although one of those was one that wasn't converted previously, and one that was previously converted wasn't on the second attempt. I hope that makes sense! Basically, it appears to be random.

My latest test was to wait for 5 minutes and then send one email. It did not convert. It might be important to point out that I am forwarding mails to the test account, not composing new ones, as this is the way in which we will be using it.

I am very experiened with VBA in Excel but this is my first attempt at Outlook.
Code is attached.
General - Declarations:



Private WithEvents objInboxItems As Items





Private Sub Application_Startup()

    Dim objNS As NameSpace

    Set objNS = Application.GetNamespace("MAPI")

    ' instantiate Items collections for folders we want to monitor

    Set objInboxItems = objNS.GetDefaultFolder(olFolderInbox).Items

    Set objNS = Nothing

End Sub





Private Sub Application_Quit()

    ' disassociate global objects declared WithEvents

    Set objInboxItems = Nothing

End Sub





Private Sub objInboxItems_ItemAdd(ByVal Item As Object)

    Dim olItems As Items, _

        olItem As Object, _

        olMailItem As MailItem, _

        objFSO As New FileSystemObject, _

        objMsgFile As TextStream

    Set olItems = objInboxItems.Restrict("[Unread] = True")

    For Each olItem In olItems

        If olItem.Class = olMail Then

            Set olMailItem = olItem

            Set objMsgFile = objFSO.CreateTextFile("C:\LicenceEmails\" & Replace(Date, "/", "-") & "@" & Int(Timer) & ".txt")

            objMsgFile.WriteLine "Subject: " & olMailItem.Subject

            If olMailItem.BodyFormat = olFormatHTML Then

                objMsgFile.WriteLine olMailItem.HTMLBody

            Else

                objMsgFile.WriteLine olMailItem.Body

            End If

            objMsgFile.Close

            olMailItem.UnRead = False

        End If

    Next

End Sub

Open in new window

0
Comment
Question by:davidcowler
  • 10
  • 9
19 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 34214113
Hi, davidcowler.

Try this approach.  As I mentioned on Twitter (I'm TechnicLee there) the approach your code is using can be a problem if messages arrive in groups.  The approach I've taken below avoids that problem.  Replace you existing code with this and let's see what happens.
Private Sub Application_NewMailEx(ByVal EntryIDCollection As String)
    Dim arrEID As Variant, varEID As Variant, olkItm As Object, objFSO As New FileSystemObject, objMsgFile As TextStream
    On Error Resume Next
    arrEID = Split(EntryIDCollection, ",")
    For Each varEID In arrEID
        Set olkItm = Session.GetItemFromID(varEID)
        If olkItm.Class = olMail Then
            Set objMsgFile = objFSO.CreateTextFile("C:\LicenceEmails\" & Replace(Date, "/", "-") & "@" & Int(Timer) & ".txt")
            objMsgFile.WriteLine "Subject: " & olkItm.Subject
            If olkItm.BodyFormat = olFormatHTML Then
                objMsgFile.WriteLine olkItm.HTMLBody
            Else
                objMsgFile.WriteLine olkItm.Body
            End If
            objMsgFile.Close
            olkItm.UnRead = False
        End If
    Next
    On Error GoTo 0
    Set olkItm = Nothing
    Set objMsgFile = Nothing
    Set objFSO = Nothing
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:davidcowler
ID: 34217500
Hi

Have removed all the existing code and replaced with the above but having sent 6 emails in quick succession, only the first one was converted. Is there something else I need to do?

Thanks
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34217543
You received all 6 but only 1 was written to the file.  Correct?  That's very strange.  Are the items all mail items?  
0
 
LVL 1

Author Comment

by:davidcowler
ID: 34217731
Yes, that is correct. 6 in, 1 out. Seems to be the first received mail that goes through. All the mails are exactly the same format but different in content.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34217755
Are you familiar with debugging in VBA?  If so, set a breakpoint at line #5.  Test the value of arrEID.  It should contain an entry for each message received.  If it does, then step through the code and see what's happening.  Also, do you have any rules that affect the items?  If so, then disable those rules.
0
 
LVL 1

Author Comment

by:davidcowler
ID: 34217839
I write VBA for Excel a lot, so I'm familiar with the IDE. There are no rules in operation, so I'll set the breakpoint and see what happens. Thanks.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34217980
Also, test olkItm after line #6 executes and make sure it's not empty.
0
 
LVL 1

Author Comment

by:davidcowler
ID: 34218710
Have checked it all and it seems to be populating the variables and objects correctly, but the For loop seems to only run through once. I checked ubound(arrEID) and it's always 0.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34235451
What about the value of EntryIDCollection?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 1

Author Comment

by:davidcowler
ID: 34239690
Just sent a test mail and the value was 00000000111D8B41BF956B489E05DF0368CBF7B107001CF7980CC3958C448506897D9CFD5D320000005085AC00001CF7980CC3958C448506897D9CFD5D3200000050CB2F0000

Does this seem correct?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34239830
Yes.  If multiple items arrive at once, then you should see a string that looks like that for each item.  Each ID should be separated by a comma.
0
 
LVL 1

Author Comment

by:davidcowler
ID: 34240276
Would it not give an ID per mail? I added a msgbox to display the value but it gives a result similar to what I posted before, with no commas. This obviously halts the code and only the one file gets through. Without the msgbox only some get through. Is it to do with whether the messages are read or unread? Should any of the settings be changed?
0
 
LVL 1

Author Comment

by:davidcowler
ID: 34240420
oh wait, I see how that works now.... so there WOULD be multiple IDs in a single string, and the code marks the message as read once it has been dealt with.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34240529
Correct.
0
 
LVL 1

Author Comment

by:davidcowler
ID: 34240557
We're on MS Exchange here - would that have a bearing on the problem at all?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34240747
No.
0
 
LVL 1

Accepted Solution

by:
davidcowler earned 0 total points
ID: 34413228
New solution: abandon automated email clever trickery and resort to a webpage with a form and make the whole process semi-automatic. Not ideal but there you go.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34419382
Sorry the automated solution didn't work.
0
 
LVL 1

Author Closing Comment

by:davidcowler
ID: 34428932
Solution not found, so using a completely different method to achieve the desired result.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Granting full access permission allows users to access mailboxes present in their database. By giving full access permission one can open and read the content of any mailbox but cannot send emails from that mailbox.
Set OWA language and time zone in Exchange for individuals, all users or per database.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now