Help Using Excel 2003 to send emails
 

This piece of code worked fine when I was on Office 2000. Now I've
upgraded to 2003 I am struggling with the below if the length of
lsMailTo is longer than 1033 characters (what a strange number)

psBody = "Hello " & gsFirstName & vbCr & vbCr & psBody & _
vbCr & vbCr & "Regards,"

lsMailTo = _
"mailto:" & gsEmailAdd & _
"?subject=" & HexString(gsSubject) & _
"&body=" & HexString(psBody)


ActiveWorkbook.FollowHyperlink lsMailTo


Function HexString(psStr) As String

Dim liIndex As Long
Dim lsChar As String
Dim lsHex As String


For liIndex = 1 To Len(psStr)
lsChar = Mid(psStr, liIndex, 1)

If IsNumeric(lsChar) Or IsChar(lsChar) Then
HexString = HexString + lsChar
Else
lsHex = Hex(Asc(lsChar))

If Len(lsHex) = 1 Then
lsHex = "0" & lsHex
End If

HexString = HexString + "%" + lsHex
End If
Next

End Function


My code is probably overkilling which characters need to be converted to
hex (e.g. a comma would definately cause a run time error) but I am
curious to get this working please

--
Mike News

Help Using Excel 2003 to send emails
 

In message
at 17:30:24 on Sun, 17 Feb 2008, Mike
S wrote
This piece of code worked fine when I was on Office 2000. Now I've
upgraded to 2003 I am struggling with the below if the length of
lsMailTo is longer than 1033 characters (what a strange number)

psBody = "Hello " & gsFirstName & vbCr & vbCr & psBody & _
vbCr & vbCr & "Regards,"

lsMailTo = _
"mailto:" & gsEmailAdd & _
"?subject=" & HexString(gsSubject) & _
"&body=" & HexString(psBody)


ActiveWorkbook.FollowHyperlink lsMailTo


Function HexString(psStr) As String

Dim liIndex As Long
Dim lsChar As String
Dim lsHex As String


For liIndex = 1 To Len(psStr)
lsChar = Mid(psStr, liIndex, 1)

If IsNumeric(lsChar) Or IsChar(lsChar) Then
HexString = HexString + lsChar
Else
lsHex = Hex(Asc(lsChar))

If Len(lsHex) = 1 Then
lsHex = "0" & lsHex
End If

HexString = HexString + "%" + lsHex
End If
Next

End Function


My code is probably overkilling which characters need to be converted to
hex (e.g. a comma would definately cause a run time error) but I am
curious to get this working please

Does nobody know the answer then?
--
Mike News

Help Using Excel 2003 to send emails
 

You should probably mention what you are trying to do with this code,
what context it is being used, what mail program you are using, and
why you aren't directly automating Outlook.


--JP


On Feb 17, 12:30*pm, Mike
S wrote:
This piece of code worked fine when I was on Office 2000. *Now I've
upgraded to 2003 I am struggling with the below if the length of
lsMailTo is longer than 1033 characters (what a strange number)

psBody = "Hello " & gsFirstName & vbCr & vbCr & psBody & _
* * * * vbCr & vbCr & "Regards,"

lsMailTo = _
* * "mailto:" & gsEmailAdd & _
* * "?subject=" & HexString(gsSubject) & _
* * "&body=" & HexString(psBody)

ActiveWorkbook.FollowHyperlink lsMailTo

Function HexString(psStr) As String

Dim liIndex As Long
Dim lsChar *As String
Dim lsHex * As String

For liIndex = 1 To Len(psStr)
* * lsChar = Mid(psStr, liIndex, 1)

* * If IsNumeric(lsChar) Or IsChar(lsChar) Then
* * * * HexString = HexString + lsChar
* * Else
* * * * lsHex = Hex(Asc(lsChar))

* * * * If Len(lsHex) = 1 Then
* * * * * * lsHex = "0" & lsHex
* * * * End If

* * * * HexString = HexString + "%" + lsHex
* * End If
Next

End Function

My code is probably overkilling which characters need to be converted to
hex (e.g. a comma would definately cause a run time error) but I am
curious to get this working please

--
Mike News

Help Using Excel 2003 to send emails
 

In message

at 05:28:07 on Sat, 23 Feb 2008, JP wrote
You should probably mention what you are trying to do with this code,
what context it is being used, what mail program you are using, and
why you aren't directly automating Outlook.

It's for my ebay tracking spreadsheet, where I simply build up an email
to one person, with a subject line and body. I choose not to run
Outlook since the risk of viruses is higher, and in any case this worked
perfectly fine for several years until I upgraded to Office 2003, and
responds correctly to mailto: commands. Now I just get "Invalid
procedure call or argument (Error 5)" if the message is too long. If I
reduce it I'm OK. If I reduce it and add several X's it gives the error
if I blow the limit

I see I am not alone
http://www.xtremevbtalk.com/archive/.../t-148359.html
but this was never resolved :(
--
Mike News

Help Using Excel 2003 to send emails
 

It sounds like you might have hit a built-in limit. Check out
http://support.microsoft.com/kb/213841/en-us for an example. I don't
understand the purpose of the HexString function -- if you were using
Outlook, this would be much simpler.

If you change your mind and want to use Outlook, check out
http://www.rondebruin.nl/sendmail.htm for example code to use to send
those emails.


HTH,
JP

On Feb 23, 6:27*pm, Mike
S wrote:
It's for my ebay tracking spreadsheet, where I simply build up an email
to one person, with a subject line and body. *I choose not to run
Outlook since the risk of viruses is higher, and in any case this worked
perfectly fine for several years until I upgraded to Office 2003, and
responds correctly to mailto: *commands. *Now I just get "Invalid
procedure call or argument (Error 5)" if the message is too long. *If I
reduce it I'm OK. *If I reduce it and add several X's it gives the error
if I blow the limit

Help Using Excel 2003 to send emails
 

In message

at 08:46:11 on Sun, 24 Feb 2008, JP wrote
It sounds like you might have hit a built-in limit. Check out
http://support.microsoft.com/kb/213841/en-us for an example. I don't
understand the purpose of the HexString function -- if you were using
Outlook, this would be much simpler.

If you change your mind and want to use Outlook, check out
http://www.rondebruin.nl/sendmail.htm for example code to use to send
those emails.

I use HexString for reasons such as embedded spaces in the subject line,
commas, ?, & all seem to have similar effects as these

lsMailTo = _
"mailto:" & gsEmailAdd & _
"?subject=" & HexString(gsSubject) & _
"&body=" & HexString(psBody)


and cause Excel to bomb out if I don't use Hex conversion


If you change your mind and want to use Outlook, check out
http://www.rondebruin.nl/sendmail.htm for example code to use to send
those emails.

Some interesting reading there. Cheers
--
Mike News