Create a dependent drop down for Outlook email

[spiwere]

Hi,

I’m requesting some help to create a dependent drop down for creating an outlook email. What I want to do is:

Incorporate a dropdown menu within this code that will help me do the following:

Select the subject line in Column N of the RDBMailOutlook, and get a dropdown menu to choose the appropriate email body in Colum K. I will be creating a comprehensive database of the subject line and related email body in the “Body Text” tab of the attached.

The main point is that every same subject line can have variations in the email body too. In other words, the same subject line can have 3-4 or more types of body texts from which the desired one will need to be selected based on the scenario. Overall I have over 30 types of body texts. I would only like to see the body text that are related to that subject line on selecting the subject

The subject line that is selected in Colum K should then be able to concatenate the company name in Column M and update the main subject line in Column I which is to be used for emailing as shown in the example.

Hope I’ve presented my request correctly and clearly. The expected results are also shown in the attached.

PS: The other part of the code needs to remain unchanged though I understand the way the subject line is currently being populated from the DataBase tab will need some alterations.

Thanks for assisting me!

[spiwere]

A small update on my request, the drop down requested is to be 3-fold.

I have to follow it in this manner:

Step 1 - Select Type from column 0. (RDMail - tab 1) This will give the various Subject lines to pick from the drop down.
Step 2 - Select the subject line from column N of tab 1 (drop down). This will give the various email bodies associated with it from the body text tab.
Step 3 - Select the appropriate email body to be sent.

PS: I also have a Type A, Type B and likewise. A single Type will have few subject lines attributed to it and these subject lines will have few email bodies related to them which will need to be selected based on the situation. All this will be maintained in the Body Text tab of the sample shared in my first post. I'll be adding few more to it so has to be dynamic.

Thanking in advance!

[jaslake]

Hi Spi

The attached has Code that demonstrates my current understanding of your requirements. I don't understand how we get Column K of RDBMailOutlook when we have multiple selections. Please explain your thinking...

[spiwere]

Absolutely brilliant! This is becoming a cake walk now. All credit and thanks to you sir…

Your understanding of the first phase is absolutely spot on. The result I got from run are absolutely in-line with what I need.

My apologies for the slight confusion that I may have created unintentionally. You are correct, it is hard to understand or perhaps an incorrect way of going about what I wanted in Column K of RDBMailOutlook. On rethinking about it, this is what I actually would request your assistance on:

1. BodyText sheet will be where I’ll store all my email templates.
2. Each email template will be associated to a subject line (1 Subject Line = 1 Body Text). The associated body text should get automatically populated into Column K as soon as the Subject from the Subject Line – Dropdown in Column N is selected
3. I’ve updated Column F and G in BodyText by attributing each sample subject line to a sample body text
4. Column K has been manually populated with the requested results / output

Hope the above rules out any disconnect I may have caused previously!

One last thing that I also need your help on is adding the current days date to the subject line. Examples:

1. If I’m sending an email today the subject line should read as follows: 1 - Account Debited – April 24, 2013
2. If send it tomorrow subject line should read as follows: 1 - Account Debited – April 25, 2013, and likewise.

That's all about it!

Look forward to your assistance.

Thank you with all humbleness!
Spi

[jaslake]

Hi Spi

Try the Code in the attached...let me know of issues.

[spiwere]

Perfect is a very small word.

One last bit...I need your inputs. Will these be the only lines that need to changed to green using ' to enable me ensure that the mail is always drafted, but NEVER EVER sent without a preview.

Please Login or Register  to view this content.

I know there is an option to choose Yes or NO in C3, but just wanted to be safe than sorry later. I just don’t want the emails to go out. During my testing some accidentally went out.

Rest everything is just Perfect.


Thanking you sir...

[jaslake]

Hi Spi

I think you need only change this one line

Please Login or Register  to view this content.

Were the User to change C3 to "No" then nothing will happen

Alternately you could Lock Cell C3 (set as "Yes") and protect the Worksheet. Another alternative is to Lock AND Hide Cell C3 and protect the Worksheet.

An issue I thought about as I was going through the exercise...when and how do you plan to clear RDBMailOutlook? If you simply highlight the Data and click Clear Contents, the Code will get triggered. There is a way around this

Please Login or Register  to view this content.

[spiwere]

Excellent, thank you for everything!

I'm on my way home. I'll try to implement all the suggested changes you mentioned (Locking and Hidding will not be an issue). I'll asloo make the necessary changes to the code you mentioned.

Until now I was deleting the RDBMail contents before every run.

Many many thanks!

I'll close the thread shortly. Quite sure nothing will come up as an issue, but hoping I can request your expertise, if by an chance ot does!

Thank you!!!

[spiwere]

Dear John,

