bfuchs
asked on
vba reading outlook error
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:
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..
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
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(
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..
ASKER
Hi,
That didnt work, file name dont exsists...It concatenates the single quotes as part of the name.
That didnt work, file name dont exsists...It concatenates the single quotes as part of the name.
Try:
Set Msg = OL.CreateItemFromTemplate( "F:\Mail\" & chr(34) s & chr(34))
Set Msg = OL.CreateItemFromTemplate(
sorry
Set Msg = OL.CreateItemFromTemplate( "F:\Mail\" & chr(34) & s & chr(34))
Set Msg = OL.CreateItemFromTemplate(
ASKER
same issue
Post the names of 3 files that works and 3 that doesn't.
Also send the code for the insertEmails function
ASKER
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
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
Also send the code for the insertEmails function
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
Also have you run a debug on your code to find out exactly where it breaks
ASKER
Hi,
the code breaks in that line Set Msg = OL.CreateItemFromTemplate( "F:\Mail\" & s)
The undeliverable is causing the issue.
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
the code breaks in that line Set Msg = OL.CreateItemFromTemplate(
The undeliverable is causing the issue.
The variable S is getting set to something that is not registering as a string.
I notice that you first set it as:
But before you loop again you just set is as:
I am assuming that should be the same as the first.
Add this line before your line that errors:
And see if that gives you any clues as to what is happening.
I notice that you first set it as:
s = Dir("F:\Mail\")
But before you loop again you just set is as:
s = Dir
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
And see if that gives you any clues as to what is happening.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Robberbaron,
I changed to the way you suggested and the problem is not happening, any explanation?
I changed to the way you suggested and the problem is not happening, any explanation?
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.
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
.
ASKER
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..
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..
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to all participants!
Set Msg = OL.CreateItemFromTemplate(