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?

 

[RoryA]

That can't be the actual code since it would error - it should be Worksheets not Worksheet. Also you should only specify one cell.

 

[JEC]

If you want to use the whole range to be used as subject, you need a join like:

.Subject = "Spreadsheet for " & Join(Application.Transpose(Application.Transpose(Sheets("Risk Assessment (S1)").Range("C8:I8").Value)))

 

[anoctuawes]

That can't be the actual code since it would error - it should be Worksheets not Worksheet. Also you should only specify one cell.

I tried both to no avail (weirdly don’t recall it erroring though?).

If the cell is merged how can I reference it then? Reference the first one C8? (Which I think I tried but will try again)

 

[JEC]

That could work, but you shouldn't use merged cells when using VBA. Merged cells will throw errors.

 

[anoctuawes]

If you want to use the whole range to be used as subject, you need a join like:

.Subject = "Spreadsheet for " & Join(Application.Transpose(Application.Transpose(Sheets("Risk Assessment (S1)").Range("C8:I8").Value)))

This did not work either sadly, it still returns as blank.

 

[anoctuawes]

Here's the whole thing:

Sub Mail_workbook_Outlook()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "email.com"
.CC = ""
.BCC = ""
.Subject = "Spreadsheet for " & Join(Application.Transpose(Application.Transpose(Sheets("Risk Assessment (S1)").Range("C8:I8").Value)))
.Body = "Hi there this is the checklist for TEST"
.Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[anoctuawes]

@RoryA looks like it's not throwing an error mid-execution but if I go back into the macro after it displays red. So you're right, it didn't work.

 

[RoryA]

It would just be:

.Subject = "Spreadsheet for " & Sheets("Risk Assessment (S1)").Range("C8").Value)

If that returns just "Spreadsheet for " as the subject, then you must be looking at the wrong cell/sheet.

 

[anoctuawes]

S

It would just be:

.Subject = "Spreadsheet for " & Sheets("Risk Assessment (S1)").Range("C8").Value)

If that returns just "Spreadsheet for " as the subject, then you must be looking at the wrong cell/sheet.

Strange... I just renamed the sheet (it was Sheet 1 before becoming the Risk Assessment (S1)) and that still does not work, so it's definitely not the sheet name as they are a match. And it's without a doubt in cell C8:IB.....

I have tried your formula above and came back to an error (I don't believe there should be a bracket on the end?) so removed that and alas, it's still returning blank