Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Can't select a whole column of data for an email subject

0

Hello Everyone, 

I have been struggling for over a week with this macro and I know it is probably a pretty simple fix. I am a novice at best at using VBA but am trying to learn more. I wrote up a code to send me an email every time the file is opened want it to contain all of the values from column U. It will work if I have the range selected as just "U3" yet I cannot get it to return the entire column of data into the subject of the email. Using the code included below I have gotten the macro to selected the cells I need but I am unsure as how to define them as text to be included in the email. Here is the code I have written so far, I have changed the email address for security reasons.

Currently, this returns me a value of -1 for the subject of the email. 

Private Sub Workbook_Open()

MsgBox ("Check Outlook for list of projects requiring attention")

Dim emailApplication As Object
Dim emailItem As Object

Set emailApplication = CreateObject("Outlook.Application")
Set emailItem = emailApplication.CreateItem(0)

emailItem.To = "Email-1"

emailItem.Subject = "Daily Project Action Items"

emailItem.Body = Range("U2", Range("U2").End(xlDown)).Select


' Send the Email
emailItem.Send

Set emailItem = Nothing
Set emailApplication = Nothing

End Sub
Answer
Discuss

Answers

0
Selected Answer

You would have to convert the Excel data to HTML but since you seem to have a very simple list try the code below first.

Option Explicit

Private Sub Workbook_Open()

    Dim emailApplication    As Object
    Dim emailItem           As Object
    Dim Arr                 As Variant          ' array of column U
    Dim i                   As Integer          ' loop counter: Arr()

    Arr = Range("U2:U6").Value
    Set emailApplication = CreateObject("Outlook.Application")
    Set emailItem = emailApplication.CreateItem(0)

    With emailItem
        .To = "Email-1"
        .Subject = "Daily Project Action Items"
        For i = 1 To UBound(Arr)
            .Body = .Body & Format(i, "#0)    ") & Arr(i, 1)
        Next i
        .Display
'        .Send               ' Send the Email
    End With

    MsgBox "Check Outlook for list of projects requiring attention"
    Set emailItem = Nothing
    Set emailApplication = Nothing
End Sub

For a better rendering of the Excel extract follow the advice of Ron deBruin..

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login