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

    Extract & Hyperlink to Send Emails (Excel 2003)

    Hi

    I have a bunch of mails in my send items folder, and if they are send to a specific recipient or recipients, is it doable for a macro to show
    the name in Col A, insert a hyperlink to that message in col B, the date and time that the email sent in Col C.

    All new send emails to this recipient or recipients are to insert into the next available row/s as I would be keeping the old mails for a while.

    TIA

    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

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

    Re: Extract & Hyperlink to Send Emails (Excel 2003)

    The macro in the attached text file will fill columns A:C with the requested info.
    You'll run into Outlook security - you'll have to allow the code to access Outlook, or use one of the workarounds mentioned in <post:=488,173>post 488,173</post:>.

    To add newly sent mails to the list automatically would require writing an event handler in Outlook, I cannot help you with that.
    Attached Files Attached Files

  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: Extract & Hyperlink to Send Emails (Excel 2003)

    Hi Hans,

    Thanks for looking into this. I have went through the code and I am unable to find the line which stated the specific recipient or recipient's that i want
    to extract. for example, if I want to extract email address " hans@woody.com" and "abc@zxy.com", where should I place these in the codes.

    TIA

    regards, 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: Extract & Hyperlink to Send Emails (Excel 2003)

    See the attached version.
    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: Extract & Hyperlink to Send Emails (Excel 2003)

    Hi Hans

    The macro doesn't seem to be doing the expected. I have placed the codes in a normal module and run a test on it, and nothing were shown in sheet 1
    see attached. Not sure where did I did wrong.

    TIA

    regards, fy
    Attached Files Attached Files
    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: Extract & Hyperlink to Send Emails (Excel 2003)

    If nothing is added to the sheet, there are several possibilities:
    - You have no e-mails in your Sent Items folder sent to the e-mail address you specified in the code.
    - The recipient is displayed differently in the Sent Items folder, e.g. 'Bill Gates' instead of bgates@microsoft.com.
    - The e-mails have more than one recipient (the code only checks if the 'To' box is equal to the specified address).

  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: Extract & Hyperlink to Send Emails (Excel 2003)

    Hi Hans,

    I found out the why nothing was added to sheet even though I have run a test by sending the mail back to myself.
    Thank sfor the additional clarifications, it is very useful.

    cheers, 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
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract & Hyperlink to Send Emails (Excel 2003)

    Hi Hans,

    I have tried the codes with some success, it extract the send emails up to some point, for eg, if my send folder have send items to abc@xyz.com from
    1st Oct 2007 to 31st Jan 2008, its will only extract up to 10th Nov 2007 and stop. If I run the codes again, it repeat what have been extracted.
    I want to extract all email send to a specific address from 1st Oct 2007 to 31st Jan 2008.Why doesn't it extract from 1st Oct 2007 to 31st Jan 2008?
    If I want to add a specific subject line into the code, eg. email subject is "Final Reminder" how do I do it?

    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

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

    Re: Extract & Hyperlink to Send Emails (Excel 2003)

    Do you get an prompt to allow the code to access your mail or address book? If so, do you give it enough time?
    Do you get an error message?

    You could add a test for the subject, e.g.

    For Each olItm In olFld.Items
    Select Case olItm.To
    Case "him@some.com", "her@other.sg"
    If olItm.Subject = "Final Reminder" Then
    lngRow = lngRow + 1
    Cells(lngRow, 1) = olItm.To
    ActiveSheet.Hyperlinks.Add Anchor:=Cells(lngRow, 2), _
    Address:="Outlook:" & olItm.EntryID, _
    TextToDisplay:=olItm.Subject
    Cells(lngRow, 3) = olItm.SentOn
    End If
    End Select
    Next olItm

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

    Re: Extract & Hyperlink to Send Emails (Excel 2003)

    I get an prompt to allow the code to access my mail and have allow the max time of 10 min for accessing the mail folder.
    No other error message appeared.

    I noticed that the codes extracted the far dates items and stop, is it that sent items are too many? I have about close
    to 300 send items in the sent folder.

    regards, 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

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

    Re: Extract & Hyperlink to Send Emails (Excel 2003)

    Sorry, no idea.

Posting Permissions

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