VBA Code to Email using addresses, subject, and body from cell contents

[misc9700]

Hello,

I am seeking some assistance in creating VBA code for Excel (windows office 2016 and 365). I am starting to learn the language and have very limited experience. I have also tried adapting code currently available on the net, without success.

I have two worksheets in my workbook named “Blastout” and “Personnel”.

In the “Personnel” worksheet, I have the following columns:
-column “C” which lists selected email addresses for a SELECTIVE BLASTOUT;
-column “D” which lists ALL email addresses for an ALL BLASTOUT;

The “Blastout” worksheet will essentially replicate an email window, where the user can input the email subject and email body. It will consist of:
-Cell B3: Email Subject
-Cell B5: Email Body
-A form control button that activates a macro blastout_selected which will BCC send the email subject (B3) and body (B5) to every email address in column C of the “personnel” worksheet;
-A form control button that activates a macro blastout_all which will BCC send the email subject (B3) and body (B5) to every email address in column D of the “personnel” worksheet;

I greatly appreciate any assistance that fellow users can provide. Thank you!

[Mumps1]

Assign these macros to the appropriate buttons. They assume that the email addresses start in row 2 of the "Personnel" sheet.

Please Login or Register  to view this content.

[misc9700]

Hi,
Thank you immensely for your time and effort! One small hiccup. On attempting to run the above code, I am faced with a "Compile Error: For without Next." Do you have any suggestions or edits? Thank you in advance. You are a life saver to date.

[Mumps1]

Oops. Insert this line of code

Please Login or Register  to view this content.

directly below this line

Please Login or Register  to view this content.

in both macros.

[misc9700]

Hi again,
One step closer. The code runs and generates a new email in Outlook with the subject and body populated. However, the BCC field remains empty. Just to be certain that the BCC wasn't causing the issue, I amended the code so the "rng.Value" appeared next to the .To= field instead, but still no success. Of note, there are some blank fields in columns C and D where there are NO email addresses. Could this be causing the issue? Any final suggestions? Thanks again.

[Mumps1]

Try:

Please Login or Register  to view this content.

[misc9700]

Hello again,

I feel like we are getting there The updated code populated the email address from the "Personnel" worksheet, but only the TOP email address in EACH column. The additional email addresses below rows C2 and D2 failed to populate. I realize this is becoming a bit tricky, but continue to appreciate your help!

[Mumps1]

I tested the macro on a dummy file and it worked properly. It would be easier to help and test possible solutions if you could attach a copy of your file (de-sensitized if necessary). See the yellow banner at the top of this page for instructions to attach a file.

[misc9700]

Hi, It would be too difficult to de-sensitize my sheet, but I also created a dummy file for troubleshooting. Unfortunately, it would still only import ONE email address from columns C&D. I will attempt to attach the dummy file so that you can hopefully see what I am doing wrong. Thanks again.

[Mumps1]

In the "Personnel" sheet, row 1 is blank and your emails start in row 3 not in row 2. Replace this line of code:

Please Login or Register  to view this content.

with this line:

Please Login or Register  to view this content.

[misc9700]

I'm really not sure what I'm missing. I've adjusted the code as you specified, but it continues to only import the BOTTOM email address in each column. I've attached a test sheet again. From what I can tell, the coding looks correct. Would the error have something to do with the lack of semi-colons ";" between the email addresses?

[Mumps1]

Try:

Please Login or Register  to view this content.

[davesexcel]

I..... it continues to only import the BOTTOM email address in each column.

If you change .display to .send it will have sent all the the emails using Mumps previous code, you only see the last of the looped items in the displayed email.

[Mumps1]

@davesexcel
Thank you for your suggestion.
The last version of the macro (Post #12) now displays all emails. In the original macro, I had misplaced this line of code:

Please Login or Register  to view this content.

outside the loop. It is now inside the loop so a new email is created and displayed for each variable (rng).

[misc9700]

Ah I see what you folks have done. Genius! And it works! Thank you.

My only concern is that there will eventually be around 100 email addresses in each column. If I understand correctly, this will open and send 100 separate emails. Is there any way to have all of the email addresses (separated by a ";" in ONE email so as not to overwhelm Outlook and the mail server (and my sent items mailbox lol)?

[Mumps1]

Try:

Please Login or Register  to view this content.

[misc9700]

Amazing! Well it certainly hasn't been easy, but it works. I can't thank you enough for all of your assistance. I am slowly learning VB code, but this would have taken many months to figure out. Once again, thank you.

[Mumps1]

You are very welcome.