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

 

[Claire Watts]

Not sure if this will help, I use it to email a copy of my workbook as an attachment

'write the default Outlook contact name list to the active worksheet

Dim OlApp As Object
Dim OlMail As Object
Dim ToRecipient As Variant
Dim CcRecipient As Variant

Set OlApp = CreateObject("Outlook.Application")
Set OlMail = OlApp.createitem(olmailitem)

For Each ToRecipient In Array("")
'OlMail.Recipients.Add ToRecipient
Next ToRecipient

For Each CcRecipient In Array("")
'With OlMail.Recipients.Add(CcRecipient)
'.Type = olCC
'End With
Next CcRecipient

'fill in Subject field
OlMail.Subject = "Put in the name of your workbook"

'Add the active workbook as an attachment
OlMail.Attachments.Add ActiveWorkbook.FullName

'Display the message
OlMail.Display 'change this to OlMail.Send if you just want to send it without previewing it

 

[conradcliff]

Thanks for the reply, I'm really just trying to get a plain text email right now as it will be sent to mobile phones as well. I will probably utilize that in the future though.

 

[Zack Barresse]

Hi there,

You might be having trouble by just creating a bunch of instances of Outlook. I would first close Outlook, then open up your task manager and end every Outlook.exe process you see. Then use this code and try again...

Sub Mail_small_Text_Outlook()

    Dim OutApp As Object, OutMail As Object
    Dim strbody As String, blnCreated As Boolean
    
    On Error Resume Next
    Set OutApp = GetObject(, "Outlook.Application")
    If OutApp Is Nothing Then
        Set OutApp = CreateObject("Outlook.Application")
        blnCreated = True
        Err.Clear
    Else
        blnCreated = False
    End If
    On Error GoTo 0

    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
'        .Send
        .Display
    End With
    On Error GoTo 0

    If blnCreated = True Then OutApp.Quit
    
End Sub

Let us know how it goes.

 

[ZVI]

Hi,
Recommendations:
1. Make sure that there are no MISSING references in VBE-Tools-References
2. Make sure that your project can be compiled at all, try VBE - Run - Compile VBAProject. In not successful then fix all highlighted lines of code step by step.
3. If still does not work then check this:

Sub Test()
  
  ' "PrintableInvoice" is expected
  Debug.Print Sheet9811.Name
  ' If debugger eats this line then you can use Sheet9811 instead of ThisWorkbook.Sheets("PrintableInvoice")
  
  ' Compare sheet name
  Debug.Print Sheet9811.Name = "PrintableInvoice"
  ' if False then copy-paste result of the 1st line above (sheet name) into the code
  
End Sub

Regards,
Vladimir

 

[Zack Barresse]

Vladimir,

From the picture the OP posted, the name is valid, and does equate to what he has posted. I highly doubt it is a sheet naming convention, but rather an application instance issue. Good call on the MISSING reference.

 

[ZVI]

Vladimir,

From the picture the OP posted, the name is valid, and does equate to what he has posted. I highly doubt it is a sheet naming convention, but rather an application instance issue. Good call on the MISSING reference.

Hi Zack,

Surely I saw the picture
But there could be foreign symbols in "PrintableInvoice" which looks exactly as the English one but with different code, therefore it's not the same.
For example, in Russian alphabet there are letters visually exactly as P,a,e,o,c which are present in "PrintableInvoice", but code of them are different.

Vladimir

 

[Zack Barresse]

But there could be foreign symbols in "PrintableInvoice" which looks exactly as the English one but with different code, therefore it's not the same.
For example, in Russian alphabet there are letters visually exactly as P,a,e,o,c which are present in "PrintableInvoice", but code of them are different.

Good to know! Thank you!! Learn something new everyday here.

 

[ZVI]

Learn something new everyday here

Me too

 

[daverunt]

Check that circle thing with 4 squares in the top left corner of the Excel sheet.
Select Excel Options
Trust Centre
Trust Cetre settings

Is the Active X problem the selection there?

 

[daverunt]

I don't get this problem when I create the relevant sheet. It fails on the strbody code probably because the sheet doesn't have the right info.

Look at the mail here: Try adapting that and see if you get the same problem
http://www.mrexcel.com/forum/showthread.php?t=462976

Also I would try

Set WsRef = ThisWorkbook.Sheets("PrintableInvoice")
 
With WsRef
        strbody = "Hi there" .................................
    End With