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
     Else
        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
     Else
        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
Next

Exit_Command104_Click:
    Exit Sub

Err_Command104_Click:
    MsgBox Err.Description
    Resume Exit_Command104_Click
   
End Sub
sydneyguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
    Else
        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_GetOutlookMailitemProps:
    Exit Function

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

End Function
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
0
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.

Exit_GetOutlookMailitemProps:
        '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
0
sydneyguyAuthor Commented:
still workign on this project still not there
0
sydneyguyAuthor Commented:
good thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Outlook

From novice to tech pro — start learning today.