Outlook: Prefixing .MSG (message) files with the date the e-mail was sent

I recently copied all my e-mails out of an archive folder, so I could zip them up on my hard drive. To do this I wanted to rename each file with the date the email was sent, using the format YYYY-MM-DD.

The VBA code below works for me 🙂

Please ensure you test any script taken from my website on a test/development machine, before running on a production server

 
Sub GetMessageDate()
    Dim OlApp As Outlook.Application
    Set OlApp = GetObject(, "Outlook.Application")
    Dim MsgFilePath
    Dim Eml As Outlook.MailItem
    Dim Path As String
    Path = "C:Outlook Files"
    
    If OlApp Is Nothing Then
        Err.Raise ERR_OUTLOOK_NOT_OPEN
    End If

    Dim fs As Object
    Set fs = CreateObject("Scripting.FileSystemObject")
    Dim temp As Object
    Set temp = fs.GetFolder(Path)
    
    For Each MsgFilePath In temp.files
        Set Eml = OlApp.CreateItemFromTemplate(Path & MsgFilePath.Name)
        'Now rename the file
        Name Path & MsgFilePath.Name As Path & Format(Eml.SentOn, "YYYY-MM-DD") & " " & MsgFilePath.Name
        Set Eml = Nothing
    Next
    
    Set OlApp = Nothing
End Sub

VB: Retrieve the header row column names from a text file

The code below is an example of how to retrieve the column/field names from the header row from a text file, and add them to a “ListBox”.

Please ensure you test any script taken from my website on a test/development machine, before running on a production server

Dim tfp As Microsoft.VisualBasic.FileIO.TextFieldParser
' Change "Filename.TXT" to the filename of your choice
tfp = My.Computer.FileSystem.OpenTextFieldParser("Filename.TXT")
' Identify the file type as Delimited
tfp.TextFieldType = FileIO.FieldType.Delimited

' Set the delimiter of the file.  In this example comma.
tfp.Delimiters = New String() {","}
' To set the delimiter to the pipe system, use:
'    tfp.Delimiters = New String() {"|"}
' or, for the Tab character, use:
'    tfp.Delimiters = New String() {vbTab}

' Does the file use quotes to enclose text values?
tfp.HasFieldsEnclosedInQuotes = True

' Declare a variable to store the current row
Dim cr() As String

' Loop through the lines within the file
While Not tfp.EndOfData
    Try
        ' Get the currentrow, and read the fields
        cr = tfp.ReadFields
        ' Declare a variable to store the current field
        Dim cf As String
        ' Loop through each field within the current row
        For Each cf In cr
            ListBox.Items.Add(cf.ToString)
        Next
    Catch malFormLineEx As Microsoft.VisualBasic.FileIO.MalformedLineException
        MessageBox.Show("Line " & malFormLineEx.Message & _
                        "is not valid and will be skipped.", _
                        "Malformed Line Exception")
    Catch ex As Exception
        MessageBox.Show(ex.Message & " exception has occurred.", _
                        "Exception")
    Finally
        ' Close theOpenTextFieldParser
        tfp.Close()
    End Try
End While

VB: The ‘Microsoft.Jet.OLEDB.4.0’ provider is not registered on local machine

I was in the middle of writing an application to retrieve the list of Access Database Tables, but I was getting the error “The ‘Microsoft.Jet.OLEDB.4.0’ provider is not registered on the local machine.”

I discovered, that as I was using Vista x64, I needed to change the “Advanced Compiler Options” in Visual Studio to target the “x86” CPU, thereby creating a 32-bit application.

Advanced Compiler Options

When I tried again, it worked