Using Excel and Outlook 2007 I want to create an emailmessage with a Macro.
But I am running into an error I can not find any "comprehensible" documentation about anywhere online.
The error is a runtime error and has error code -2147467259 (80004005)
The error message displayed
(translated from dutch so might not be the exact english error message)
= "The lower limit of the matrix should be zero."
The error occurs at the line: ".Body = Copyrng"
If I replace "Copyrng" in this line with "" the code works properly, so it has something to do with the range I selected, or not?
What is the solution?
Here is my code:
Private Sub Copy()
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.ReferenceStyle = xlA1
Dim KeyCells As Range
Dim Chainrng As Range
Dim PriceRng As Range
Dim Copyrng As Range
lastrow = Range("A65536").End(xlUp).Row
Set KeyCells = Range("A1:J30")
Set Chainrng = Range("C:C")
Set PriceRng = Range("D:D")
Set Copyrng = Range("A2:J" & lastrow)
'Autofit column widths
PriceRng.ColumnWidth = "10.14"
Chainrng.ColumnWidth = "11.57"
'Create Mail Item and view before sending
Dim OutApp As Object
Dim OutMail As Object
Dim Mailrng As Range
Set Mailrng = Selection
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
.To = "Recipient"
.CC = ""
.BCC = ""
.Subject = "Subject"
.Body = Copyrng
Set OutMail = Nothing
Set OutApp = Nothing
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.ReferenceStyle = xlR1C1