Hey guys, I'm trying to build a macro to email a range of cells of at least C3:c150, but perfect world would be to email a range of c3:e150.
Here's the code I have so far:
But it is telling me that "The source is not a range or the sheet is protected. Please correct and try again."
The sheet is not protected in any way, and I feel I need to change the code in bold, but I'm not familiar enough with the options and syntax.
Anyone have any advice, direction or changes?
Thanks so much in advance!
Here's the code I have so far:
Rich (BB code):
Sub SendEmail2()
Dim Source As Range
Dim OutlookApp As Object
Dim MItem As Object
Dim cell As Range
Dim email_ As String
Dim subject_ As String
Dim body_ As String
Dim attach_ As String
Set Source = Nothing
On Error Resume Next
Set Source = Range("C3:c150").SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Source Is Nothing Then
MsgBox "The source is not a range or the sheet is protected. " & _
"Please correct and try again.", vbOKOnly
Exit Sub
End If
'Create Outlook object
Set OutlookApp = CreateObject("Outlook.Application")
' Loop through the rows
For Each cell In Range("a3:a5").Cells.SpecialCells(xlCellTypeConstants)
email_ = cell.Value
subject_ = cell.Offset(0, 1).Value
body1_ = Source.Values
body2_ = Cells.Range("c3").Value
'Create Mail Item and send it
Set MItem = OutlookApp.CreateItem(0)
With MItem
.To = email_
.Subject = subject_
.Body = body1_ & vbNewLine & body2_
.Display
End With
Next
End Sub
But it is telling me that "The source is not a range or the sheet is protected. Please correct and try again."
The sheet is not protected in any way, and I feel I need to change the code in bold, but I'm not familiar enough with the options and syntax.
Anyone have any advice, direction or changes?
Thanks so much in advance!