Excel/Send e-mail via macro
QUESTION: Hi Damon,
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:
' 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)
.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
.Body = BodyStr
' In place of the following statement, you can use ".Display" to
' display the e-mail message.
.Display 'or use .Send
Set OutMail = Nothing
Set OutApp = Nothing
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.
[an error occurred while processing this directive]---------- FOLLOW-UP ----------
QUESTION: Hi Damon,
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.
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 VBAexpert-AT-myway.com.