Outlook App Object in Excel: Not Working for Default Email Range Cells in Office 365
In this article, we will discuss the Outlook App Object in Excel and why it may not be working for default email range cells in Office 365. We will cover the key concepts related to this issue and provide detailed context to help you understand the problem better.
What is the Outlook App Object in Excel?
The Outlook App Object in Excel is a feature that allows users to automate Outlook tasks within Excel. This object can be used to send emails, manage contacts, and perform other Outlook functions directly from Excel. The Outlook App Object is a powerful tool that can save time and increase productivity for users who need to perform repetitive Outlook tasks.
Why is the Outlook App Object Not Working for Default Email Range Cells in Office 365?
There are several reasons why the Outlook App Object may not be working for default email range cells in Office 365. One common issue is that the default email range cells in Office 365 are not compatible with the Outlook App Object. This means that if you try to use the Outlook App Object to send an email with a default email range cell in the body, the email may not be sent or may contain errors.
Another reason why the Outlook App Object may not be working for default email range cells in Office 365 is that the Outlook App Object requires a specific format for the email body. If the default email range cells are not formatted correctly, the Outlook App Object may not be able to read the cells, and the email may not be sent or may contain errors.
How to Fix the Issue
To fix the issue with the Outlook App Object and default email range cells in Office 365, you can try the following steps:
- Check the format of the default email range cells. Make sure that the cells are formatted correctly and that there are no errors in the data.
- Use a specific range of cells instead of the default email range cells. This will ensure that the Outlook App Object can read the cells correctly and that the email is sent without errors.
- Use a code block to format the email body. This will allow you to specify the format of the email body and ensure that the default email range cells are formatted correctly.
Code Block Example
Here is an example of how to use a code block to format the email body and ensure that the default email range cells are formatted correctly:
Sub SendEmail()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set cell = Range("A1:B10")
With OutMail
.To = "[email protected]"
.CC = ""
.BCC = ""
.Subject = "Test Email"
.HTMLBody = RangetoHTML(cell)
.Display
End With
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Function RangetoHTML(rng As Range)
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook
TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
'Copy the range and create a new workbook to past the data in
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With
'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=TempWB.Sheets(1).Name, _
Source:=TempWB.Sheets(1).UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
'Create a reference to the htm file
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.readall
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
"align=left x:publishsource=")
'Close TempWB
TempWB.Close savechanges:=False
'Delete the htm file we used in this function
Kill TempFile
Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function
In this article, we discussed the Outlook App Object in Excel and why it may not be working for default email range cells in Office 365. We covered the key concepts related to this issue and provided detailed context to help you understand the problem better. We also provided a code block example to show how to format the email body and ensure that the default email range cells are formatted correctly.
References
- Microsoft Support: How to send email from Excel using Outlook automation
- Microsoft Developer Network: MailItem.HTMLBody property
- Stack Overflow: How to send email with Excel VBA without popping up outlook message box