Sending email via VB - address length

  • Thread starter Thread starter Carl Woodall
  • Start date Start date
C

Carl Woodall

Hi

I'm trying to send e-mails from Excel 97 via VB but am
having problems as the address text is truncated at 260
characters. The code I'm using is:

ActiveWorkbook.FollowHyperlink address:=addresses
where: addersses is defined as a variant type.

Does anybody know of a work around or is this just a limit
of the function? The mail client is Outlook 98.

Cheers
Carl Woodall
 
Carl

I don't have OL98, but in OL2000, it worked fine with 300 characters. Post
how you are using the FollowHyperlink method and I'll see if I can reproduce
it.
 
The code I'm using is:

Sub Send_Email()
Dim flag As Boolean

Dim count As Integer
Dim last_row As Integer
Dim length As Integer
Dim total_length As Long
Dim value As Integer

Dim row_height As Double

Dim addresses As Variant
Dim this_address As String

Dim h As Object
Dim endcell As Object
Dim startcell As Object

'
' Initialise Values
'
addresses = "mailto:"
count = 0
flag = False

'
' Count number of names
'
Cells(Row_num_start_of_name_list, 1).Activate
While Not (ActiveCell.value = "")
count = count + 1
ActiveCell.Offset(1, 0).Activate
Wend

Set startcell = Cells(Row_num_start_of_name_list, 1)
Set endcell = Cells(Row_num_start_of_name_list +
count - 1, 1)
Range(startcell, endcell).Select

For Each h In Selection.Hyperlinks
this_address = h.address
row_height = h.Parent.RowHeight
If (row_height > 0) Then
length = Len(this_address)

value = length - 7
If (value < 0) Then
value = 0
End If

addresses = addresses + Right(this_address,
value) + "; "
flag = True
End If
Next

If flag Then
ActiveWorkbook.FollowHyperlink address:=addresses
End If
End Sub

Basically I'm picking up a list of hyperlinks I've defined
in a worksheet and which I can filter (Hence the code
checking the row height). I build up 'addresses' to
contain one large mailing list and then open the new e-
mail message using the FollowHyperlink method.

Hope it's not a limitation of OL98!

Cheers
Carl
 
Carl

I can do it up to about 448 characters at which point I get the message
"default mail client no properly installed"

I think you should try posting this to an outlook newsgroup. There's a
better chance that someone there is using or is familiar with O98.

If you can't get any satisfaction, you can try automating Outlook instead of
using a mailto hyperlink. If you go this route, you won't be able to use it
with any other mail program. See here

www.dicks-clicks.com

for information on automating Outlook. I'm sure you will be able to add as
many members to the Recipients collection as you like.

Whatever the result, be sure to let us know.
 
Back
Top