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


Board Regular
Mar 25, 2005
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.


'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
End With

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!


Well-known Member
Feb 17, 2003
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.

' 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
    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)
            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
    End If
End Sub
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back