none
email from Excel prolem with code RRS feed

  • Question

  • Found this code on forum but when I run it I get an error saying:

    activeX control cannot create object - any thoughts?

    thanks,

    Doug

    Sub CommandButton35_Click()
     Dim Email_Subject, Email_Send_From, Email_Send_To, Email_Cc, Email_Bcc, Email_Body As String
     Dim Mail_Object, Mail_Single As Variant
     Email_Subject = TextBox103.Value
     Email_Send_From = "***.***@hotmail.com" 'blanked for privacy
     Email_Send_To = TextBox101.Value
     Email_Cc = ""
     Email_Bcc = ""
     Email_Body = TextBox104.Value
     On Error GoTo debugs
     Set Mail_Object = CreateObject("Outlook.Application")
     Set Mail_Single = Mail_Object.CreateItem(olMailItem)
     With Mail_Single
     .Subject = Email_Subject
     .From = Email_Send_From
     .To = Email_Send_To
     .CC = Email_Cc
     .BCC = Email_Bcc
     .Body = Email_Body
     .Send
     End With
     MsgBox ("Mail Sent")
    debugs:
     If Err.Description <> "" Then MsgBox Err.Description

    End Sub
    Wednesday, February 8, 2017 7:20 PM

Answers

  • Hi 6da4,

    it looks like you found this code from link below.

    VBA Compile Error - Sending emails from Excel

    there is error in code. that code was edited by me.

    you do not need to pass parameter mentioned below.

    .From = Email_Send_From

    below is modified code which is working.

    Sub demo2()
    Dim Email_Subject, Email_Send_From, Email_Send_To, Email_Cc, Email_Bcc, Email_Body As String
     Dim Mail_Object, Mail_Single As Variant
     Email_Subject = "Subject"
     Email_Send_To = "abc@xyz.com"
     Email_Cc = ""
     Email_Bcc = ""
     Email_Body = "Test body"
     On Error GoTo debugs
     Set Mail_Object = CreateObject("Outlook.Application")
     Set Mail_Single = Mail_Object.CreateItem(olMailItem)
     With Mail_Single
     .Subject = Email_Subject
     .To = Email_Send_To
     .CC = Email_Cc
     .BCC = Email_Bcc
     .Body = Email_Body
     .Send
     End With
     MsgBox ("Mail Sent")
    debugs:
     If Err.Description <> "" Then MsgBox Err.Description
    
    
    End Sub

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 9, 2017 1:53 AM
    Moderator

All replies

  • What line of code is throwing the error?


    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com. For any reply that either helps to answer your question or is the answer, please mark it as helpful or as the answer so others with the same question will have an answer quickly.

    Wednesday, February 8, 2017 11:55 PM
  • Hi 6da4,

    it looks like you found this code from link below.

    VBA Compile Error - Sending emails from Excel

    there is error in code. that code was edited by me.

    you do not need to pass parameter mentioned below.

    .From = Email_Send_From

    below is modified code which is working.

    Sub demo2()
    Dim Email_Subject, Email_Send_From, Email_Send_To, Email_Cc, Email_Bcc, Email_Body As String
     Dim Mail_Object, Mail_Single As Variant
     Email_Subject = "Subject"
     Email_Send_To = "abc@xyz.com"
     Email_Cc = ""
     Email_Bcc = ""
     Email_Body = "Test body"
     On Error GoTo debugs
     Set Mail_Object = CreateObject("Outlook.Application")
     Set Mail_Single = Mail_Object.CreateItem(olMailItem)
     With Mail_Single
     .Subject = Email_Subject
     .To = Email_Send_To
     .CC = Email_Cc
     .BCC = Email_Bcc
     .Body = Email_Body
     .Send
     End With
     MsgBox ("Mail Sent")
    debugs:
     If Err.Description <> "" Then MsgBox Err.Description
    
    
    End Sub

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 9, 2017 1:53 AM
    Moderator
  • thanks for the tip on the code change Deepak!

    I believe my problem stems from the fact that I don't have Outlook installed...new laptop!!!

    will install and try again.

    marking as answered but will post if problem continues.

    thanks,

    Doug

    Thursday, February 9, 2017 6:18 PM