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

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
88
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
88
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jun 13, 2020
Messages
88
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Aug 31, 2016
Messages
3,856
.
VBA Code:
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,122,850
Messages
5,598,457
Members
414,239
Latest member
xnanx

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
Top