Visual Basic Doesn't Recognize Pathway

Question

My VBA code, copied from http://www.techrepublic.com/blog/microsoft-office/quickly-export-outlook-e-mail-items-to-excel/, will open the excel sheet but also says the strPath doesn't exist. It is supposed to autopopulate with some information from the emails but nothing is inputted. I have used both of these paths (the reason is beneath them).

    strSheet = "Job.xlsx"
    strPath = "C:\Users\user\Documents\"
    strSheet = strPath & strSheet

    strSheet = "Job.xlsx"
    strPath = "C:\Users\user\Desktop\"
    strSheet = strPath & strSheet

The location (from right-click, then selecting properties) is:
C:\Users\user\Desktop .... This opens up the sheet, but nothing is autopopulated

If I open up the sheet, and go to options -> save, then the default location is: C:\Users\user\Documents .... This doesn't open up the sheet

My questions:

Why is the pathway not recognized?
And why is the auto-population not working?

I assume the second one is intrinsically tied to the first question but if it isn't, I verbatim copied the code from the URL in the first line and plan on customizing after getting a working prototype.

UPDATED BELOW

I used the F8 button and discovered that the code reaches the error at this point right here:

            For Each itm In fld.Items
                intColumnCounter = 1
                    Set msg = itm

                    intRowCounter = intRowCounter + 1
                        Set rng = wks.Cells(intRowCounter, inColumnCounter)

The basic code for this section is here:

          For Each itm In fld.Items
                intColumnCounter = 1
                    Set msg = itm

                    intRowCounter = intRowCounter + 1
                        Set rng = wks.Cells(intRowCounter, inColumnCounter)
                        rng.Value = msg.To

                    intColumnCounter = intColumnCounter + 1
                        Set rng = wks.Cells(intRowCounter, intColumnCounter)
                        rng.Value = msg.SenderEmailAddress

                    intColumnCounter = intColumnCounter + 1
                        Set rng = wks.Cells(intRowCounter, intColumnCounter)
                        rng.Value = msg.Subject

Try to run the code step by step (F8) to spot the problem. Then update your question with a much circunscribed problem. — CMArg, May 26 '17 at 16:47
Is that *literally* the path you used, or did you remove your actual username? Have you tried using just (eg) `C:\Mail\` or something simpler? — Tim Williams, May 26 '17 at 16:47
@Tim Williams, that was from an old question that I figured out. I didn't see it when I submitted it. And to answer your other question, I tried to go straight to the main 'C:\' but I do not have that permission. — , May 26 '17 at 17:04
@CMArg, I used the f8 and added more code where the break ends up occurring. Thanks for that tip! — , May 26 '17 at 17:17
Do you need `PickFolder`? `PickFolder` is useful if the user might want to select a different Outlook folder each time the macro is run but is a pain if you always want to use Inbox. I am trying to find the time to write an introduction to Outlook. You might find these useful: [Introduction Part 3: Stores and all their folders](https://stackoverflow.com/documentation/outlook-vba/8874/introduction-part-3-stores-and-all-their-folders#t=201705262205519821678) and [How to copy Outlook mail message into excel using VBA or Macros](https://stackoverflow.com/a/12146315/973283) — Tony Dallimore, May 26 '17 at 22:17
@TonyDallimore That was awesome. I just copied it on over (added in my email address and the correct PathName) and it worked, kinda. Only one email was copied over. I am going to continue to go over it and tinker around. Just thought I would give you some props while I still remembered. Thanks! — , Jun 01 '17 at 19:08

CMArg · Answer 1 · 2017-05-26T18:43:54.747

Unluckily I do not know about the method you are describing in your question (GetNamespace and PickFolder), so I can't be of much help. But since nobody answers you in the last hour, I dare to write a hint. First of all, try to precisely get your problem (is fld not defined? Is wks not defined? etc.). Also, I would try to simplify the code. For example:

      intRowCounter = 1
      For Each itm In fld.Items
            wks.Cells(intRowCounter, 1) = itm.To
            wks.Cells(intRowCounter, 2) = itm.SenderEmailAddress
            wks.Cells(intRowCounter, 3) = itm.Subject
            intRowCounter = intRowCounter + 1
        Next

Or, as in your schema:

      intRowCounter = 1
      For Each itm In fld.Items
            wks.Cells(intRowCounter, 1) = itm.To
            wks.Cells(intRowCounter + 1, 1) = itm.SenderEmailAddress
            wks.Cells(intRowCounter + 2, 1) = itm.Subject
            intRowCounter = intRowCounter + 3
        Next

But the above will not solve your problem. You can add an interruption point (click in the "margin" of the code window) and hover over variables to get their values. Also you can add an expression to the Watch Window to peep what may be wrong.

Visual Basic Doesn't Recognize Pathway

1 Answers1