Here are my observations from the final run:

On changing this .Send '<----- line it is working absolutely as you said. No emails get generated even if the “NO” option is selected or C3 is left blank. So all good here…

Locking Cell C3 (after setting as "Yes") and protecting doesn’t seem to work. As I’m getting a popup stating that RDBMail won’t work if cells or sheet are protected. I think as a second precautionary measure, I’ll hide it. Better safe now than sorry later!

Clearing RDBMailOutlook – I’m not sure if I picked the right line for this one on the code. On updating this line as False. The code stopped working.

Please Login or Register  to view this content.

One thing that I messed up on was when I attempted to change the order. The current design requires me to select in this order:

Company Name (Column M) - Type (Column O) - Subject Line (Column N). I tried amending the order to make it user friendly as in:

Company Name (Column M) - Type (Column N) - Subject Line (Column O), but it did not work out. So will have to manage...

I really respect your efforts and time and helping me out on this. It will go a long way. Other than this it been a good learning too.

I’ll lookout in case you have any inputs. For now, closing the thread “Solved”

Many many thanks!

Regards,
Spi

[jaslake]

Hi Spi

I'll play with this when I have a bit of time...I thought you might want to reconsider the order

One thing that I messed up on was when I attempted to change the order.

Regarding this

Clearing RDBMailOutlook

When to you want this to happen? After the Emails are sent? Should ALL records be cleared?

[spiwere]

Thanks a lot John for considering this...

1). Yes, I would really want the order to be sorted. It is a little off and not very user friendly. My preference would have actually been to have Company Name in Column A, Type in Column B and so on, but I understand it can't be done so easily. However, if you could at your convenience help me with Company Name (Column M) - Type (Column N) - Subject Line (Column O) order it would be immensely helpful!

2) Ideally, I would like to have a Clear button next to Send. This will give me flexibility to delete at will and whenever I need it to rather than it doing it automatically.

Really can't thank you enough for this massive help!

Kind regards,
Spi

[jaslake]

Hi Spi

The attached has the Code to Clear the Worksheet. The other issue is a significant rewrite and, at the moment, I'm doing a major File conversion for my youngest Daughter. I'll look at it but it may be several days.

[spiwere]

Thank you so much! No probs at all. I'll be absolutely patient and will wait for it.

One more thing I did my first live run with the available solution you helped me with. It has significantly cut down on my time for composing emails. Now all I need to do is update certain dates and people names as welll as company names in the basic drafts I generate using it from another spreadsheet I maintain.

Thanks a ton!

Good luck to you for everything you do. You are immensely helpful sir:-)

Regards,
Spi

[jaslake]

Hi Spi

I've modified the Code for this

My preference would have actually been to have Company Name in Column A, Type in Column B and so on

I suspect other Columns are not where you'd ideally like them but take a look...let me know of issues.

[spiwere]

Unbelievable sir! I honestly have no words to express how thankful I am. I'm currently out. Will reach home in the next couple of hours, and will check it out as a first thing.

You are right! Actually some of the columns are not applicable to me and were creating a lot of scrolling...

You've made my evening! Really can't wait anymore to check it out. Take a bow sir!

Kindest regards,
Spi

[spiwere]

Dear John,

I came early and have just run it. It doesn't seem to do anything. Hope I'm not missing out on anything. Could you please look into it at your convenience.

Also, the placement is absolutely in sync what I was looking at except that there are few more columns that I don't need at this point and would have ideally like to be moved to the end, but I understand that would be a lot of work. I really don't want to even make such a request that it becomes unreasonable. However, my complete preference would have been like this:

Company Name
Type
Subject Line - Dropdown
Send
To
Cc
Bcc
Subject
Body text
Importance
Attach other files
Sheets
File Name
Values
Objects

Thanks for all your efforts.

Best regards,
Spi

[jaslake]

Hi Spi

When you say

It doesn't seem to do anything

Are you saying There's no Drop Down in Column A and nothing gets populated in Columns B, C, I, J, K, L and N? This works for me.

Or are you saying no Emails are produced? I've not touched the Email Code...that's your baby...

[spiwere]

Nothing wrong with the drop downs. They are working perfectly!

The issue is in the emails not getting produced at all I tried with the previous version and they did like earlier. But somehow not with this one: Code Modification Request V3_SPIV4.2

Thanks!

[jaslake]

Spi, the Columns have been moved around...the references in the Email Code need to be changed.

[spiwere]

Okay, Guess than I'm stumped

[jaslake]

Hi Spi

Here's a clue

Please Login or Register  to view this content.

I'm certain there are other References that will need to be made. You PROBABLY don't want to make these changes until this is completed

