Subject returning blank when referencing cell in macro

[anoctuawes]

Hi all,

I've been googling for hours on end and can't see to find a solution that works.

I have a macro that saves and send an email and I want the subject line to include data from one of the worksheets within the book.

This is what I have currently, but when executed the area no data is returned:

With OutMail
.To = "email.com"
.CC = ""
.BCC = ""
.Subject = "Spreadsheet for " & Worksheet("Risk Assessment (S1)").Range("C8:I8").Value

"Spreadsheet for " is the text
Risk Assessment (S1) is the name of the tab the cell is contained within
The cell contains text - I have also tried ".text" but this returns blank too.

I thought perhaps I should be calling it "Sheet1" instead of the name of the tab, but Sheet1 does not work
I read different suggestions of "Worksheet" vs "Worksheets" and tried both, but neither worked.
I tried referencing just the first cell (it is a merged cell), so that being C8, and this did not work

I am not getting any errors, the subject simply keeps displaying as, "Spreadsheet for "

What am I doing wrong?

 

[anoctuawes]

So the code that I was looking at was sitting as an object, but it is the module that needed changing. Clearly I am pretty new to this. So why would a spreadsheet have macros running as objects vs modules? i.e. each page of this sheet has a button, that clears the form but I can see that the code for each is sitting as an "object", not modules.

 

[RoryA]

There are different kinds of modules but anything with code in it is a module.

Are you sure C8:I8 is actually merged and not just centred across the selection?