Conditional Hyperlink an Email List

[flip198613]

I have a tracker for individuals that are in my program. I want to create a hyperlink button that populates a 'mailto' for all individuals that are "active" only . I have 4 columns:

A5: Last Name
B5: First Name
C5: Email
D5: Data validation drop-down containing "Active", etc....


The idea is I can click one button and outlook would populate a blast email to all of the active members, and exclude all others. How can I accomplish this?

[dflak]

I do this all the time. Attach a sample workbook (with sanitized data, of course) and I 'll take a look at it for you.

[flip198613]

I do this all the time. Attach a sample workbook (with sanitized data, of course) and I 'll take a look at it for you.

I created a smaller, more manageable sample of my actual spreadsheet. In addition to emailing all "active" participants, I added a list to select (or CC) the supervisor of each "active" person. Thanks for your help in advance!

[dflak]

Nothing attached. Use the Go Advanced tab and manage attachments.

[flip198613]

Nothing attached. Use the Go Advanced tab and manage attachments.

I attached it to my original post above. Is it not showing on your end? This is my first time using this platform.

[dflak]

My apologies, I did not go back to the original. Here is the file.

To the extent practical, I made everything "data driven" so you should not have to get "under the hood" with the code. Everything you need is on the Mail Setup Sheet.

Subject is the subject for the email. This subject will be used on all the emails.

Attachments is a list of full paths to attachment separated by semicolons such as C:\Temp\File1.xlsx;C:\Temp\Document.pdf

Mail Flag can be True or False. With TRUE the mail gets processed and sent automatically. With False, the mail is stacked up on the monitor and you can edit it before clicking the send button.

CC is the Carbon Copy that would be sent with each email

BCC is the Blind Carbon Copy that would be sent with each email.

Body is the body of the mail message

If you leave Attachments, CC or BCC blank, they will be ignored. Also you can "hard copy" these variables or you can generates them with formulas.

I converted your table to an Excel table. Some of the many advantages of Excel tables is that the know how big they are and they automatically copy down formulas, validations, etc.

I make use of static named ranges.

Using names and tables makes the code easier to write and understand.

Basically, I read the first column and if it is ACTIVE, I process it. I also look at the Include Supervisor value to "build" the TO string.

Please Login or Register  to view this content.

The module ModMailerMulti contains "standard code" that I wrote a while back and I plug it in whenever I need to mail something. The main code is there to feed it the variables it needs.

[flip198613]

Wow, that is some pretty intricate stuff for my newbie eyes - very impressive. Thank you for putting so much time and effort into a response. I've been staring at it at trying to figure out what's "under the hood" so-to-speak because I have another file I was trying to hyperlink emails with, but hit a 255 character roadblock using the basic hyperlink function. I don't have much experience with this VBA type of coding; is there a simple way to understand the code enough to make changes and use it in other spreadsheets? Which line in that code points to the "TO" and which line places a button? Perhaps a color-coded version so I can follow along? I want to pull this code and paste it into the actual sheet with the needed input changes. Sorry for my lack of understanding .

[dflak]

It was not a lot of work because way back when I developed the module for the mailer. It is in, original computer parlance, a subordinate routine to the main routine. I designed it so I could pass To, CC, BCC, Subject, etc. to it. So whenever I need to mail something, I just plug this module in and write a minimal amount of code to collect and pass the parameters to it.

The program I wrote for you does not use hyperlinks; it uses the mailer module to have Excel talk to Outlook. Also, it was designed as a template. Almost everything is set up on the mail setup page. You can use this template to set up different mailers. You can use different lists, different mail subjects, different mail bodies, and different attachments.

Now that I look at it, the code I provided IS specific to this application. I will give it a shot to make it more generic, so that you can fill in a table and that's all you will have to do. You may have to make some formulas on the excel side to do things like add the supervisor email. This will make it a true template that you can use for any mail application without having to know any VB code.

Unless you've had previous coding experience, there is no simple explanation of the code. It is pretty straightforward until the if statement, that's where I add the supervisor email. I think I can "kick this upstairs" so you can handle it on the worksheet.

Give me a day or two to play with this.

[dflak]

I used your case as the example for a more generic version of the mailer.

https://www.excelforum.com/tips-and-...lk-mailer.html