?
Solved

vba reading outlook error

Posted on 2014-09-17
18
Medium Priority
?
438 Views
Last Modified: 2014-09-29
Hi Experts,

I have a database of email addresses that are being used for mass email listings.
Now I also have a module that is used for cleaning up invalid email addresses.

the way it works is that I save all bounced emails in a specific folder and then I run that module, that reads every file under that folder and extract the email address from within the text of the file, and then moves that email from the email address field to the Invalid email address field.

below is the code:
s = Dir("F:\Mail\")
While Not s = ""

Set OL = New Outlook.Application
Set Msg = OL.CreateItemFromTemplate("F:\Mail\" & s)

insertEmails (Msg.Body)

Set OL = Nothing
Set Msg = Nothing
s = Dir
Wend

Open in new window


The issue I am encountering now is that the following line of code gets stuck when trying to open certain files, I guess its because of the name..not sure.
"Set Msg = OL.CreateItemFromTemplate("F:\Mail\" & s)"

the error message states "Type mismatch".

for example  the following file name gave me that error "Undeliverable  Change in Requirements   .msg"
while the following file was accepted "Delivery Status Notification (Failure) (151).msg"

any idea how can I solve this issue?

meanwhile I run it with "on error resume next", however indeed I need the program to work for those files as well..
0
Comment
Question by:bfuchs
[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
  • 7
  • 7
  • 3
  • +1
18 Comments
 
LVL 16

Expert Comment

by:Sheils
ID: 40329866
It may have to do with the space at the end or use of numerical values. To make sure that the computer reads it as a string try modifying your code as follows:-

Set Msg = OL.CreateItemFromTemplate("F:\Mail\'" & s & "'")
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40331331
Hi,
That didnt work, file name dont exsists...It concatenates the single quotes as part of the name.
0
 
LVL 16

Expert Comment

by:Sheils
ID: 40331372
Try:

Set Msg = OL.CreateItemFromTemplate("F:\Mail\" & chr(34) s & chr(34))
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 16

Expert Comment

by:Sheils
ID: 40331373
sorry

Set Msg = OL.CreateItemFromTemplate("F:\Mail\" & chr(34) & s & chr(34))
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40331432
same issue
0
 
LVL 16

Expert Comment

by:Sheils
ID: 40332077
Post the names of 3 files that works and 3 that doesn't.
0
 
LVL 16

Expert Comment

by:Sheils
ID: 40332087
Also send the code for the insertEmails function
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40335563
Hi Sheils,

Further testing shows that this really has nothing to do with the name of file, (As i tried to rename the file and continue to get the type mismatch error), I am assuming that there these may be a different type of file, although they all have the same icon, file extension  and are all being opened by Outlook editor...

I will attach 2 files, one that is working and one that not, maybe you can spot the difference..

Thanks
Ben
Delivery-Status-Notification--Failure---
Undeliverable--Change-in-Requirements.ms
0
 
LVL 16

Expert Comment

by:Sheils
ID: 40335609
Also send the code for the insertEmails function
0
 
LVL 16

Expert Comment

by:Sheils
ID: 40335619
Your two files one have extention and the otherone don't. I am not sure which one works as one is undeliverable and the other is failure.

Also have you run a debug on your code to find out exactly where it breaks
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40337907
Hi,
Public Function insertEmails(s As String)
    Dim db As Database, rs As Recordset, s1 As String, s2 As String, i As Long, i2 As Long
    Set db = CurrentDb
    Set rs = db.OpenRecordset("BadEmails")
    i2 = Len(s)
    For i = 1 To i2 - 1
        s1 = s1 & Mid(s, i, 1)
        'Debug.Print s1
        If Mid(s, i + 1, 1) = " " Or i + 1 = i2 Or Asc(Mid(s, i + 1, 1)) = 13 Then
            If InStr(1, s1, "@") > 0 Then
                rs.AddNew
                If InStr(1, s1, "<") > 0 Then
                rs("BadEmail") = getemail(s1, "<>")
                ElseIf InStr(1, s1, "(") > 0 Then
                rs("BadEmail") = getemail(s1, "()")
                Else
                rs("BadEmail") = s1
                End If
                rs.Update
            End If
            s1 = ""
            i = i + 1
        End If
    
    Next
    rs.Close
    db.Close
    
End Function

Open in new window


the code breaks in that line Set Msg = OL.CreateItemFromTemplate("F:\Mail\" & s)

The undeliverable is causing the issue.
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40349150
The variable S is getting set to something that is not registering as a string.

I notice that you first set it as:
s = Dir("F:\Mail\")

Open in new window


But before you loop again you just set is as:
s = Dir

Open in new window


I am assuming that should be the same as the first.

Add this line before your line that errors:
Debug.Print TypeName(s)
Debug.print s

Open in new window


And see if that gives you any clues as to what is happening.
0
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 2000 total points
ID: 40349282
the Dir statements are correct.

i have reordered the code somewhat to give a better idea of where and what error may occur and reduced the creation of objects. also used variables
Sub Test1()
    Dim fldr As String, msgfilename As String, sTmp As String
    fldr = "F:\Mail\"
    
    Dim OL As Object, Msg As Object
    
    Set OL = New Outlook.Application
    
    msgfilename = Dir(fldr)   'get first filename in folder
    

    Do While Not msgfilename = ""

        s = fldr & msgfilename
        Debug.Print ">>" & s & "<"
        On Error Resume Next
        Set Msg = OL.CreateItemFromTemplate(s)
        
        If Err Then Debug.Print Err.Description
        
        insertEmails (Msg.Body)

        Set Msg = Nothing
        msgfilename = Dir   'get next file
    Loop
    
    Set OL = Nothing
End Sub

Open in new window

to ensure types are ok and make easier to change if needed.

delete the dim statements if using in vbs.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40351096
Hi Robberbaron,
I changed to the way you suggested and the problem is not happening, any explanation?
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 40351355
1. i moved the outlook object out of loop so only created once. and then reused
2. i forced the creation of the filename to happen outside the template object creation, into a variable that is passed to the Outlook function this should not have had any effect but makes it easier to detect any issues with the file name itself.

3. are you sure it didnt just skip over the error as i had put an On Error Resume there.  Check the Immediate window for details.
Sub Test2()
    Dim fldr As String, msgfilename As String, sTmp As String
    fldr = "F:\Mail\"
    
    Dim OL As Object, Msg As Object
    
    Set OL = New Outlook.Application
    
    msgfilename = Dir(fldr)   'get first filename in folder
    

    Do While Not msgfilename = ""

        s = fldr & msgfilename
        Debug.Print ">>" & s & "<"
        On Error Resume Next
        Set Msg = OL.CreateItemFromTemplate(s)
        
        If Err Then 
            Debug.Print Err.Description
	    MsgBox "error @ msg >>" & s & "<<"
        else
        	insertEmails (Msg.Body)
	End if

        Set Msg = Nothing
        msgfilename = Dir   'get next file
    Loop
    
    Set OL = Nothing
End Sub

Open in new window

.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40351408
Yes, I realized that on error statement and commented out and the error didn't occur,
As a matter of fact, I looked at the old version and saw which file is causing the error message, then on the new version I inserted a breakpoint in the case that file is being processed, and it stopped there, then continued w/o an issue..
I guess you unintentionally solved the problem, just for curiosity I would like to know what did the trick..
0
 
LVL 32

Assisted Solution

by:Robberbaron (robr)
Robberbaron (robr) earned 2000 total points
ID: 40351447
most likely not recreating the outlook object each time.  it would chew up memory as it sometimes takes a few seconds to complete garbage collection.
0
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 40351452
Thanks to all participants!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This article describes how to import Lotus Notes Contacts into Outlook 2016, 2013, 2010 and 2007 etc. with a few manual steps. You can easily export and migrate Lotus Notes contacts into Microsoft Outlook without having to use any third party tools.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Suggested Courses

752 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