glennon261
New Member
- Joined
- Sep 21, 2021
- Messages
- 3
- Office Version
- 2019
- 2016
- Platform
- Windows
I have been killing brain cells with this issue. I copied the routine from MR Excel video. My issue is when Set oWrdRng = oWrdDoc.Paragraphs.Add is executed I am returned an Error 13 Type Mismatch.
Sub sendemail()
'Declare Outlook Variables
Dim oLookApp As Outlook.Application
Dim oLookItm As Outlook.MailItem
Dim oLookIns As Outlook.Inspector
'Declare Word Variable
Dim oWrdDoc As Word.Document
Dim oWrdRng As Word.Range
'Declare Excel Variable
Dim ExcRng As Excel.Range
On Error Resume Next
'Get the Active instance of Outlook if the is one
Set oLookApp = GetObject(, "Outlook.application")
'if Outlook isnt open the create a new instance of Outlook
If Err.Number = 429 Then
Err.Clear
Set oLookApp = New Outlook.Application
End If
On Error GoTo ErrHand
'Create a new email
Set oLookItm = oLookApp.CreateItem(olMailItem)
Set ExcRng = Sheet7.Range("M2:U132")
With oLookItm
.SentOnBehalfOfName = "CapMkts@financeofamerica.com"
.To = "George@gmail.com"
.CC = ""
.Subject = "xxx Retail Pricing Report"
.Body = "ello," & "<br><br>" & "Please see comparisons below, full data is in the attached." & "<br><br>" & "Please let us know of any questions." & "<br><br>" & "Thank You."
.Attachments.Add Application.ActiveWorkbook.Path & "\IconPxReport_" & Format(Now, "yyyymmdd") & ".xlsm"
'Get the Active Inspector
Set oLookIns = .GetInspector
'Get the documant with the inspector
Set oWrdDoc = oLookIns.WordEditor
'Specify the range in the doc
Set oWrdRng = oWrdDoc.Application.ActiveDocument.Content
oWrdRng.Collapse Direction:=wdCollapseEnd
'Start New Paragraph
Set oWrdRng = oWrdDoc.Paragraphs.Add
oWrdRng.InsertBreak
ExcRng.Copy
oWrdRng.PasteSpecial DataType:=wdPasteHTML
.Display
'.send
End With
Exit Sub
ErrHand:
Debug.Print "ERROR: " & strError & "(" & Err.Number & ") " & Err.Description
Debug.Print "(" & Err.Number & ") " & Err.Description
Stop
Resume Next
Exit Sub
End Sub
Sub sendemail()
'Declare Outlook Variables
Dim oLookApp As Outlook.Application
Dim oLookItm As Outlook.MailItem
Dim oLookIns As Outlook.Inspector
'Declare Word Variable
Dim oWrdDoc As Word.Document
Dim oWrdRng As Word.Range
'Declare Excel Variable
Dim ExcRng As Excel.Range
On Error Resume Next
'Get the Active instance of Outlook if the is one
Set oLookApp = GetObject(, "Outlook.application")
'if Outlook isnt open the create a new instance of Outlook
If Err.Number = 429 Then
Err.Clear
Set oLookApp = New Outlook.Application
End If
On Error GoTo ErrHand
'Create a new email
Set oLookItm = oLookApp.CreateItem(olMailItem)
Set ExcRng = Sheet7.Range("M2:U132")
With oLookItm
.SentOnBehalfOfName = "CapMkts@financeofamerica.com"
.To = "George@gmail.com"
.CC = ""
.Subject = "xxx Retail Pricing Report"
.Body = "ello," & "<br><br>" & "Please see comparisons below, full data is in the attached." & "<br><br>" & "Please let us know of any questions." & "<br><br>" & "Thank You."
.Attachments.Add Application.ActiveWorkbook.Path & "\IconPxReport_" & Format(Now, "yyyymmdd") & ".xlsm"
'Get the Active Inspector
Set oLookIns = .GetInspector
'Get the documant with the inspector
Set oWrdDoc = oLookIns.WordEditor
'Specify the range in the doc
Set oWrdRng = oWrdDoc.Application.ActiveDocument.Content
oWrdRng.Collapse Direction:=wdCollapseEnd
'Start New Paragraph
Set oWrdRng = oWrdDoc.Paragraphs.Add
oWrdRng.InsertBreak
ExcRng.Copy
oWrdRng.PasteSpecial DataType:=wdPasteHTML
.Display
'.send
End With
Exit Sub
ErrHand:
Debug.Print "ERROR: " & strError & "(" & Err.Number & ") " & Err.Description
Debug.Print "(" & Err.Number & ") " & Err.Description
Stop
Resume Next
Exit Sub
End Sub