Add Email Body Text - Excel/Outlook 2007

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,419
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
Code:
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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
961
Office Version
2007
Platform
Windows
You could try adding .Text to the body line as:

.Body = Range("Admin_EmailBody").Text
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,566
Office Version
365
Platform
Windows
What's the error?

How is the named range 'Admin_EmailBody' defined?
 

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,419
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

Well-known Member
Joined
Jul 9, 2009
Messages
1,816
Office Version
2013
Platform
Windows
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.....


Code:
Range("Admin_EmailSubject") '  & " - " & strMonth  - this works
Worksheets("Sheet1").Range("Admin_EmailSubject") & " - " & strMonth ' This works
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,100,142
Messages
5,472,754
Members
406,835
Latest member
steve43040

This Week's Hot Topics

Top