none
VBA Compile Error - Sending emails from Excel RRS feed

  • Question

  • I am attempting to create a command button in Excel that will use some info from a sheet to generate and send an email.  The VBA code for the button is as follows:

    Sub CommandButton21_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 = "Subject"
     Email_Send_From = "*** Email address is removed for privacy ***"
     Email_Send_To = Sheet1.Cells(1, 2)
     Email_Cc = Sheet1.Cells(6, 2)
     Email_Bcc = Sheet1.Cells(7, 2)
     Email_Body = "Test body"
     On Error GoTo debugs
     Set Mail_Object = CreateObject("Outlook.Application")
     Set Mail_Single = Mail_Object.CreateItem(0)
     With Mail_Single
     .Subject = Email_Subject
     .From = Email_Send_From
     .To = Email_Send_To
     .CC = Email_Cc
     .BCC = Email_Bcc
     .Body = Email_Body
     End With
     debugs:
     If Err.Description <> "" Then MsgBox Err.Description
     End Sub

    When I try to run or compile, I get the error: "Object doesn't support this property or method".  With breaks added, it seems like the error is at the line:

    .To = Email_Send_To

    I have tried switching .To to .Recipients, but got the same error.  I'm really not understanding why this line would not compile, as .To is a property of MailItem.  I would really appreciate any help with fixing this code, or suggestions for other approaches to solving this problem.  I am very inexperienced with VBA; this code is not mine but came from a webpage explaining how to send email from Excel, with minor modifications.

    Thank you for reading~

    Wednesday, September 14, 2016 9:19 PM

Answers

  • Hi PSCJCastello,

    I test your code on my side and find some errors in it.

    first of all select the "Microsoft outlook 16.0 Object Library" from the References in the Tools Menu. if you did not selected it.

    No need to write ".From". The error is in this line.

    it will send mail from the Outlook account so no need to specify. it will directly take it.

    if you have more then one accounts active in Outlook and you want to send mail from particular account then you can do it with below mentioned line of code.

    .SendUsingAccount = OutApp.Session.Accounts.Item(1)
    

    also I noticed that you did not use ".Send" in your code. without using .send it will not going to send the mail.

    below is the modified code from the above mentioned code. I tested it and It is sending the mails.

    Sub CommandButton21_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 = "Subject"
     Email_Send_From = "Mail Address"
     Email_Send_To = "Mail Address"
     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
     '.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

    For testing purpose I directly pass the parameters and value in the macro. you can again change it and take the values from the excel sheet.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, September 15, 2016 11:27 AM
    Moderator

All replies

  • Hi,

    I notice that your issue is code related to send email from Excel by using Outlook application. To better resolve your issue, I will move your thread to Outlook for Developers forum for further troubleshooting. Hope you can find the solution there :)


    Regards,

    Winnie Liang


    Please remember to mark the replies as an answers if they help and unmark them if they provide no help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, September 15, 2016 3:04 AM
  • Hi PSCJCastello,

    I test your code on my side and find some errors in it.

    first of all select the "Microsoft outlook 16.0 Object Library" from the References in the Tools Menu. if you did not selected it.

    No need to write ".From". The error is in this line.

    it will send mail from the Outlook account so no need to specify. it will directly take it.

    if you have more then one accounts active in Outlook and you want to send mail from particular account then you can do it with below mentioned line of code.

    .SendUsingAccount = OutApp.Session.Accounts.Item(1)
    

    also I noticed that you did not use ".Send" in your code. without using .send it will not going to send the mail.

    below is the modified code from the above mentioned code. I tested it and It is sending the mails.

    Sub CommandButton21_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 = "Subject"
     Email_Send_From = "Mail Address"
     Email_Send_To = "Mail Address"
     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
     '.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

    For testing purpose I directly pass the parameters and value in the macro. you can again change it and take the values from the excel sheet.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, September 15, 2016 11:27 AM
    Moderator