You are here:

Excel/Send e-mail via macro



I need to send e-mails from excel via macro with the below requirements:

1- E-mails to be sent through MS outlook
2- "TO" will be a value in cell B2
3- "CC" will be values in cells B4 & C4
4- "Subject" will be a value in cell C5
5- "body" will be a value in this range B6:C12
6- I don't want to send the worksheet as an attachment, only send the range in point number 5.

thanks a lot Damon for your support and assistance.

ANSWER: Hi Khaled,

Here is a macro that does what you describe:

Sub Send_Email_via_macro()

' Working in Office 2000-2015
  Dim Rng        As Range
  Dim OutApp     As Object
  Dim OutMail    As Object
  Dim BodyStr    As String
  Dim iRow       As Long

  Set Rng = Nothing

  Set OutApp = CreateObject("Outlook.Application")
  Set OutMail = OutApp.CreateItem(0)

  With OutMail
     .To = Range("B2").Text
     .CC = Range("B4") & ";" & Range("C4")
     .Subject = Range("C5").Text
        For iRow = 6 To 12
         BodyStr = BodyStr & Cells(iRow, "B") & Space(5) & Cells(iRow, "C") & vbCrLf
        Next iRow
     .Body = BodyStr
     ' In place of the following statement, you can use ".Display" to
     ' display the e-mail message.
     .Display   'or use .Send
  End With

  Set OutMail = Nothing
  Set OutApp = Nothing
End Sub

When you mentioned in #3 that CC is in B4 and C4, I assumed that each cell contained one or more email addresses.  If more than one in a cell I assumed they are separated by ";".

In #6 I assumed you wanted the values in the ENTIRE range B6:C12 to be copied as text to the email Body, maintaining row-column order.

I hope you find this helpful.


---------- FOLLOW-UP ----------


I've another question.

I tried to hide the above cells and protect them, but after I did this the macro code is not executed, I don't know why, please your help in this.

Hi Khaled,

I have not been able to duplicate your problem. When I lock the cells, hide their columns and protect the sheet the macro still runs for me.

If you would like you can email your workbook to me and perhaps I can duplicate and debug your problem then.  My email address is

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Damon Ostrander


I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.


I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 All rights reserved.