Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Digip
    Guest

    Email links (Excel 97 SR2)

    Well, I know everyone HATES the auto formatting as an email address of anything with an @ in it but I have finally run into a place where I NEED them! (I feel like the ultimate blacksheep asking this!<img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I have a list of clients that I want to be able to email individually while reviewing the spreadsheet. I know I can type "mailto:" and then their address in a cell and I'll get the hand and I will be able to just click on it and it will bring up Outlook Express but it would be very time consuming to edit all the addresses to add the "mailto".

    Is there some way to make the email address bring up Outlook Express without the "mailto"?

    Thanks much!

    Doug

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Email links (Excel 97 SR2)

    Not sure if I follow exactly what you want to do, but the following code will send the active workbook to the email address in the active cell. To enable it to work you will have to set up a reference in your VBA environment to the Outlook Library. To do that go to Tools, References (in VBA) and scroll down uintil you find Microsoft Oulook x.x Object Library and make sure it is checked. The x.x represents the version of outlook and in your case is probably 8.0. <pre>Sub MailTo()
    Dim objOLook As New Outlook.Application
    Dim objOMail As MailItem
    Dim strAddress As String
    Set objOLook = New Outlook.Application
    Set objOMail = objOLook.CreateItem(olMailItem)
    strAddress = ActiveCell.Value
    With objOMail
    .To = strAddress
    .Subject = ""
    .Body = ""
    .Attachments.Add ActiveWorkbook.FullName
    .Send
    End With
    Set objOMail = Nothing
    Set objOLook = Nothing
    End Sub</pre>

    You can include a Subject line and Body text if required

    Hope it is whatt you want.

    Andrew C

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Email links (Excel 97 SR2)

    I'm a little confused too. Why do you need to add the mailto: if Excel automatically formats it as an email address? I'm not sure about 97, but in Excel 2000 if I enter someone@someone.com it automatically creates a mailto: hyperlink even though it doesn't display the mailto: bit.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Digip
    Guest

    Re: Email links (Excel 97 SR2)

    Sorry guys if I wasn't clear enough. Here is what I need to do:
    While I am working in this spreadsheet I want to be able to simply click on the email address of a specific client and have a NEW email message appear so that I can send that one client an email without having to go through an address book or cut/paste.
    I CAN do that now in excel but it seems that in my 97 SR2 I must put "mailto:" in from of the address. IF I type in an email address without the mailto: then it LOOKS like an email address (font and color change) but I do NOT get the HAND cursor nor does it pop up an email when I click on it. If I put mailto: in fromt of the address it works fine.

    Is this a problem in my copy of Excel? do your copies automatically use the simple email as a hot link???

    Did I explain it better??<img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Thanks again for the help!

    Doug

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Email links (Excel 97 SR2)

    I use Excel 2000 (and Outlook, not Outlook Express, which may make a difference) but if I type in someone@somewhere.com it automatically creates an email hyperlink. The cursor does have to be within the cell that the address is in though - if it's outside the cell, even if it's over the hyperlink text itself, it doesn't work.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email links (Excel 97 SR2)

    To convert e-mail addresses to hyperlinks that behave the way you describe, select the cells with the e-mail addresses and run this macro:

    Option Explicit

    Sub ChangeTextToHyperlinks()
    Dim rRange As Range
    Dim rCell As Range
    Set rRange = Selection
    For Each rCell In rRange
    ActiveSheet.Hyperlinks.Add rCell, rCell.Value
    Next
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Digip
    Guest

    Re: Email links (Excel 97 SR2)

    Thanks for the tip, Jan, but it doesn't work. I ran your macro and it made the address LOOK like a link (hand cursor, font and color and underline) but when I click on it I get the error message "Can't Open the Specified File".
    Again, if I type the address as mailto:name@someplace.com it works just fine, changes the cursor, the font, color and underline and brings up an Outlook Express blank email with the address in the TO box.

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email links (Excel 97 SR2)

    Try this routine:

    <pre>Public Sub InsertMailTo()
    Dim oCell As Range
    For Each oCell In Selection
    oCell.Value = "mailto:" & oCell.Value
    Next oCell
    End Sub
    </pre>

    Legare Coleman

  9. #9
    Digip
    Guest

    Re: Email links (Excel 97 SR2)

    Again, Thanks but this too fails to work. This routine DOES change the "name@somplace.com" to "mailto:name@someplace.com" and it LOOKS like an email link (font, underline,color) but it too fails to change the cursor and will not bring up the new email window.

    In any cell I can type "mailto:name@someplace.com" and once I click out of the cell, it becomes a hyperlink and will change the cursor to the hand and bring up the NEW mail window when single clicked....

    I guess I have no choice by to manually add mailto: to every entry... Strange, because every other program I use will create a link from email formatted text and pop up the NEW mail window... arrgghhhhh.

    Thanks guys,

    Doug

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email links (Excel 97 SR2)

    After running my routine, what happens if you select one of the cells that has had the "mailto:", press F2 then Enter?
    Legare Coleman

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Email links (Excel 97 SR2)

    Doug,

    If you amend the code provided by Jan Karel above to include <pre>ActiveSheet.Hyperlinks.Add rCell, <font color=red>"MailTo:"</font color=red> & rCell.Value</pre>

    it might help.

    Andrew C

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email links (Excel 97 SR2)

    Would it help to select the cells and choose Format, Style, Hyperlink ?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    Digip
    Guest

    Re: Email links (Excel 97 SR2)

    If I hit F2 and then enter after running your routine, the F2 put me into the cell (normal) and Enter then just closes me out of the cell and does nothing. The same as if it was a normal cell you did that to.

    Doug

  14. #14
    Digip
    Guest

    Re: Email links (Excel 97 SR2)

    Jan, Style>Hyperlink was the first thing I tried. No joy. I still have no manually type in mailto:.

    Thanks

    Doug

  15. #15
    Digip
    Guest

    Re: Email links (Excel 97 SR2)

    Thanks Andrew but still no joy. your routine DID add the mailto: but it still is not an active link to the email program, it just looks like it is!<G>

    Doug

Page 1 of 2 12 LastLast

Posting Permissions

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