CODEWITHSUNDEEP
excelvbaoutlook
Francis
Francis

Reputation: 121

Send Outlook email using text in an Excel textbox - Error 424: Object required

I am trying to send an email with Outlook using text in a textbox (I named it tx in Excel) as body.

When I run the code, there is an error on the line:

strbody = tx.Text

Error 424: Object required

Sub SendMail()

Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim strbody As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)

strbody = tx.Text

'On Error Resume Next
With OutMail
    .To = "..."
    .CC = ""
    .BCC = ""
    .Subject = Cells(3, 2)
    .Body = strbody

    .Send
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Upvotes: 0

Views: 1605

Answers (2)

R3uK
R3uK

Reputation: 14537

Replace Sheet's name by the name of the sheet where your textbox is
in strbody = ThisWorkBook.Sheets("Sheet's name").Shapes("tx").ControlFormat.Value

Sub SendMail()

Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim strbody As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)

strbody = ThisWorkBook.Sheets("Sheet's name").Shapes("tx").ControlFormat.Value

'On Error Resume Next
With OutMail
    .To = "..."
    .CC = ""
    .BCC = ""
    .Subject = Cells(3, 2)
    .Body = strbody

    .Send
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Upvotes: 1

Kevin
Kevin

Reputation: 2243

You could use CDO? Here's some quick VBA code I put in a test function in Excel VBA (with the email addresses and SMTP server address redacted):

Sub test()

    Dim strbody As String
    strbody = "Test Email" & vbNewLine & vbNewLine & "TEST EMAIL"

    Dim iMsg As Object
    Set iMsg = CreateObject("CDO.Message")

    With iMsg

        .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
            = "whateverYourSMTPServerIs"
        .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") _
            = 25
        .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") _
            = 2   'Stands for sending using CDO
        .Configuration.Fields.Update

        .To = "[email protected]"
        .CC = ""
        .BCC = ""
        .From = "[email protected]"
        .Subject = "Test Email"
        .TextBody = strbody
        .Send
    End With

End Sub

Upvotes: 0

Related Questions