Limit the number of character that an access VBA get from Outlook

[Cezar]

Hi All,

I was not sure what part of the forum to put this one, I have a VBA script that run in an Access Database this script looks in all emails in a folder and all subfolders in it working just fine just until it meets an email with more than 200 or 255 characters in the to field, and that is where I end up with the collection of information stops.

I was thinking if any one could have a look and see how I could limit the number of characters it will collect, as I have to store the information in a text field I have this limit.

Sub GetMailProp(objProp As Outlook.Items, ofProp As Outlook.MAPIFolder)
    ' Code for writeing Outlook mail properties to Access.
    
    ' Set up DAO objects. Code uses existing Access table titled "Email." Fields and data type must be sepcified in table.
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("Email")
    
    'Set up Outlook objects.
    Dim cMail As Outlook.MailItem
    
    'Write Outlook mail properties to Access "Email" table.
    iNumMessages = objProp.Count
    If iNumMessages <> 0 Then
        For i = 1 To iNumMessages
            If TypeName(objProp(i)) = "MailItem" Then
                Set cMail = objProp(i)
                 'Code used to insert individual outlook mail properties. Properties must match fields in Access table titled "Email."
                    rst.AddNew
                    rst!Subject = cMail.Subject
                    rst!SenderName = cMail.SenderName
                    rst!To = cMail.To
                    rst!SentOn = cMail.SentOn
                    rst!ReceivedTime = cMail.ReceivedTime
                    rst!MailFolder = ofProp.Name
                    rst!MailFolderPath = ofProp.FolderPath
                    rst.Update
            End If
        Next i
    End If
End Sub

[mrojas]

Use the Left function as follows:
rst!To = Left(cMail.To,255)

You may need to first store the cMail.To in a variable, then use the Left function.

[Cezar]

Hi Mrojas,

Just tried this solution and it is working great.
Thanks for the solution

BR

Cezar