Emailing a range of cells

Cease

Board Regular
Joined
Dec 28, 2010
Messages
112
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:

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!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Forum statistics

Threads
1,224,551
Messages
6,179,476
Members
452,915
Latest member
hannnahheileen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top