VBA: Outlook email - cannot get .body to post var

[jsolomon]

Hi guys!
Here's my problem: The msg variable is not posting to the email window. So the body is blank whenever i send it. I pasted my code below. Any help is appreciated.

Joe

'Dimension all variables
Dim rngAddy As Range, cel As Range
Dim strAddy As String
Dim OLApp As Object, OLMsg As Object
Dim Msg As String
Dim ContactName As Range
Dim InvoiceDate As Range

'Set all variables
Set ContactName = ActiveWorkbook.Sheets("Customer Info").Range(b16)
Set InvoiceDate = ActiveWorkbook.Sheets("Invoice").Range(k3)
Set OLApp = CreateObject("Outlook.Application")
Set OLMsg = OLApp.CreateItem(0)
Set rngAddy = ActiveWorkbook.Sheets("Customer Info").Range("B11")
***************** Msg = "Dear " & ContactName.Value & ", Thank you for choosing CCE Services for your cable and telecommunication needs. Attached you will find the latest aging statement listing all past invoices, respective dates due, and other information. If you have incurred any billings throughout week ending" & InvoiceDate.Value & ", you will find those invoices attached as well. If you have any questions or concerns, please feel free to contact our accounting department."

'Set Outlook message details
With OLMsg
.To = strAddy
.Subject = "Aging Report and New Invoices"
.Attachments.Add ActiveWorkbook.FullName
**************** .Body = Msg
.Display
End With

 

[BrianB]

It looks like you have come across the maximum string limit of 256 characters. (Which may be larger in later versions of Excel).

To overcome this I put my message into a (Drawing Toolbar) Textbox in a worksheet and transfer it to the email body 250 characters at a time.

Actually this is very convenient because the message can be changed very easily.

You will note that for email purposes this is partial code only. Looks like you want to change the message with code. I see no problem with this - the method of changing the textbox content is implied in the code below, or you can record a macro and amend it - or do something fancy with my textblocks.

'=========================================================================
'-   SPLIT TEXTBOX TEXT INTO 250 CHARACTER BLOCKS FOR EMAIL BODY
' NB. For email purposes this is partial code only
'     For test purposes use the Worksheet alternative
'=========================================================================
Sub bodytext()
    Dim BodyTextBox As Object
    Dim NumChar As Long
    Dim NumBlocks As Double
    Dim TextBlock As String
    '----------------------------------------------------------------------
    'This is in the main declarations
'    Set MyMail = CreateObject("Outlook.application")
'    Set MyItem = MyMail.createitem(olmailitem)
'    Set MyAttachments = MyItem.attachments
'    Set MyRecipients = MyItem.recipients
    '-----------------------------------------------------------------------
    '- text box in worksheet
    Worksheets("Main").Activate
    Set BodyTextBox = ActiveSheet.Shapes("TextBox1").TextFrame
    '-----------------------------------------------------------------------
    '- break textbox text into 250 character blocks
    NumChar = BodyTextBox.Characters.Count
    If NumChar > 0 Then
        '- calculate number of 250 character blocks
        NumBlocks = NumChar / 250
        If NumBlocks - Int(NumBlocks) = 0 Then
            NumBlocks = Int(NumBlocks)
        Else
            NumBlocks = Int(NumBlocks) + 1
        End If
        '---------------------------------------------------------------------
        '- put textbox contents into the body a block a time
        s = 1
        For b = 1 To NumBlocks
            TextBlock = BodyTextBox.Characters(start:=s, Length:=250).Text
            '----------------------------------------------------------------
            '- ALTERNATIVE 1 : Make the mail body
'            MyItem.body = MyItem.body & TextBlock
            '-----------------------------------------------------------------
            ' ALTERNATIVE 2 : to WorkSheets("Test") to test the code
            Worksheets("Test").Cells(b, 1).Value = TextBlock
            '-----------------------------------------------------------------
            s = s + 250
        Next
    End If
End Sub