Add Email Body Text - Excel/Outlook 2007

[mikeymay]

I have trhe following code to create an email, add a subject, body and attachment but the line to add the body text is causing an error

Set objMail = objOutlook.CreateItem(0)
      
With objMail
   .To = strEmail
   .Subject = Range("Admin_EmailSubject") & " - " & strMonth
   .Body = Range("Admin_EmailBody")
   .Attachments.Add ActiveWorkbook.Path & "\Client Outputs\" & strClient & " " & strMonth & ".pdf"
   .Send
End With

If I ignore the .Body line, the .Send executes with no problem.

Not entirely sure why this doesn't work as I can't find anything online other than what I have and this works in Excel 2016.

TIA

 

[jmacleary]

You could try adding .Text to the body line as:

.Body = Range("Admin_EmailBody").Text

 

[Norie]

What's the error?

How is the named range 'Admin_EmailBody' defined?

 

[mikeymay]

Unsure of the error code as it was on my clients machine who uses Office 2007 and I don't get the error when I run via Excel 2007 but email via Outlook 2016. I will add the .Text and see if that fixes otherwise I will report back with the error code and description.

The text is defined as a Worksheet named range. I've never had an issue with referencing a named range before though.

 

[daverunt]

I think when you are inside a With Block referencing an email object you would need to qualify the worksheet the range is on.

Correction: The issue appears to be the added month detail. With that commented out - it works.
With the Worksheet added and the month detail added it works.

So maybe someone can explain that to me.....

Range("Admin_EmailSubject") '  & " - " & strMonth  - this works
Worksheets("Sheet1").Range("Admin_EmailSubject") & " - " & strMonth ' This works

 

[mikeymay]

I'll give that a go! Cheers