Excel Hyperlinks

Hi. This may have already been asked. I have a column
that has an email address in each row. How can I create
an email message by clicking on a header that will
include all emails in the individual rows? Keeping in
mind that the emails in the rows may change later?

Would the HYPERLINK function be able to do this? Thanks

Forgot to mention that I was looking for a cell function
and not a macro/vba code. Thanks.

-----Original Message-----
Hi. This may have already been asked. I have a column
that has an email address in each row. How can I create
an email message by clicking on a header that will
include all emails in the individual rows? Keeping in
mind that the emails in the rows may change later?

Would the HYPERLINK function be able to do this? Thanks
.

Ed wrote:
This may have already been asked. I have a column
that has an email address in each row. How can I create
an email message by clicking on a header that will
include all emails in the individual rows?


I don't understand the question.
Do you want to send one email message to all the addresses in the
column? Or to the addresses in the selected cells? And what do you
mean by "a header"?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Ed wrote:
Forgot to mention that I was looking for a cell function
and not a macro/vba code.


In that case I am even more mystified by what you are trying to do.
I don't think you will be able to get a cell function to send an email.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Hi. I wish to send a single email to any email address in
a specific column. Not a selection. Right now, there are
249 rows in that column. The header I mentioned is
actually a column heading,sorry.

I have tried to append all emails in that column into a
string variable with a macro. When the Outlook email
message appears, I only have about 5% of the emails
listed in the TO field. Excel seems to only allow a
certain number of characters in a string, right?
I have also tried to load all emails into an array that
increments when the length of the string is 230. But
that's where I'm stuck. I loop through the array and
perform a FollowHyperlink. I get all emails to appear in
the TO field of the email, but I get multiple windows.
Help?
-----Original Message-----
Ed wrote:
This may have already been asked. I have a column
that has an email address in each row. How can I
create
an email message by clicking on a header that will
include all emails in the individual rows?


I don't understand the question.
Do you want to send one email message to all the
addresses in the
column? Or to the addresses in the selected cells? And
what do you
mean by "a header"?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

.

I wish to send a single email to any email address in
a specific column. Not a selection.


Do you mean "I wish to send a single email to ALL the email addresses
in a specific column"?

The header I mentioned is actually a column heading,sorry

Do you mean the first cell in the column or the button-like thing at
the top of the column that contains the column letter?

Excel seems to only allow a certain number of characters in a string,
right?

Rather a lot actually - about 32,000
However, the interface you are using to Outlook may restrict the number
of characters passed across.

I don't use Outlook, but you could try using the following code
(untested):

Sub DoAnEMail(Recipients As Range)
Dim oNS As Object
Dim oF As Object
Dim oI As Object
Dim oMail As Object
Dim R as Range

On Error Resume Next

Set oOutl = GetObject(, "Outlook.Application")
If Err 0 Then
Err.Clear
Set oOutl = CreateObject("Outlook.Application")
If Err 0 Then
MsgBox "I cannot send an email - Outlook is not available on this
machine"
Exit Sub
End If
End If
On Error GoTo locErr
Set oNS = oOutl.GetNamespace("MAPI")
oNS.Logon

Set oMail = oOutl.CreateItem(0) ' olMailItem
For Each R In Recipients.Cells
oMail.Recipients.Add R.Value
Next
Set oI = oMail.GetInspector
oI.Display
End Sub

Call it by, for example

DoAnEMail Range(Cells(1,ActiveCell.Column), _
Cells(1,ActiveCell.Column).End(xlDown))


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Your ISP may limit the number of individuals to much less than 256,
there may be other limits as well such as the length of text in Excel
which you can look up in HELP, specifications for details.

32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.

Here is an example
%40 is a commercial at sign (@)
%3B is a semi-colon (

watch for line breaks in the following:
=HYPERLINK("mailto:ddd.a%40hotmail.com%3ddd.b%40ho tmail.com?subject=XYZ Club Inquiry&body=concerning our website
http//www.geocities.com/xyzclub/program.html ...","ddd")

for one person
","ddd")

To send to each person in a selection you would need the macro you
don't want, and you would probably only have the email address in the cell.

No, I don't think the question has been asked before, because such things
would use macros..
---
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

"Ed" wrote in message ...
Forgot to mention that I was looking for a cell function
and not a macro/vba code. Thanks.

-----Original Message-----
Hi. This may have already been asked. I have a column
that has an email address in each row. How can I create
an email message by clicking on a header that will
include all emails in the individual rows? Keeping in
mind that the emails in the rows may change later?

Would the HYPERLINK function be able to do this? Thanks
.

are you the same poster as Ed ? It is very difficult to follow
conversations posted through communities that do not thread
properly, don't have unique names like a first and last name,
and have anonymous@ thrown in as an
email address, and perhaps inconsistent names entered
from one post to another.

A column width can only be 255 characters wide. Try turning on
cell wrapping. Format, cells, alignment, [x] wrap.

See specification limits in my other reply in this thread.
---
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

wrote in message ...
Hi. I wish to send a single email to any email address in
a specific column. Not a selection. Right now, there are
249 rows in that column. The header I mentioned is
actually a column heading,sorry.

I have tried to append all emails in that column into a
string variable with a macro. When the Outlook email
message appears, I only have about 5% of the emails
listed in the TO field. Excel seems to only allow a
certain number of characters in a string, right?
I have also tried to load all emails into an array that
increments when the length of the string is 230. But
that's where I'm stuck. I loop through the array and
perform a FollowHyperlink. I get all emails to appear in
the TO field of the email, but I get multiple windows.
Help?
-----Original Message-----
Ed wrote:
This may have already been asked. I have a column
that has an email address in each row. How can I
create
an email message by clicking on a header that will
include all emails in the individual rows?


I don't understand the question.
Do you want to send one email message to all the
addresses in the
column? Or to the addresses in the selected cells? And
what do you
mean by "a header"?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

.