Help Using Excel 2003 to send emails

[Mike]

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]

In message <[email protected]>
at 17:30:24 on Sun, 17 Feb 2008, Mike

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?

 

[JP]

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

 

[Mike]

In message

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/index.php/t-148359.html
but this was never resolved

 

[JP]

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

 

[Mike]

In message

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