hyperlinks

L

lldjc

I keep a listing of family e-mail addresses in Excel. I'd like to be able to
pick and choose several hyperlinks to be able to send e-mails to whoever I
choose. Is this possible? I seem to be able to only send e-mail to one
hyperlink at a time.
 
D

David McRitchie

If you want to generate the letter through code see Ron de Bruin's site,
but I assume you just mean to open a blank email letter with the addresses
filled in.

If you are sending to several relatives or to a club I think that is best
handled with your email group lists.

However you can create a hyperlink in Excel or anywhere else that
uses hyperlinks to list several people.

I guess what you want to do is to pick several names in a selection
and start an email. That should be fairly straight forward, it just
a matter of stringing cells together together in a correct format.

So you probably have the email addresses in your Excel down a column
and you would select the one ones you want. NOT unlike what you would
do from your address book if in say Excel you clicked on TO: to the
left of the addresses you might type in.
See if Ron de Bruin's addin will do that -- I can't tell but it seems to hint at it
http://www.rondebruin.nl/mail/add-in.htm
 
D

David McRitchie

Here is the macro

Sub mailto_Selection()
Dim Email As String, Subj As String, cell As Range
Dim response As Variant
Dim msg As String, url As String
Email = "" 'create list below
Subj = "Family Newsletter"
msg = "Dear Family,"
' -- Create the URL
For Each cell In Selection
Email = Email & cell.Text & "; "
Next cell
url = "mailto:" & Email & "?subject=" & Subj & "&body=" _
& Replace(msg, Chr(10), "/" & vbCrLf & "\")
MsgBox url
url = Left(url, 2025) 'was successful with 2025 , not with 2045
' -- Execute the URL (start the email client)
ShellExecute 0&, vbNullString, url, vbNullString, vbNullString, vbNormalFocus
End Sub

A1: [email protected]
A2: [email protected]
A3: [email protected]
A4: [email protected]
A5: Dad (at work)<[email protected]>

I would suggest that you delete the hyperlinks from these if you
are going to select them. See
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#DelHyperlinks
actually you can type them in with a single quote prefix to indicate text


Select cells A1 & A4 using Ctrl key
run the macro

The message will not be sent, it will be waiting for you to fill in
your content.

If you are using Outlook Express, the names will be sent as well
based on the email addresses. But the last example (A5:) shows that
you can include both. it should have a single < before the email
and a single > after the email address.l

If not familiar with macros see
Getting Started with Macros and User Defined Functions
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
D

David McRitchie

or it you want to create the hyperlink in a cell all you need to do is
enter them in a cell separating each with a semicolon (US list separator char).
You can add a space after the semicolon to make it easier to read.

[email protected]; [email protected]
 
L

lldjc

Thanks for the help, this is what I was trying to do.


David McRitchie said:
Here is the macro

Sub mailto_Selection()
Dim Email As String, Subj As String, cell As Range
Dim response As Variant
Dim msg As String, url As String
Email = "" 'create list below
Subj = "Family Newsletter"
msg = "Dear Family,"
' -- Create the URL
For Each cell In Selection
Email = Email & cell.Text & "; "
Next cell
url = "mailto:" & Email & "?subject=" & Subj & "&body=" _
& Replace(msg, Chr(10), "/" & vbCrLf & "\")
MsgBox url
url = Left(url, 2025) 'was successful with 2025 , not with 2045
' -- Execute the URL (start the email client)
ShellExecute 0&, vbNullString, url, vbNullString, vbNullString, vbNormalFocus
End Sub

A1: [email protected]
A2: [email protected]
A3: [email protected]
A4: [email protected]
A5: Dad (at work)<[email protected]>

I would suggest that you delete the hyperlinks from these if you
are going to select them. See
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#DelHyperlinks
actually you can type them in with a single quote prefix to indicate text


Select cells A1 & A4 using Ctrl key
run the macro

The message will not be sent, it will be waiting for you to fill in
your content.

If you are using Outlook Express, the names will be sent as well
based on the email addresses. But the last example (A5:) shows that
you can include both. it should have a single < before the email
and a single > after the email address.l

If not familiar with macros see
Getting Started with Macros and User Defined Functions
http://www.mvps.org/dmcritchie/excel/getstarted.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
Click to expand...
 
D

David McRitchie

You're welcome, creates kind of a nice understandable skeleton to do
whatever you want in programming since it will allow selection of
individuals from within a column to be emailed..

My original was a bit more complex and actually not much more code
as all the text was in the spreadsheet. Created for when I changed my website url.
The body consisted of a fixed portion, an optional portion specific
to the receiver's site, and another fixed portion. and used and updated
a send/sent/quit flag. And like this one wasn't sent out automatically but
allowed further review before sending.
 
Top