How to trigger Outlook Email from excel with the click of a button?

[nikstheicon]

Hi,
I need to know as to how do I trigger an outlook email from excel by clicking a button.Also the email should have a specific Email body whose contents are taken from the specific cells of excel.

For e.g.,in the attached excel there are columns like Issue,Root cause,etc and the word file contains the content of the email body.So when this mail is triggered,it should also copy the content from the corresponding Issue,Root cause cell into the Email body in front of "Issue:" , "Root cause:" . Also it should populate the “issue id" & “Program Name" in the starting line of the mail with the corresponding cells from the excel.

Is there any way this can be programmed?

[Jack7774]

Insert a button and assign a macro to it.

Please Login or Register  to view this content.

[nikstheicon]

Hi jessebranum777,

Thanks alot for the code.But i am completely new to writing macros.I don't even know where to put this code and how to insert the mail body as per the word file into it.So also i want it to be applicable to each and every row,so i suppose a drop-down dependent trigger should be better instead of button.Like as per attachment,for any row , if i change the drop-down to "yes" then it should create the mail with all things filled in the mail body as per corresponding excel cell.

It would be really helpful and thankful if you could program this into the attached excel.
Thanks in advance..

[Jack7774]

Here are step by step instructions on how to put the macro in place to work for you.

1. Hold ALT and press F11 to open VBA editor
2. Then at the top click Insert then module
3. Then copy and paste the code below (when we achieve your custom code)

The headers will need to be in the place where they are going to stay in the worksheet so that the macro knows where to pull which data piece from. You will need to make an email column and put an email for each row.

You mentioned you have a word file that you want to be the body. I will either need the location of it or for me it would be easier if you copied and pasted it into a cell in your excel spreadsheet then I could just reference that value as the body. If something in the body of that letter changes based on the row values then you either you or I can set that up in excel to replace those spots based on the values in the row we are on.

[Jack7774]

Oh and do you want to see each email before you send it or not?

This is make the automated process a slightly more manual one. Keep in mind if you have the same process being done over and over again its better to leave it to being automatic than to see each email before it goes out. If you want to test it you can always only change one row to yes and modify the email temporarily to your personal email for testing.

[nikstheicon]

Hi jessebranum777,

Yes I do want to see the mail before I send it and i do agree that it will be slightly manual.But this is required as there will be more data in that mail body which varies all the time and is not included in the excel so cannot be referenced from the excel.This data will be added manually after the email is composed.

The mail body format that I need is exactly as seen in the attached word file;all fields in that including questions will remain the same for every mail.And the corresponding fields in the mail body should be filled by referencing corresponding cells in that particular row as soon as the dropdown for that row is switched to "Yes".
I have shown this in the attached "Autofilling" file.Yellow highlighted part in the 1st line need to be filled as per the arrows shown & Yellow highlighted parts in the table needs to have data in front of them from the corresponding cells in excel.

With your steps I got how to and where to put this code.Thanks a lot for that.However I don't understand how do I incorporate this mail body in the VB code and how the cells from that excel can be referenced in the mail body and filled automatically when the mail is composed.

Request your assistance again here.
Thanks

[Jack7774]

I have an idea that is much easier however more manual than pressing one button like a macro. And since you want to see the email each time and have a specific format in word that you want to use for the email. What I would do is a mail merge then send the emails.

https://support.office.com/en-us/art...b-0a948fa3d7d3

[Jack7774]

If this was able to solve your issue please click * and give rep to those whom have helped you and mark the thread as solved. Thanks.

[nikstheicon]

Hi jessebranum777,

Thanks for the option.However this process as you mentioned is bit more manual.eg.xlsmWord KE.docxFinal.xlsmThis may not help me really.I needed this to be coded into a macro only.So that everytime writup of the mail and filling all data in it can be avoided as soon as user switches dropdown option to "Yes" for that particular row.

I would be really thankful if you could please help me with the macro..

[Jack7774]

Okay but keep in mind if you want to look at each email before they go out and you have 100+ emails you will have 100+ emails generated and stay open until you either send them or the macro crashes or the computer due to the large amount of emails open. This macro is not finished as you are missing a spot for the email address, subject, body, you have to save your document in a certain directory then copy that directory onto the macro since I don't know its location. This however will not put the body of the email as the word document you presented but will attached the word document you presented with the appropriate information you mentioned where is should go. Let me know if we have begun down the right track here.

Please Login or Register  to view this content.

[nikstheicon]

Hi jessebranum777,

Sorry for the delay..thanks alot for the help...I changed the code as per my requirement by creating the required design of the mail body in another sheet of the same workbook and then populating it with the required data.

I could successfully create the mail with all the data required.Thanks alot for all the support

[Jack7774]

Awesome! If you haven't already give rep to those whom helped and mark the thread as solved.