Help with VBA Email Selection from Excel 2010 using Outlook 2010 - Error 430 - Automation not supported

[peejaygee]

Hey Guys,

I have an excel spreadsheet setup, it copies data from another sheet, sets everything up, highlights a section of an active sheet and then it is supposed to email a list of people the data.

It was working for the past week or two, all of a sudden it has stopped, I don't know if Windows has done an update, or Office has done an update, or the fact of installing Lync 2010 is causing the problem (which I've since uninstalled). I am using Outlook 2010, and I'm using Excel 2010 and when I run it, mail envelope lines appear, ready for the To: CC: and Subject (which just currently fills with the spreadsheet name before it error's out)

Using the code of

ActiveSheet.Range("J35:K44").Select


ActiveWorkbook.EnvelopeVisible = True

With ActiveSheet.MailEnvelope <----------------'This part here errors out.

.Item.To = "email@address.com"
.Item.Subject = "This is my Subject Line"
.Item.Send

End With

ActiveWorkbook.EnvelopeVisible = False

ActiveSheet.Range("J35:K44").Select

Selection.Copy

But when I run the macro I get the error 'Run-time error '430':Class does not support Automation or does not support expected interface'

I have a funny feeling it is because an ADO is conflicting, but I don't know which one is causing the problem, I've tried all various ones in the 'References'. I was going to upload a screenshot of the list available to me, but I can't find any quick place online to store my picture temporarily.

Can anybody help me and/or point me in the right direction.
Regards

 

[blakec4]

I have just recently come across this same error. My scenario is the same and stumped. This code was working daily for months. Our enterprise upgraded to Skype for Business. I cannot uninstall that update. Is there a work-around?

Hey Guys,

I have an excel spreadsheet setup, it copies data from another sheet, sets everything up, highlights a section of an active sheet and then it is supposed to email a list of people the data.

It was working for the past week or two, all of a sudden it has stopped, I don't know if Windows has done an update, or Office has done an update, or the fact of installing Lync 2010 is causing the problem (which I've since uninstalled). I am using Outlook 2010, and I'm using Excel 2010 and when I run it, mail envelope lines appear, ready for the To: CC: and Subject (which just currently fills with the spreadsheet name before it error's out)

Using the code of

ActiveSheet.Range("J35:K44").Select


ActiveWorkbook.EnvelopeVisible = True

With ActiveSheet.MailEnvelope <----------------'This part here errors out.

.Item.To = "email@address.com"
.Item.Subject = "This is my Subject Line"
.Item.Send

End With

ActiveWorkbook.EnvelopeVisible = False

ActiveSheet.Range("J35:K44").Select

Selection.Copy

But when I run the macro I get the error 'Run-time error '430':Class does not support Automation or does not support expected interface'

I have a funny feeling it is because an ADO is conflicting, but I don't know which one is causing the problem, I've tried all various ones in the 'References'. I was going to upload a screenshot of the list available to me, but I can't find any quick place online to store my picture temporarily.

Can anybody help me and/or point me in the right direction.
Regards