Macro won't work

[exandbeyond]

Can you someone tell me what's wrong?

Sub GetMailInfo(Path As String)

    Dim MyOutlook As Outlook.Application
    Dim msg As Outlook.MailItem
    Dim x As Namespace

    Set MyOutlook = New Outlook.Application
    Set x = MyOutlook.GetNamespace("MAPI")

    FileList = GetFileList(Path + "*.msg")


    row = 1

    While row <= UBound(FileList)

        Set msg = x.OpenSharedItem(Path + FileList(row))

        Cells(row + 1, 1) = msg.Subject
        Cells(row + 1, 2) = msg.Sender
        Cells(row + 1, 3) = msg.CC
        Cells(row + 1, 4) = msg.To
        Cells(row + 1, 5) = msg.SentOn


        row = row + 1
    Wend

End Sub

If uses this function:

Function GetFileList(FileSpec As String) As Variant
'   Taken from http://spreadsheetpage.com/index.php/tip/getting_a_list_of_file_names_using_vba/
'   Returns an array of filenames that match FileSpec
'   If no matching files are found, it returns False

    Dim FileArray() As Variant
    Dim FileCount As Integer
    Dim FileName As String

    On Error GoTo NoFilesFound

    FileCount = 0
    FileName = Dir(FileSpec)
    If FileName = "" Then GoTo NoFilesFound

'   Loop until no more matching files are found
    Do While FileName <> ""
        FileCount = FileCount + 1
        ReDim Preserve FileArray(1 To FileCount)
        FileArray(FileCount) = FileName
        FileName = Dir()
    Loop
    GetFileList = FileArray
    Exit Function

'   Error handler
    NoFilesFound:
        GetFileList = False
End Function

 

[Chris Macro]

What line is giving you the error?

 

[exandbeyond]

What line is giving you the error?

The macro doesn't even show up on the Macro's list when I try to run it. Am I missing something?

 

[exandbeyond]

Dup post. Won't let me delete

 

[Chris Macro]

It's because you have (Path as String). Why are you passing the variable "Path" through this macro? Coding like this means that another subroutine must submit a value to Path before you run GetMailInfo. Does that make sense?

 

[exandbeyond]

It's because you have (Path as String). Why are you passing the variable "Path" through this macro? Coding like this means that another subroutine must submit a value to Path before you run GetMailInfo. Does that make sense?

Somewhat. So I am I guessing I will have to Declare Path as a String with in this Sub Routine i.e. Dim Path as String. Then recode in a way so it will get the Path some how. Thank you.

 

[Chris Macro]

Yep, you're on the right track! Let me know if you need any help getting your Path. Just start off by declaring your subroutine as: Sub GetMailInfo()

 

[exandbeyond]

Yep, you're on the right track! Let me know if you need any help getting your Path. Just start off by declaring your subroutine as: Sub GetMailInfo()

Thanks Chris Macro. I will work on this today if I have time. I am hoping to have something working by tuesday of next week. I am sure I will get stuck so I will try to post what I have today or monday. I literally have hundreds of emails to put on a spreadsheet for my monthly report and my report is due on the 24th.

 

[exandbeyond]

I haven't had to time to get to this. Does anyone have a macro that gets the Subject,Sender,To, and SentOn from a msg file to excel?