Line break Using ShellExecute for email Possible??

[imaquila]

I've tried various methods vbCrLf, vbLf, Chr(10) & CHR(13), vbnewline
They work great in the immediate window but my email is one continuous line.

I'm really hoping someone has a solution because the only other alternative that I can think of is to try to write to a text file and then do a copy and paste into my email and I'm not at all certain that will work either (even if I had a clue how to pull all that off).

So here I am hoping someone has the answer.

 

[dk]

Hi

Can you post your current code - not really possible to help you without that

DK

 

[imaquila]

Thanks for taking the time to read my post. I suspected there would be a need but I didn't want to force a bunch of code that my scare off any potential help. As a compromise, I've stripped down the code to illustrate my problem. I very much hope that someone can find a way to force a line break with in the email. As I said earlier, any of these methods work with in my message box but none work in the email.

I should point out that my spacers also do not work in the email but I have not explored any other alternatives for spacing yet. It won't matter if I can't manage a line break. Also, I do NOT use OutLook which is why I opted to use ShellExecute to activate my default email program which is Thunderbird. Thanks again for any discoveries you can share to solve my probem.

This first bit of code is in a UserForm with a button to activate routine.

Private Sub CommandButton1_Click()
Dim SendTo As String
Dim Body As String
Dim spacer As String
Dim CC As String
Dim BCC As String

SendTo = "email@yahoo.com"
spacer = "    "
Body = "Sales Report for dates between 3/31/12 and 4/7/12" & vbCrLf
Body = Body & "Total Gross Sales: This much " & spacer & "Commission paid: This much" & spacer & "Total Net: This much" & vbCrLf
Body = Body & "Inv 3000" & spacer & "Item number" & spacer & "Quatity " & spacer & "$53.00" & "Total: $53.00" & vbNewLine
Body = Body & "Inv 3001" & spacer & "Item number" & spacer & "Quatity " & spacer & "$79.00" & "Total: $79.00" & Chr(10) & Chr(13)
Body = Body & "Inv 3002" & spacer & "Item number" & spacer & "Quatity " & spacer & "$50.00" & "Total: $100.00" & Chr(34)
ShellExecute 0, vbNullString, "mailto:" & SendTo & "?subject=" & "Sales Report" & _
"&body=" & Body & "&CC=" & CC & "&BCC=" & BCC, 0&, 0&, 1
'Application.Wait (Now + TimeValue("0:00:05"))
'Application.SendKeys "%s"   'sends message
debugs:
If Err.Description <> "" Then MsgBox (Err.Description)
MsgBox (Body)
End Sub

This second bit is to access the API ShellExecute and resides in a module.

Public 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

 

[dk]

Hi

I tried using the HTML equivalent of a new line and it seemed to work. I also used the same technique to get your "spacer" to work. See how you get on with this code. I haven't tried but you might be able to get an HTML table to work (this would allow your columns to align nicely).

HTH
DK

Sub CommandButton1_Click()
    Dim SendTo As String
    Dim Body As String
    Dim CC As String
    Dim BCC As String
    Dim sCommand As String

    Const NEWLINE As String = "%0D%0A"
    Const SPACER = "%20%20%20%20"


    SendTo = "email@yahoo.com"

    Body = "Sales Report for dates between 3/31/12 and 4/7/12" & NEWLINE
    Body = Body & "Total Gross Sales: This much " & SPACER & "Commission paid: This much" & SPACER & "Total Net: This much" & NEWLINE
    Body = Body & "Inv 3000" & SPACER & "Item number" & SPACER & "Quatity " & SPACER & "$53.00" & "Total: $53.00" & NEWLINE
    Body = Body & "Inv 3001" & SPACER & "Item number" & SPACER & "Quatity " & SPACER & "$79.00" & "Total: $79.00" & Chr(10) & NEWLINE
    Body = Body & "Inv 3002" & SPACER & "Item number" & SPACER & "Quatity " & SPACER & "$50.00" & "Total: $100.00"

    Body = Replace(Body, " ", "%20")



    ShellExecute 0, vbNullString, "mailto:" & SendTo & "?subject=" & "Sales Report" & _
                                  "&body=" & Body & "&CC=" & CC & "&BCC=" & BCC, 0&, 0&, 1
    'Application.Wait (Now + TimeValue("0:00:05"))
    'Application.SendKeys "%s"   'sends message
debugs:
    If Err.Description <> "" Then MsgBox (Err.Description)
    MsgBox (Body)
End Sub

 

[dk]

I should add, I tested this and it worked fine using Outlook. I have never even seen Thunderbird so have no idea if this technique will work...

 

[imaquila]

This is FANTASTIC!!! I have a little experience with HTML but NO experience using %0D%0A in HTML I'd typically just use <br> where I wanted to force new line and I'd use   to force a space. I'd very much like to learn more about how to use an HTML table within the email as you suggested. I'm familiar with <TABLE><TR><TD>....</TD></TR></TABLE> but I suspect that like your magic "%0D%0A" and "%20", there's more to it than what I'm familiar with. Is there a name for those special codes (that I could research online)?

THANK YOU SO MUCH!!! I was researching my alternative the text file but I don't think that would have worked for the same reasons.

You're a HERO!!!

 

[dk]

Hi mate

Sorry, I would have given a better explanation earlier but was short on time when I replied.

Although the numbers appear to be magic they are actually quite simple. In HTML you can use character codes in place of their actual values. For example, rather than using a space character you can use its ASCII numeric equivalent - the ASCII code for a space is 32 in decimal, which is 20 in hexadecimal and you specify hex values in the format %xx (where x is your hex ASCII code). Similarly, the string %0D%0A are the numbers 13 and 10 in decimal (you will probably recognise these as the ASCII codes for carriage return and line feed - vbCrLf in VB/VBA).

Anyway, hope that makes sense. Regarding the table thing I mentioned - I just tried it and it doesn't seem to like it using Gmail - the below might work for you or it might not - I'm not really sure exactly what the limitations are of the mailto command.

Sub CommandButton1_Click()
    Dim SendTo As String
    Dim Body As String
    Dim CC As String
    Dim BCC As String
    Dim sCommand As String

    SendTo = "email@yahoo.com"

    Body = "<table><tr><td>State</td><td>Revenue</td><td>Profit</td></tr>" & _
           "<tr><td>NSW</td><td>100,000</td><td>24,000</td></tr>" & _
           "<tr><td>VIC</td><td>85,000</td><td>18,500</td></tr>" & _
           "<tr><td>WA</td><td>69,000</td><td>11,200</td></tr>" & _
           "</table>"


    ShellExecute 0, vbNullString, "mailto:" & SendTo & "?subject=" & "Sales Report" & _
                                  "&body=" & Body & "&CC=" & CC & "&BCC=" & BCC, 0&, 0&, 1
    'Application.Wait (Now + TimeValue("0:00:05"))
    'Application.SendKeys "%s"   'sends message
debugs:
    If Err.Description <> "" Then MsgBox (Err.Description)
    MsgBox (Body)
End Sub

HTH
DK

 

[imaquila]

Thanks! That was a very good explanation even though Thunderbird did not swallow the Table idea either. I guess it prefers formatting of any kind to be strictly in HEX. A valuable lesson and one I would never have thought to even try without your guidance.

Thanks again for your kind assistance!