• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 348
  • Last Modified:

msoutlook using vba and findng the .To and .Recievedtime can get subject and body ok

have some code below to extract the emails from my out look folder can read BODY and SUBJECT, also recievedtime ,  but cannot get the syntax correct to find the "SenderEmailAddress" and the ".To" from the email
guseesing this is just a syntax problem cannot see the object structure in the debug to see what it wants its just blank

also some times the recievedtime is null so when i try to save these to a text box it errors out, so need to test to see if the objects null before i operate on it
have tried
 If IsNull(objItem.ReceivedTime) Then
        Forms![frmSelectCompanymain]![capturesubject] = objItem.ReceivedTime
    End If
but does not seem to like checking for nulls on objects

Private Sub Command104_Click()

On Error GoTo Err_Command104_Click
Const olFolderInbox = 6
Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set objInbox = objNamespace.GetDefaultFolder(olFolderInbox)
strFolderName = "testing.com" ' objInbox.Parent
Set objMailbox = objNamespace.Folders(strFolderName)
Set objFolder = objMailbox.Folders("inbox")

Set colItems = objFolder.Items
x = 1
For Each objItem In colItems
    Dim nameval As Object
    Dim anInteger As Integer
    Dim aString As String
    'anInteger = CType(anObject, Integer)
' ****************  these work *******
    Forms![frmSelectCompanymain]![capturebody] = objItem.Body
    Forms![frmSelectCompanymain]![capturesubject] = objItem.subject
    Forms![frmSelectCompanymain]![capturesubject] = objItem.CreationTime

' *************** these dont ************
    Forms![frmSelectCompanymain]![capturesubject] = objItem.SenderEmailAddress
    Forms![frmSelectCompanymain]![capturesubject] = objItem.To

    Forms![frmSelectCompanymain]![capturecount] = x
    If IsNull(objItem.ReceivedTime) Then
        Forms![frmSelectCompanymain]![capturesubject] = objItem.ReceivedTime
    End If
  MsgBox "ff"
  '  aString = objItem.Body.ToString()
  '  SQLString = "INSERT INTO tblemailextract ( subject, body ) values (objItem.subject,Forms![frmSelectCompanymain]![datacapture].value )"
 '   updateTable (SQLString)
  x = x + 1

    Exit Sub

    MsgBox Err.Description
    Resume Exit_Command104_Click
End Sub
  • 3
  • 2
1 Solution
omgangIT ManagerCommented:
The following function successfully retrieves both sender and recipient values
OM Gang

Public Function GetOutlookMailitemProps()
On Error GoTo Err_GetOutlookMailitemProps

        'declare and open instance of MS Outlook
    Dim olOutlook As New Outlook.Application
    Dim olNS As Outlook.NameSpace
    Dim olFolders As Outlook.MAPIFolder
    Dim olInbox As Outlook.MAPIFolder
    Dim olSubFolder As Outlook.MAPIFolder
    Dim olItems As Outlook.Items
    Dim olInboxItem As Outlook.MailItem
    Dim strPSTName As String, strFolderName As String
    Dim strAddress As String, strToAddress As String
    Dim strSubFolderName As String, strAddy As String
    Dim lngCounter As Long
        'Display Name of outlook Mailbox or PST file we want to use
    strPSTName = "Mailbox - Gang, OM"
        'name of folder in PST file we want to work with
    strFolderName = "Inbox"
        'name of sub folder in PST file we want to work with - if applicable
        'set to empty string, e.g. "", if we're not working with
    strSubFolderName = "Test,2"
        'set object Outlook NameSpace
    Set olNS = olOutlook.GetNamespace("MAPI")
        'set object NameSpace Folders for PST file
    Set olFolders = olNS.Folders(strPSTName)
        'set object mail folder for PST file
    Set olInbox = olFolders.Folders(strFolderName)
        'set object mail sub folder for PST file
    If strSubFolderName <> "" Then
        Set olSubFolder = olInbox.Folders(strSubFolderName)
    End If
        'set object messages in folder
    If Not olSubFolder Is Nothing Then
            'set object messages in sub folder
        Set olItems = olSubFolder.Items
        Set olItems = olInbox.Items
    End If
        'display hourglass while working
    DoCmd.Hourglass True
        'loop through list of mail messages
    For lngCounter = 1 To olItems.Count
        Set olInboxItem = olItems(lngCounter)
            'get sender address
        strAddress = olInboxItem.SenderEmailAddress
            'get recipient address
        strToAddress = olInboxItem.To
        Debug.Print "From: " & strAddy & "; To: " & strToAddress
    Next lngCounter

    Exit Function

    MsgBox Err.Number & " (" & Err.Description & ") in procedure GetOutlookMailitemProps of Module Module1"
    Resume Exit_GetOutlookMailitemProps

End Function
omgangIT ManagerCommented:
Are you doing this in Access?  If so, try

Forms![frmSelectCompanymain]![capturesubject] = Nz(objItem.ReceivedTime, #1/1/1900#)
assuming the capturesubject is a date/time field
if it is a text/string value field instead try
Forms![frmSelectCompanymain]![capturesubject] = Nz(objItem.ReceivedTime, "")

If Nz won't evaluate the expression you could also try a variable assignment and then testing
Dim varRecTime As Variant
varRecTime = objItem.ReceivedTime
Forms![frmSelectCompanymain]![capturesubject] = Nz(varRecTime, #1/1/1900#)

OM Gang
omgangIT ManagerCommented:
In the function I posted above I forgot to turn off the hourglass and I also forgot to destroy object variables.  Sorry 'bout that.

        'turn off hourglass
    DoCmd.Hourglass False
        'destroy object variables
    Set olInboxItem = Nothing
    Set olItems = Nothing
    Set olSubFolder = Nothing
    Set olInbox = Nothing
    Set olFolders = Nothing
    Set olNS = Nothing
    Exit Function

OM Gang
sydneyguyAuthor Commented:
still workign on this project still not there
sydneyguyAuthor Commented:
good thanks
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now