Some issue with this Outlook VBA.

[jobs123]

Hello All,

I have got a VBA script in outlook which checks for the subject of the mail if it contains some text and then checks whether or not that text is present in an already exisitng excel sheet.If it is present then it will update another excel sheet with the subject,body.Ann if that text is not present in that sheet then it will not upadte that another sheet.

But what is happening is it is updating that new sheet if that text is not present also in the exisiting excel sheet which is taking from the subject.And one more thing is that this script is running only on my system,not working on any other system.
I am attaching the VBA code for reference.

Function CheckText(CellToCheck As Range, KeyString As String) As Boolean
    Dim CheckRange As Range, Hit As Range
    Set Hit = CellToCheck.Find(what:=KeyString, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
    CheckText = Not (Hit Is Nothing)
End Function
 
Private Sub Application_NewMailEx(ByVal EntryIDCollection As String)
Dim CheckRange As Range
Dim KeyString As String
vID = Split(EntryIDCollection, ",")
Set XLApp1 = CreateObject("Excel.Application")
Set CheckRange = XLApp1.Workbooks.Open("C:\Users\vijaywp\Desktop\India-IR-Schedule.xlsx").Sheets("Client Testing").Range("A1:S100")
For i = 0 To UBound(vID)
Set objMail = Application.Session.GetItemFromID(vID(i))
vSubject = objMail.Subject
vBody = objMail.Body
vFrom = objMail.SenderEmailAddress
VRtime = objMail.SentOn
KeyString = Mid(Trim(Mid(vSubject, InStr(vSubject, "#") + 1)), 1, 3)
If CheckText(CheckRange, KeyString) = True Then
 
   Set XLApp = CreateObject("Excel.Application")
   Set xlWB = XLApp.Workbooks.Open("C:\Users\vijaywp\Desktop\sample.xlsx")
   Set xlSheet = xlWB.Sheets("Sheet1")
   vRow = xlSheet.Range("A" & XLApp.Rows.Count).End(-4162).Offset(1, 0).Row
   xlSheet.Range("A" & vRow).Value = vSubject
   xlSheet.Range("B" & vRow).Value = vBody
   xlSheet.Range("C" & vRow).Value = VRtime
   xlWB.Save
   XLApp.Quit
   Set objMail = Nothing
   Set XLApp = Nothing
 
End If
Next i
XLApp1.DisplayAlerts = False
XLApp1.Quit
XLApp1.DisplayAlerts = True
Set XLApp1 = Nothing
End Sub

Please help me on this one guys.

Thanks in advance.

[westconn1]

i can only assume that your checktext function is not working as expected

as you are using hardcoded paths to the excel files you should not expect them to be found on any other system

if any other problem is occurring you have not given enough information

[jobs123]

The excel files are present in one standard location so every user can access them.

It is sometimes updating the new sheet with some other emails also and sometimes not.
Not understanding what can be the issue?

[westconn1]

The excel files are present in one standard location so every user can access them.

not according to the code posted

It is sometimes updating the new sheet with some other emails also and sometimes not.
Not understanding what can be the issue?

as i said above the problem is likely to be the checktext function, as it is not possible to test your code we can only guess

[jobs123]

Hello westconn1,
I am getting an error when running this VBA on XP.I attached the scrrenshot.Please have a look and let me know what it can be.

The error is coming on this line "Set CheckRange = XLApp1.Workbooks.Open("C:\Users\vijaywp\Desktop\sample2.xlsx").Sheets("Sheet1").Range("A1:S100")"

This VBA is now working fine on other systems with Windows 7.
Thanks.

[westconn1]

what version of excel in the XP system?

[jobs123]

Excel 2007 only on all of the systems here.

[westconn1]

Excel 2007 only on all of the systems here.

check if any service pack available for office on this XP system

try breaking up the line into 3

set wb = xlapp1.workbooks.open(filename)
set sht = wb.sheets("client testing")
set checkrange = sht.range("a1:s100")

see if the error occurs on the first line or elsewhere. put the proper filename, i was not able to read it clearly

you should be getting an error on xlapp1.displayalerts = true as it is after quitting the application object

[jobs123]

Hey it is not showing any error now after i made the changes which u mentioned but it is not updating the excel sheet also.Now what to do?

[RobDog888]

Try breaking down your code and test for it running each section of code to help isolate the issue.

Try placing a breakpoint at the beginning and stepping through your code checking values of variables and the spreadsheet along the way.

Also are all emails in the same Store?

[jobs123]

Can you please tell me how can I break the code more now?
Thanks.

[westconn1]

Set Hit = CellToCheck.Find(what:=KeyString, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)

you should test if the xl constants you are using in the checktext function are valid, if you are only using latebinding they may have no values (empty), in which case you need to declare them or use literal values
xlvalues = -4163
xlwhole = 1

[jobs123]

Hey Westconn1 I checked the XL constants.
Getting xlvalues=-4163 and xlwhole=1 as you told on windows 7.But on windows XP this VBA is not even getting triggered.The newmailex event is not getting triggered i guess when a new mail is coming.
What can be done about this?
Please help.
Thanks.

[westconn1]

this is an outlook specific issue, you will need to look at why this event does not fire and test within outlook, as i do not use outlook i can be of little assistance here

[jobs123]

Okay Is there any way that i can convert this VBA to VBScript so that i can then test in on XP systems?

[westconn1]

Okay Is there any way that i can convert this VBA to VBScript so that i can then test in on XP systems?

probably not too hard, but what advantage do you think you will get?

as far as i can see you need to test the events within outlook on the xp system
i had assumed that the code was all written in outlook anyway, so i would be testing on the xp system to workout why the new mail event is not firing

i asked about excel versions before, but how about outlook versions

[jobs123]

Outllok 2007 is the version on all.
Not understanding what should I do now to make this VBA work

[jobs123]

Can anyone please help me on this?

[jobs123]

Please help