Issue with reading data from specific cells in Excel VBA

Question

I'm attempting to send an email containing an Excel workbook from within the document using the built in VB macros. There is data in one of the sheets, which are relevant to sending the email (Subject, recipient etc). I am trying to access these using the Sheets object like so

Sub Button1_Click()
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    Dim cell As Object
    Dim count As Integer

    count = 0
    For Each cell In Selection
        count = count + 1
    Next cell
    If count <> 1 Then
        MsgBox ("You must select exactly one cell, which shall be the e-mail address of the recipient")
        Wscript.Quit
    Else
        recipient = ActiveCell.Value
    End If

    On Error Resume Next
    With OutMail
        .To = recipient
        .CC = ""
        .BCC = ""
        .SentOnBehalfOfName = This.Sheets("MailContent").Range("A2").Value
        .Subject = This.Sheets("MailContent").Range("A4").Value
        .Body = This.Sheets("MailContent").Range("A6").Value & vbNewLine & This.Sheets("MailContent").Range("A7") & vbNewLine & vbNewLine & "Næste gang senest den " + This.Sheets("MailContent").Range("A10") & vbNewLine & vbNewLine & This.Sheets("MailContent").Range("A8")
        .Attachments.Add ActiveWorkbook.Name
        .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

I've also been able to replicate the same error with this small snippet

Sub Button1_Click()
    Dim subjectCell As Range
    subjectCell = This.Sheets("MailContent").Range("A2")
    MsgBox (subjectCell.Value)
End Sub

I've tried using WorkSheets, Sheets, ActiveWorkbook to access the cells, but I'm sure it's just a problem of how I assign the data, since I'm not used to languages with syntax like VB. Any help is much appreciated, and if you need more info leave me a comment.

Stef Joynson Stef Joynson · Accepted Answer · 2015-03-31T10:42:11

You need to use the 'Set' keyword to assign to a range.

Set subjectCell = ThisWorkbook.Sheets("MailContent").Range("A2")

This still catches me out on an irritatingly regular basis.

Issue with reading data from specific cells in Excel VBA

1 Answers