Getting around the 255 Character count for creating an email in outlook

Question

I have the following VBA and it is stopping once it has time the Description Field in the email. I have researched and there seems to be a 255 character limit. How do I work around that get an infinite number of characters depending on variable inputs.

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Sub GenerateVolumeQuoteEMail()
    Dim Email As String, Subj As String
    Dim Msg As String, URL As String
    Dim r As Integer, x As Double
    For r = 4 To 4 'data in rows 2-4
'       Get the email address
'       Email = Cells(r, 6)

'       Message subject
        Subj = "Volume Rate Request"

'       Compose the message
        Msg = ""
        Msg = Msg & "UTS Order ID: " & Cells(r, 12) & vbCrLf & vbCrLf
        Msg = Msg & "Shipping Adress: " & Cells(r + 1, 12) & vbCrLf & vbCrLf
        Msg = Msg & "Consignee Adress: " & Cells(r + 2, 12) & vbCrLf & vbCrLf
        Msg = Msg & "Description: " & Cells(r + 3, 12) & vbCrLf & vbCrLf
        Msg = Msg & "Hazmat (Yes or No): " & Cells(r + 4, 12) & vbCrLf & vbCrLf
        Msg = Msg & "Skid Count: " & Cells(r + 4, 12) & vbCrLf & vbCrLf
        Msg = Msg & "Weight (in LBS): " & Cells(r + 6, 12) & vbCrLf & vbCrLf
        Msg = Msg & "Class: " & Cells(r + 7, 12) & vbCrLf & vbCrLf
        Msg = Msg & "Dimensions: " & Cells(r + 8, 12) & vbCrLf & vbCrLf
        Msg = Msg & "Stackable: " & Cells(r + 9, 12) & vbCrLf & vbCrLf
        Msg = Msg & "Special Requirements: " & Cells(r + 10, 12) & vbCrLf & vbCrLf


'       Replace spaces with %20 (hex)
        Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
        Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")

'       Replace carriage returns with %0D%0A (hex)
        Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
'       Create the URL
        URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

'       Execute the URL (start the email client)
        ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

'       Wait two seconds before sending keystrokes
'       Application.Wait (Now + TimeValue("0:00:02"))

'       Application.SendKeys "%s"
    Next r
End Sub

Please help as I am still learning.

Where is a 255 character limit? On the message body? On the subject line? I don't understand (yet). — Ralph, Mar 02 '17 at 18:18
take a look : [http://stackoverflow.com/questions/2516702/getting-around-the-max-string-size-in-a-vba-function](http://stackoverflow.com/questions/2516702/getting-around-the-max-string-size-in-a-vba-function) — scraaappy, Mar 02 '17 at 18:20
Exactly which line is raising an error? VBA strings can be much longer than 255 characters, but cells in Excel can't. Don't use `WorksheetFunction.Substitute`, use `VBA.Strings.Replace` instead. — Mathieu Guindon, Mar 02 '17 at 18:22
Also consider working with the Outlook object model instead of using `ShellExecute` — Mathieu Guindon, Mar 02 '17 at 18:24
@scraaappy Oh, because the OP is using `Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")` instead of just `Subj = Replace(Subj, " ", "%20")`? — Ralph, Mar 02 '17 at 18:26
@Ralph that's what I'm thinking. Using worksheet functions for things that VBA does natively is never a good idea IMO. — Mathieu Guindon, Mar 02 '17 at 18:30
I can't get the email body to go past the Description: entry. It stops and won't continue with Hazmat(Yes or No):, etc. — Joshua House, Mar 02 '17 at 18:54

score 1 · Answer 1 · answered Mar 02 '17 at 18:30

If @scraaappy is right and your problem is merely due to the limitation of the workhsheet function Substitutethen you can easily avoid these problems by replacing the appropriate lines

    Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
    Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")

    Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")

by using the replace instead:

    Subj = Replace(Subj, " ", "%20")
    Msg = Replace(Msg, " ", "%20")

    Msg = Replace(Msg, vbCrLf, "%0D%0A")

Getting around the 255 Character count for creating an email in outlook

1 Answers1