VBA TO SEND 1 EMAIL BASED ON 3 COLUMNS: FOR "TO", "CC", "BCC"

[JuicyMusic]

I have 3 macros that i successfully combined sections but still can't get the results I need. The code I have sends an individual email to every email address in column A. The Cc and Bcc works fine, but i need the following for another project at work.

Needed: ONE email to be sent which will show the following on 1 email. Like a company email blast to the employees. Everyone can see who was on the distribution list. Everyone can see who was copied as well,

TO : BASED ON LIST OF EMAIL ADDRESSES IN COLUMN A
CC: BASED ON LIST OF EMAIL ADDRESSES IN COLUMN B
BCC: BASED ON LIST OF EMAIL ADDRESSES IN COLUMN C

1) The number of email addresses in all 3 columns will very depending on whi the user wants to send it to, and too cc on that same email, and to blind copy if there is that need.

2) There will always be an email address in column A, but may not always be any addresses in column B or C if the user doesnt want to copy or blind copy anyone,

3) I have 2 text boxes on the sheet to act as the Subject line and the body of the email. This section of the code i have works well. When i type in the text boxes and run the code the text appears in the subject line and body of the email. Need this still.

4) IMPORTANT: Columns A B and C have a header in row 1 as follows: Send to, CC, BCC. The user may change the header so i dont want the code to be fixed on these names. Email addresses in these 3 columns start on row 2, always.

5) Need this code to be based on "ActiveSheet", not "Sheet1". The user will need to name the sheet however they want. Plus, there will be a 2nd tab for a different kind of emailing specifics which the code i manipulated myself works well for.

6) I need a command button for the user to run the code.

7) Need this code to be set as DISPLAY instead of SEND.

8) The code i have starts off with a "CarryOn" message box to ask the user if they want to run the code. Yes or No.

Thank you in advance,

Juicy

 

[Logit]

Yes

 

[JuicyMusic]

Logit,
I'm back. Could you please show me how to add a Message Box that says something like "No emails have been added" or whatever phrase

What if someone runs the code but they didn't actually enter any email addresses in any of the columns? I just don't want an error message and the code pops up.

Thank you so much,
Juicy

 

[JuicyMusic]

Logit,

It seems as if my new question has disappeared.

Could you please show me how to add a Message Box that says something like "Please add email addresses", or something like that.

I would like a message box in case the code is ran but no email addresses were entered on any of the 3 columns.

Thanks,
Juicy

 

[Logit]

.

Sub SendEmail()
    Dim OutlookApp As Object
    Dim MItem As Object
    Dim cell As Range
    Dim email_ As String
    Dim cc_ As String, bcc_ As String, subject_ As String, body_ As String
     
    Dim answer As Variant
      answer = MsgBox("Do you want to send the email ?", vbYesNo + vbQuestion, "Send Email ?")
      If answer = vbNo Then
          Exit Sub
      End If

    If WorksheetFunction.CountA(Range("A2:A10")) = 0 Then '<--- change range as required for Column A only
        MsgBox "Please enter a minimum of one email address in Column A.", vbCritical, "Missing Email Address"
        Exit Sub
    End If

 

[JuicyMusic]

Thank you,
It worked perfectly.

Juicy

 

[Logit]

You are welcome.