Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Read Mails from Excel - Revisit (xls 2003)

    Hi Hans,

    I refers to the post <post#=460411>post 460411</post#> for the following codes, I too encounter the error msg : Run time error '-2147352567(80020009)' Could not send the message
    and the debug shown on the line highlighted.

    Further down in that tread <post#=463421>post 463421</post#> , I am not sure where to insert these few lines of codes to check the error.
    Would appreciate that you can revisit and advise.


    Sub Verify()
    Dim objOL As Object 'create outlook variable (for easy calling)
    Dim strFrom As String 'whos it from?
    Dim strBody As String 'body of email
    Dim strExists As String 'what we wanna find
    Dim objNameSpace As Object
    Dim objFolder As Object
    Dim objMessage As Object
    Dim lngRow As Long

    On Error GoTo ErrHandler

    Set objOL = CreateObject("Outlook.Application") 'create outlook object
    'objOL.Visible = True 'make sure outlook is visible
    Set objNameSpace = objOL.GetNamespace("MAPI") ' required name
    Set objFolder = objNameSpace.GetDefaultFolder(olFolderInbox)

    lngRow = 2
    'for statement cycling through inbox starts here...
    For Each objMessage In objFolder.Items
    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">strFrom = objMessage.Reply.Recipients(1).Address 'find sender </span hi>
    If strFrom = "something@something.com" Then
    strBody = objMessage.Body
    strExists = InStr(1, strBody, "exists")
    If Not strExists = "" Then
    'copy strexists into appropriate cell
    ActiveSheet.Range("C" & lngRow) = strExists
    lngRow = lngRow + 1
    End If
    End If
    Next objMessage

    ExitHandler:
    On Error Resume Next
    Set objMessage = Nothing
    Set objFolder = Nothing
    Set objNameSpace = Nothing
    objOL.Quit 'close outlook
    Set objOL = Nothing 'free resources
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Read Mails from Excel - Revisit (xls 2003)

    Simply replace the line that causes the error with the lines from <post:=463,421>post 463,421</post:>.
    Do you need to do exactly the same as OfficeSpacer? I.e., check whether a mail is from a specific sender (something@something.com)?

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Read Mails from Excel - Revisit (xls 2003)

    Hi Hans,

    Thanks for the response. I have replaced the offending lines with the codes and now I get the same error msg at this line
    Set objReply = objMessage.Reply

    In answering your query, Yes, it is the same condition that I need to do...from a specific sender.

    thanks, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Read Mails from Excel - Revisit (xls 2003)

    I don't know why it fails - it works on my PC. But I have attached a different version that uses CDO (Collaborative Data Objects) to get the sender address.
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Read Mails from Excel - Revisit (xls 2003)

    Hi Hans,

    The codes run without problem but it return a number '0' in C2 and '5' in C3. I thought it suppose to return the email msg and the sender address <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    What was your result?

    regards, fy
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Read Mails from Excel - Revisit (xls 2003)

    The line

    ActiveSheet.Range("C" & lngRow) = strExists

    places a number in a cell. The macro doesn't contain code to put anything else in cells. This is a problem in the code from the original thread.
    The attached version should place the sender in column A and the message in column B.
    Attached Files Attached Files

  7. #7
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Read Mails from Excel - Revisit (xls 2003)

    Hi Hans,

    Thanks. it works auto magically. how do I include more senders instead of one. I have try to include in the line
    If strFrom = "somebody@somebody.com; anybody@anybody.com" Then or "somebody@somebody.com & anybody@anybody.com" but these won't works.

    Is it possible to add the date of receiving the email in Col C in the codes? Should I use Now() but this function is volatile.....

    Lastly, do you also encounter the warning msg from Outlook asking for permission to grant access to Outlook? I am not sure if this is my anti virus program
    or it is the security bulit-in in Outlook.

    Thanks, fy
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Read Mails from Excel - Revisit (xls 2003)

    1) You can replace

    If strFrom = "something@something.com" Then
    ...
    Then

    with

    Select Case strFrom
    Case "something@something.com", "anybody@anybody.com"
    ...
    End Select

    2) You can add the following line:

    ActiveSheet.Range("C" & lngRow) = objMessage.ReceivedTime

    ReceivedTime contains both the date and time of receiving the e-mail. You can format column C to display only the date, if you wish.

    3) The security message is displayed by Outlook. See <post:=488,173>post 488,173</post:> for some ways to avoid it.

  9. #9
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Read Mails from Excel - Revisit (xls 2003)

    Hi Hans,

    Thank you very much. From now on, I will not miss any emails from my important clients not to mentioned my boss' emails <img src=/S/grin.gif border=0 alt=grin width=15 height=15> given that
    I received approximately 200 emails per day. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •