Results 1 to 19 of 19

Thread: Some issue with this Outlook VBA.

  1. #1

    Thread Starter
    Member
    Join Date
    Jan 2012
    Posts
    41

    Some issue with this Outlook VBA.

    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.
    VBA Code:
    1. Function CheckText(CellToCheck As Range, KeyString As String) As Boolean
    2.     Dim CheckRange As Range, Hit As Range
    3.     Set Hit = CellToCheck.Find(what:=KeyString, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
    4.     CheckText = Not (Hit Is Nothing)
    5. End Function
    6.  
    7. Private Sub Application_NewMailEx(ByVal EntryIDCollection As String)
    8. Dim CheckRange As Range
    9. Dim KeyString As String
    10. vID = Split(EntryIDCollection, ",")
    11. Set XLApp1 = CreateObject("Excel.Application")
    12. Set CheckRange = XLApp1.Workbooks.Open("C:\Users\vijaywp\Desktop\India-IR-Schedule.xlsx").Sheets("Client Testing").Range("A1:S100")
    13. For i = 0 To UBound(vID)
    14. Set objMail = Application.Session.GetItemFromID(vID(i))
    15. vSubject = objMail.Subject
    16. vBody = objMail.Body
    17. vFrom = objMail.SenderEmailAddress
    18. VRtime = objMail.SentOn
    19. KeyString = Mid(Trim(Mid(vSubject, InStr(vSubject, "#") + 1)), 1, 3)
    20. If CheckText(CheckRange, KeyString) = True Then
    21.  
    22.    Set XLApp = CreateObject("Excel.Application")
    23.    Set xlWB = XLApp.Workbooks.Open("C:\Users\vijaywp\Desktop\sample.xlsx")
    24.    Set xlSheet = xlWB.Sheets("Sheet1")
    25.    vRow = xlSheet.Range("A" & XLApp.Rows.Count).End(-4162).Offset(1, 0).Row
    26.    xlSheet.Range("A" & vRow).Value = vSubject
    27.    xlSheet.Range("B" & vRow).Value = vBody
    28.    xlSheet.Range("C" & vRow).Value = VRtime
    29.    xlWB.Save
    30.    XLApp.Quit
    31.    Set objMail = Nothing
    32.    Set XLApp = Nothing
    33.  
    34. End If
    35. Next i
    36. XLApp1.DisplayAlerts = False
    37. XLApp1.Quit
    38. XLApp1.DisplayAlerts = True
    39. Set XLApp1 = Nothing
    40. End Sub

    Please help me on this one guys.

    Thanks in advance.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Some issue with this Outlook VBA.

    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

  3. #3

    Thread Starter
    Member
    Join Date
    Jan 2012
    Posts
    41

    Re: Some issue with this Outlook VBA.

    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?

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Some issue with this Outlook VBA.

    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

  5. #5

    Thread Starter
    Member
    Join Date
    Jan 2012
    Posts
    41

    Re: Some issue with this Outlook VBA.

    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.
    Attached Images Attached Images  
    Last edited by jobs123; Feb 22nd, 2012 at 04:19 AM.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Some issue with this Outlook VBA.

    what version of excel in the XP system?
    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

  7. #7

    Thread Starter
    Member
    Join Date
    Jan 2012
    Posts
    41

    Re: Some issue with this Outlook VBA.

    Excel 2007 only on all of the systems here.

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Some issue with this Outlook VBA.

    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
    vb Code:
    1. set wb = xlapp1.workbooks.open(filename)
    2. set sht = wb.sheets("client testing")
    3. 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

  9. #9

    Thread Starter
    Member
    Join Date
    Jan 2012
    Posts
    41

    Re: Some issue with this Outlook VBA.

    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?

  10. #10
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yodaâ„¢
    Posts
    60,710

    Re: Some issue with this Outlook VBA.

    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?
    VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    • Reps & Rating Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API Viewer utility • .NET API Viewer Utility •
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  11. #11

    Thread Starter
    Member
    Join Date
    Jan 2012
    Posts
    41

    Re: Some issue with this Outlook VBA.

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

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Some issue with this Outlook VBA.

    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

  13. #13

    Thread Starter
    Member
    Join Date
    Jan 2012
    Posts
    41

    Re: Some issue with this Outlook VBA.

    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.

  14. #14
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Some issue with this Outlook VBA.

    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

  15. #15

    Thread Starter
    Member
    Join Date
    Jan 2012
    Posts
    41

    Re: Some issue with this Outlook VBA.

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

  16. #16
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Some issue with this Outlook VBA.

    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

  17. #17

    Thread Starter
    Member
    Join Date
    Jan 2012
    Posts
    41

    Re: Some issue with this Outlook VBA.

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

  18. #18

    Thread Starter
    Member
    Join Date
    Jan 2012
    Posts
    41

    Re: Some issue with this Outlook VBA.

    Can anyone please help me on this?

  19. #19

    Thread Starter
    Member
    Join Date
    Jan 2012
    Posts
    41

    Re: Some issue with this Outlook VBA.

    Please help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width