+ Reply to Thread
Results 1 to 18 of 18

(Auto Exporting Emails to Excel) Script Only Runs Once; Possibly Related to Use of "End"

  1. #1
    Registered User
    Join Date
    08-15-2012
    Location
    Massachusetts, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    (Auto Exporting Emails to Excel) Script Only Runs Once; Possibly Related to Use of "End"

    I am trying to create some code in VBA that will automatically take all emails that enter an inbox and automatically move the emails and some meaningful information about them into an excel spreadsheet log. Each month's emails will be in a different sheet. I discovered I can use the Item Add event in the ThisOutlookSession module. The problem is this only works once, at which point I have to close out of vba and Outlook 2010 and reopen for it to work again. I think this is because at the end of
    Please Login or Register  to view this content.
    I am using End to exit the script as shown below:

    Please Login or Register  to view this content.
    I suspect End might be causing an inability for me to re-enter the ItemAdd event. The problem is if I don't use End my code doesn't work at all and I think it has something to do with the workbook, worksheet, wks, and wkb references displaying method sheets of object _global failed when I try and debug the code. I feel my two choices are to keep using End or find some other way to (assuming this is why End has been working) release all the memory that allows me to keep re-using the script OR figure out if the method sheets of object _global failed is related and what I need to do to fix this. My question is what seems to be causing these issues and as a new VBA rookie what do I explicitly need to do to fix it?

    Thank you for the help and here is most of my Code that is relevant

    Please Login or Register  to view this content.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: (Auto Exporting Emails to Excel) Script Only Runs Once; Possibly Related to Use of "En

    Get rid of End, it stops all code execution.

    Where are you getting the error when you don't use End?

    By the way, is all this code in Outlook?

    I ask because in Outlook VBA things like ActiveCell don't really mean anything.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    08-15-2012
    Location
    Massachusetts, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: (Auto Exporting Emails to Excel) Script Only Runs Once; Possibly Related to Use of "En

    Quote Originally Posted by Norie View Post
    Get rid of End, it stops all code execution.

    Where are you getting the error when you don't use End?

    By the way, is all this code in Outlook?

    I ask because in Outlook VBA things like ActiveCell don't really mean anything.
    All this code is in Outlook and is used to move emails into an Excel spreadsheet.
    When I step through the code in debug it doesn't show a pop up error, it will just immediately end Sub PostDataToExcel, then close, save, quit, clears variables, and end Sub olInboxItems_ItemAdd(ByVal Item As Object). When I step through the code it shows this happening after I run the line
    Please Login or Register  to view this content.
    from Sub CheckMonthlyLogExistence
    Please Login or Register  to view this content.
    Last edited by bacon1192; 06-16-2015 at 10:59 AM.

  4. #4
    Registered User
    Join Date
    08-15-2012
    Location
    Massachusetts, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: (Auto Exporting Emails to Excel) Script Only Runs Once; Possibly Related to Use of "En

    ..........
    Last edited by bacon1192; 06-16-2015 at 11:00 AM. Reason: Double Posted (Please delete this reply)

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: (Auto Exporting Emails to Excel) Script Only Runs Once; Possibly Related to Use of "En

    If this code is in Outlook Worksheets.Count doesn't really mean anything.

    Even if it was in Excel VBA you should have a workbook reference to tell VBA which workbook you want to count the sheets in.
    Please Login or Register  to view this content.
    PS Is this where you are getting the 'method sheets of object _global failed' error you mentioned?

  6. #6
    Registered User
    Join Date
    08-15-2012
    Location
    Massachusetts, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: (Auto Exporting Emails to Excel) Script Only Runs Once; Possibly Related to Use of "En

    Quote Originally Posted by Norie View Post
    If this code is in Outlook Worksheets.Count doesn't really mean anything.

    Even if it was in Excel VBA you should have a workbook reference to tell VBA which workbook you want to count the sheets in.
    Please Login or Register  to view this content.
    PS Is this where you are getting the 'method sheets of object _global failed' error you mentioned?

    I reference the workbook

    Please Login or Register  to view this content.
    'method sheets of object_global failed' shows up when I hover my mouse over the following to check the value being set during debug
    Please Login or Register  to view this content.
    Why doesn't Count mean anything? I am trying to use the VBA code to open the excel file and count the number of sheets. The script takes the date an email is sent, and assigns the sheet it should be on based on that date. If an email were sent today it would be assigned to "Jun2015", then the script checks if "June2015" exists as a sheet. If it does, it'll place the entry to that sheet and if not it will create the sheet so it can be placed there. The count lets us know the location of the last sheet.

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: (Auto Exporting Emails to Excel) Script Only Runs Once; Possibly Related to Use of "En

    You still haven't said if all this code is in Outlook.

    If it is then Worksheets.Count doesn't really mean anything, in Excel by default, because there's no workbook reference, Worksheets.Count would refer to worksheets in the active workbook.

    You do have a workbook reference for Sheets in this line of code, but you don't have a workbook reference fo Worksheets.Count.
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    08-15-2012
    Location
    Massachusetts, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: (Auto Exporting Emails to Excel) Script Only Runs Once; Possibly Related to Use of "En

    Apologies, I may have accidentally deleted part of my response; Yes this code is fully contained in Outlook.

    I have previously tried making
    Please Login or Register  to view this content.
    into
    Please Login or Register  to view this content.
    and also throughout but it did not seem to work, I can try again if this is the type of change you are talking about.

    Also, you mentioned that Worksheets.Count would refer to worksheets in the active workbook, yet the active workbook is the one I am trying to manipulate so I would think it would work. No?

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: (Auto Exporting Emails to Excel) Script Only Runs Once; Possibly Related to Use of "En

    No, because Outlook doesn't have workbooks, active or other.

    Whenever automating one application from another you should make sure everything is fully qualified, if you don't you'll get errors and/or unexpected results.

  10. #10
    Registered User
    Join Date
    08-15-2012
    Location
    Massachusetts, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: (Auto Exporting Emails to Excel) Script Only Runs Once; Possibly Related to Use of "En

    Quote Originally Posted by Norie View Post
    No, because Outlook doesn't have workbooks, active or other.

    Whenever automating one application from another you should make sure everything is fully qualified, if you don't you'll get errors and/or unexpected results.
    So is this useless based on what you're saying?
    Please Login or Register  to view this content.
    I've also noticed that before I run this line, as expected, I can hover my pointer over wkb and it will show it '= nothing' but once this line runs, no bubble pops up... I always expected the name of the workbook 'OutlookItems.xlsx' to show up in the bubble

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: (Auto Exporting Emails to Excel) Script Only Runs Once; Possibly Related to Use of "En

    No, because you are referring to the instance of Excel you opened/created earlier and that does have an ActiveWorkbook.

    Mind you there's no need to use ActiveWorkbook, instead set wbk to reference the workbook you are opening, when you open it.
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    08-15-2012
    Location
    Massachusetts, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: (Auto Exporting Emails to Excel) Script Only Runs Once; Possibly Related to Use of "En

    Thank you for the help, I have applied these changes but am still having trouble running the line:
    Please Login or Register  to view this content.
    It is fine the first time the script runs through but afterwards it exits the sub immediately after running the line and doesn't show nextrow as being set to anything. The only work around for this was using the End statement.

    Even when I try to put in a reference for the workbook it will not work. Such as
    Please Login or Register  to view this content.
    Last edited by bacon1192; 06-22-2015 at 09:23 AM.

  13. #13
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: (Auto Exporting Emails to Excel) Script Only Runs Once; Possibly Related to Use of "En

    There's no workbook reference for Worksheets(wksSheetName).Range("A:A").

  14. #14
    Registered User
    Join Date
    08-15-2012
    Location
    Massachusetts, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: (Auto Exporting Emails to Excel) Script Only Runs Once; Possibly Related to Use of "En

    Quote Originally Posted by Norie View Post
    There's no workbook reference for Worksheets(wksSheetName).Range("A:A").
    No matter what I try and add to make the reference, I can't seem to make it work. Perhaps because of my rudimentary skill level I am missing something that could be obvious to you. Would you teach me how you'd change that line?

  15. #15
    Registered User
    Join Date
    08-15-2012
    Location
    Massachusetts, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: (Auto Exporting Emails to Excel) Script Only Runs Once; Possibly Related to Use of "En

    Quote Originally Posted by bacon1192 View Post
    No matter what I try and add to make the reference, I can't seem to make it work. Perhaps because of my rudimentary skill level I am missing something that could be obvious to you. Would you teach me how you'd change that line?
    I think I've been able to move past this by using
    Please Login or Register  to view this content.

  16. #16
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: (Auto Exporting Emails to Excel) Script Only Runs Once; Possibly Related to Use of "En

    I was going to suggest that but I wasn't sure if wks referred to the same worksheet as Worksheets(wksSheetName).

  17. #17
    Forum Contributor
    Join Date
    08-27-2006
    Posts
    136

    Re: (Auto Exporting Emails to Excel) Script Only Runs Once; Possibly Related to Use of "En

    Quote Originally Posted by bacon1192 View Post
    When I step through the code in debug it doesn't show a pop up error
    Comment out the first two instances of

    Please Login or Register  to view this content.
    When you copy code in the future consider commenting out all instances of the above not followed closely by

    Please Login or Register  to view this content.
    Use On Error Resume Next and On Error GoTo 0 with a purpose, not to bypass random errors.
    To mark "Solved" go to Thread Tools.

  18. #18
    Registered User
    Join Date
    08-15-2012
    Location
    Massachusetts, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: (Auto Exporting Emails to Excel) Script Only Runs Once; Possibly Related to Use of "En

    Quote Originally Posted by skatonni View Post
    Comment out the first two instances of

    Please Login or Register  to view this content.
    When you copy code in the future consider commenting out all instances of the above not followed closely by

    Please Login or Register  to view this content.
    Use On Error Resume Next and On Error GoTo 0 with a purpose, not to bypass random errors.
    This is used to bypass a known error with the code that is indicative of whether or not a worksheet already exists.

    Thank you guys for the help, I think I've been able to move past some of these issues with your assistance.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Script not copying data from "Emails" sheet to "New Sheet" - Run time error: Object
    By methuselah90 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-08-2014, 03:22 PM
  2. [SOLVED] Possibly Easy: Making an "Other" with Countifs; "Not this, this, or this"
    By nscarritt in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-28-2014, 11:44 AM
  3. [SOLVED] Script to save to folder in "v:\dept2", confirm file saved, then delete from "v:\dept1"
    By ks100 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-28-2014, 11:42 AM
  4. Loop to populate graph -- Excel "runs out of memory"?
    By Dante_Blake in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-20-2011, 01:55 PM
  5. Macro that runs entered value through "low" and "high" range
    By Vika.F in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-14-2005, 03:35 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1