Company Name
Type
Subject Line - Dropdown
Send
To
Cc
Bcc
Subject
Body text
Importance
Attach other files
Sheets
File Name
Values
Objects

[spiwere]

You are right that needs to be fixed first. That's my much needed sequence, but the only challange is that if these are changed how much the code will be impacted and how difficult / easy will it be to fix it too. I suspect in my current stage wrt VBA I'll be able to make all changes in all honesty.

By the way, also sent you a PM do check it out sir!

Regards,
Spi

[jaslake]

Hi Spi

I've made the changes for this

Company Name
Type
Subject Line - Dropdown
Send
To
Cc
Bcc
Subject
Body text
Importance
Attach other files
Sheets
File Name
Values
Objects

Need to do a bit of testing yet. Probably get back with you tomorrow. After I've made the Column changes, I'd like you to tackle the Email Code. If you run into issues with that let me know.

[jaslake]

Hi Spi

Try the attached...I've added some Bells and Whistles that I think I'd like to see. Let me know of issues...

[spiwere]

The drop downs are working superbly. The sequence has put a perfect icing on the cake. Brilliant is all I can say...no words are enough.

However, the References as you suggested will need to be reworked. I'll put some head into it. Really unsure what the outcome would be will let you know what is happening in a while.

Have a great weekend!

[spiwere]

Hi John,

I did change quite a few references to relate the changed fields with the code. But it seems that there is definitely a few more places that it needs to be changed or maybe I've done something incorrectly. No mail drafts are getting generated...

May I please request you to take a look at the script with changes incorporated by me, and help correct and update missing references.

Thanks a ton!

Best regards,
Spi

[jaslake]

Hi Spi

There's only three lines of Code that need changed to make the Email Code work. I question how much of this Code you truly need. Are you going to be attaching Files? Are you using "Red" Cells? Are you going to be setting "Importance"? Etc, etc...

Do you wish simply to send Emails to those Customers with Data in Columns A through I? What's your game plan? Get back to me.

In Private Sub RDB_Outlook_Click()

Change these lines of Code

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[spiwere]

Good day John!

I'll only need this bit with the option of sending the attachements ocassionally:

Company Name
Type
Subject Line - Dropdown
Send
To
Cc
Bcc
Subject
Body text
Importance
Attach other files

Thank you!

[jaslake]

Hi Spi

I see. Well make the changes I suggested and the Email Code works (I tested it) and let me know of issues.
I did NOT test Importance or Attach Other Files.

[spiwere]

Hi John,

I made the suggested 3 changes but the Type column is getting highlighted in Red which means wrong data and it will not create a mail from that row.

Please advise.

Thanks,
Spi

[jaslake]

Hi Spi

My copy of the Workbook does NOT change the Type Column to red. Please attach the offending workbook WITH DATA that demonstrates the issue so I can "see" what's causing this.

[spiwere]

Here's the file. Please see column B it gets highlighted in Red on clicking send button.

Thank you for all your patience with this.

Regards,
Spi

[jaslake]

Hi Spi

Get the Email Code working the way you wish it to and then we'll rewrite it. There's too much "stuff" going on that appears unnecessary.

[spiwere]

Hey John. This is magical. I just tried your version shared in post 33 and it is working absolutely fine. This is extremely user friendly.

Thank you, thank you, thank you for this super help!

Only the attachment and importance part remains now...But that isn't an immediate request. At your convenience anytime...

Rest is simply great

[spiwere]

Hey John.

Just noticed a small issue. I can't go into any of the cells between E to I. The cursor is jumping from D to J on scrolling. I can't make any customization in cells from E to I.

Could you please look into this... Sorry but just came to my notice.

Thanks!

[jaslake]

Hi Spi

I have those cell locked. Simply unlock them and you'll be able to change them.

[spiwere]

Ooops...sorry didn't realize that could be the reason...Thought it was code doing that. All good now

The reason I want that flexibility is that I'll be updating some dates, figures and names in the templates manually after making the selection using drop downs. The code will help me pick basic drafts. I'll then do some fill in the blanks kind of an exercise. Thought would update you just in case you are wondering.

Thanks!

[jaslake]

Hi Spi

That's fine...only you know all that you're trying to do. Since I'm so deep into this, I'll look at your Email Code to see about streamlining it.

Regarding Attachments...

• What will you be attaching...an entire Workbook? A worksheet from a Workbook?
• Will you be attaching as an Excel File? As a PDF?
• Where do these Attachments live (Full Path)?

[spiwere]

It will always be a complete workbook not a sheet.

However, it would depend at times. I may need to attach a PDF for some, an Excel or Word doc for others.

If they can be picked wherever needed from a folder on the desktop that I can designate it would be great. The file names would always be in sync with the customer names.

Thank you.

