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.
VBA Code:
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))
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
check if any service pack available for office on this XP system
try breaking up the line into 3
vb Code:
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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.
Last edited by jobs123; Feb 24th, 2012 at 01:04 AM.
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete