Emailing from excel with some problems...

[conradcliff]

Hey guys, so..I'm trying to figure out how I can send a simple email from excel with he body consisting of certain cell values.

I got some code from rondebruin.nl but when I took the suggestions on how to tweak the message body it breaks the code. here's what I have:

Sub Mail_small_Text_Outlook()
'Working in Office 2000-2010
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

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

    With ThisWorkbook.Sheets("PrintableInvoice")
        strbody = "Hi there" & vbNewLine & vbNewLine & _
                  .Range("ThisInvoiceEmailInfo")
    End With

    On Error Resume Next
    With OutMail
        .To = "cliff@mydomain.com"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

I get:

run-time error '429':
ActiveX component can't create object

it then highlights the "With ThisWorkbook.Sheets("PrintableInvoice")" line of code and when I mouse over that it tells me that < object variable or With block variable not set>

I've looked these things up but can seem to figure it out ...any help would be much appreciated

 

[Zack Barresse]

If any of the forementioned items do not work, you should try using Early Binding (instead of using Late Binding as you are now). Make the following reference from your VBE: Microsoft Outlook 12.0 Object Library (note the "12.0" is for Outlook 2007, and if you are not using that version the number will be different). Then use the following rendition of code...


<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> Mail_small_Text_Outlook()<br><br>    <SPAN style="color:#00007F">Dim</SPAN> OutApp <SPAN style="color:#00007F">As</SPAN> Outlook.Application, OutMail <SPAN style="color:#00007F">As</SPAN> Outlook.MailItem<br>    <SPAN style="color:#00007F">Dim</SPAN> strbody <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, blnCreated <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> OutApp = GetObject(, "Outlook.Application")<br>    <SPAN style="color:#00007F">If</SPAN> OutApp <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> OutApp = CreateObject("Outlook.Application")<br>        blnCreated = <SPAN style="color:#00007F">True</SPAN><br>        Err.Clear<br>    <SPAN style="color:#00007F">Else</SPAN><br>        blnCreated = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br><br>    <SPAN style="color:#00007F">Set</SPAN> OutMail = OutApp.CreateItem(0)<br><br>    <SPAN style="color:#00007F">With</SPAN> ThisWorkbook.Sheets("PrintableInvoice")<br>        strbody = "Hi there" & vbNewLine & vbNewLine & .Range("ThisInvoiceEmailInfo")<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> OutMail<br>        .To = "cliff@mydomain.com"<br>        .CC = ""<br>        .BCC = ""<br>        .Subject = "This is the <SPAN style="color:#00007F">Sub</SPAN>ject line"<br>        .Body = strbody<br><SPAN style="color:#007F00">'        .Send</SPAN><br>        .Display<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br><br>    <SPAN style="color:#00007F">If</SPAN> blnCreated = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN> OutApp.Quit<br>    <br><SPAN style="color:#00007F">End</SPAN> Sub<br></FONT>


HTH

 

[conradcliff]

Thanks for all the great replies everyone! So sorry it took me so long to get back to you, I've been all over the place lately.

So as it turns out, I fixed the problem but I can't remember what it was that I did..I think it was something really simple and stupid.

Sorry again for wasting anyone's time..