Do let me know if you have any questions.

PS: The attacments may be required for some emails and not for others.

[spiwere]

Hi John,

Somehow I’m facing a run time error 1004 on this row:

Please Login or Register  to view this content.

It suggests no cells were found…

Attached is the sheet with my selection. Could you please advise why this may be happening. I think I have input everything correctly.

Please advise.

Thanks!

[jaslake]

Hi Spi

Change that line of Code to

Please Login or Register  to view this content.

[spiwere]

Thanks John!

It seems to work fine now. I'll test it a bit more and will let you know in case of any issues.

For my understanding sake, could please help me learn what was causing the error in the earlier run.

Regards,
Spi

[jaslake]

Hi Spi

Regarding this

what was causing the error in the earlier run.

The Code was using lr which referenced Sheet RDBMailOutlook and is set here

Please Login or Register  to view this content.

instead of LR1 which is set here

Please Login or Register  to view this content.

Attached is a new Workbook with the Email Code significantly modified. You should get rid of the Text Message Pop Ups in Columns K and L...they are NOT applicable. I could not figure out how to eliminate them.

[spiwere]

Many many thanks John!

You are simply great. No thank you can ever be enough! I'll do a final run tonight will let you know of the outcome. Hopefully, everything should go well.

Kindest of regards,
Spi

[jaslake]

Hi Spi

At the beginning of the Email Code replace these lines

Please Login or Register  to view this content.

with these

Please Login or Register  to view this content.

[spiwere]

Sure, will do!

Just one more thing the code sometimes tends to get numb. I'm not able make selections from the drop down nor does it clear contents until I close and reopen it. This happens sometimes but it doesn't at others. Have you experienced the same in your tests?

Not really sure, but a little afraid if it gets stuck in between it may cause a problem and I may need to make all selection again. Happy to post the workbook as soon as get it gain if you like, but it might not help as reopening makes it work.

Please advise your thoughts sir...

[jaslake]

No Spi...I've not had this issue...

[jaslake]

Hi Spi

I had mentioned this previously in Post #43

You should get rid of the Text Message Pop Ups in Columns K and L...they are NOT applicable. I could not figure out how to eliminate them.

See this link on how to do that
http://www.excelforum.com/excel-prog...n-columns.html

[spiwere]

Thanks John,

I'll try it out. For now, I'm doing some more test runs and planning to incorporate my real data into it.

All my tests since the last post have been okay. This has become immensely user friendly. Once I have done every bit of testing. I'll post the final script with some detailed explanation here. I hope it will be extremely useful for some other excel enthuisaists like me.

Currently, I'm thinking of a way to autopouplate the last things (name, date etc) in my basic temaplates that I'm doing manually wish there was a trick to do that too

Thanks for all the support, guidance and invaluable help. Appreciate all of it.

[spiwere]

Hi All,

I would like to extend a very heartfelt thank you to John (jaslake) for all his kind help in assisting me solve an issue and making the existing solution absolutely user friendly and meeting my request. John I really understand that it did take a lot of work from your side, and with all humility I would like to convey my regards and thanks to you. It wouldn’t have been accomplished without your help and expertise!!! Kudos to you for this fabulous masterpiece that I can now be proud of…

Dear Forum members / guests,

For everyone’s benefit and since the thread has been quite long. I’m attaching the final script that helps compose email drafts on Outlook:

Features:

A simple and perfect dropdown menu that auto populates email content:

• Selection in Column A will auto fill “To”, “CC” & “BCC” fields
• Selection in Column B (Type) will give a dropdown with the relevant subject lines to pick from Column C
• Selection in Column C will auto fill the Subject Line and BodyText

You’ll notice that there are 4 tabs in the attached solution. The original can be attained from http://www.rondebruin.nl/sendmail.htm . This is a customized solution by Jaslake which meets my request perfectly. I hope it is useful to others as well.

Components:

• Tab 1 – RDBMailOutlook is the main sheet that is to be used to create email drafts using the dropdown menu
• Tab 2 – DataBase a place where you can maintain all your email distribution lists
• Tab 3 – BodyText a sheet to keep stock of all your basic templates to be used as drafts
• Tab 4 – Lists it gets created to facilitate dropdown selection in RDBMailOutlook

The solution is extremely robust, and the credit for this goes to Ron for creating and making it available for free. And to John for making this even more user friendly to cater to my request.

Once again I would like to express my kind regards and extend a BIG THANK YOU to John for all his hard work and patience in helping me out!

I would also request John, to add / edit any of the above that help better explain the usability and functionality for all excel enthusiasts like me…

Hope this comes handy to all who need it…

Regards,
Spi

[jaslake]

Hi Spi

You're welcome...glad I could help. Thanks for the Rep.