none
VBA Code won't send content in email RRS feed

  • Question

  • Hi, 

    I am using this code to try and send the content of 2 cells in a work book via email. I can get it to send the email with the title and the text in the body of the email, but I can't get it to include the contents of what I want from the worksheet. What I want from the worksheet is in sheet 2 and is the two boxes A1, B1. The code im using is here below. Can anyone help?


    Public Sub EmailProcedure()
     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 = "Open & Outstanding RFIs"
     Email_Send_From = "my email"
     Email_Send_To = "Mail Address"
     Email_Cc = ""
     Email_Bcc = ""
     Email_Body = "Outstanding RFIs summarised below"
     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 = "my email"
     .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, December 19, 2018 3:37 PM

Answers

  • The lines

     Email_Body = Email_Body = "Outstanding RFIs summarised below" & vbCrLf & _
        Worksheets("Sheet 2").Range("A1").Text & vbCrLf & _
        Worksheets("Sheet 2").Range("B1").Text

    should be

     Email_Body = "Outstanding RFIs summarised below" & vbCrLf & _
        Worksheets("Sheet 2").Range("A1").Text & vbCrLf & _
        Worksheets("Sheet 2").Range("B1").Text

    And since you're using late binding, I'd change olMailItem to 0 in the line

      Set Mail_Single = Mail_Object.CreateItem(olMailItem)

    With these changes, the code works for me. Are you sure that the sheet is named "Sheet 2" (with a space)?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Michael K13 Thursday, December 20, 2018 11:21 AM
    Thursday, December 20, 2018 9:39 AM

All replies

  • Change the line

    Email_Body = "Outstanding RFIs summarised below"

    to for example

    Email_Body = "Outstanding RFIs summarised below" & vbCrLf & _
        Worksheets("Sheet 2").Range("A1").Text & vbCrLf & _
        Worksheets("Sheet 2").Range("B1").Text


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, December 19, 2018 4:49 PM
  • Hi Hans, thanks for the reply.

    I am getting an out of range message now? This is the entire code I'm entering:

    Public Sub EmailProcedure()
     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 = "Open & Outstanding RFIs"
     Email_Send_From = "michael.kelly@careysplc.co.uk"
     Email_Send_To = "Mail Address"
     Email_Cc = ""
     Email_Bcc = ""
     Email_Body = "Outstanding RFIs summarised below" & vbCrLf & _
        Worksheets("Sheet 2").Range(A1).Text & vbCrLf & _
        Worksheets("Sheet 2").Range(B1).Text
     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 = "michael.kelly@careysplc.co.uk"
     .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

    The value in A1 is "outstanding RFI's" and B1 is the number count. This is on Sheet 2.

    Wednesday, December 19, 2018 5:50 PM
  • You forgot to put the cell addresses in quotes:

     Email_Body = "Outstanding RFIs summarised below" & vbCrLf & _
        Worksheets("Sheet 2").Range("A1").Text & vbCrLf & _
        Worksheets("Sheet 2").Range("B1").Text


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, December 19, 2018 7:43 PM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Thursday, December 20, 2018 2:00 AM
  • Hi Michael,

    Did Hans's answer resolved your problem?

    You can mark it as answer if it helped and please help us close the thread.

    Best Regards,

    Lina


    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.

    Thursday, December 20, 2018 6:17 AM
  • Hi Hans,

    I am still getting an out of range message, this is the complete code:

    Public Sub EmailProcedure()
     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 = "Open & Outstanding RFIs"
     Email_Send_From = "michael@careys.co.uk"
     Email_Send_To = "Mail Address"
     Email_Cc = ""
     Email_Bcc = ""
     Email_Body = Email_Body = "Outstanding RFIs summarised below" & vbCrLf & _
        Worksheets("Sheet 2").Range("A1").Text & vbCrLf & _
        Worksheets("Sheet 2").Range("B1").Text
     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 = "michael@careys.co.uk"
     .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

    Thursday, December 20, 2018 7:18 AM
  • The lines

     Email_Body = Email_Body = "Outstanding RFIs summarised below" & vbCrLf & _
        Worksheets("Sheet 2").Range("A1").Text & vbCrLf & _
        Worksheets("Sheet 2").Range("B1").Text

    should be

     Email_Body = "Outstanding RFIs summarised below" & vbCrLf & _
        Worksheets("Sheet 2").Range("A1").Text & vbCrLf & _
        Worksheets("Sheet 2").Range("B1").Text

    And since you're using late binding, I'd change olMailItem to 0 in the line

      Set Mail_Single = Mail_Object.CreateItem(olMailItem)

    With these changes, the code works for me. Are you sure that the sheet is named "Sheet 2" (with a space)?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Michael K13 Thursday, December 20, 2018 11:21 AM
    Thursday, December 20, 2018 9:39 AM
  • Hans, 

    That works for me now, the error was the space between Sheet & 2. Thank you.

    Thursday, December 20, 2018 11:21 AM