VBA Code to export excel file to MS Outlook email as attachment, send and categorize the mail

Nravota

New Member
Joined
Apr 2, 2015
Messages
7
Hi Everyone and thanks in advance for the help. I am relatively new to basic but really want to learn. I know I can count on you guys.

I do not know how to approach this. I need a macro which can do the following

1. saves excel worksheet to a folder
2. replies to an outlook mail (criteria: the last that was selected) and puts the correct template in the body of the mail</SPAN>
I receive an e-mail with a notepad attachment, which I then process in excel. I want the macro to save the file and then to reply to the mail (the one I have worked upon). I need this mail to be categorized and have a flag as well. I have to select three different categories and three different templates for the body of the mails I send. For this, I created three quick steps in Outlook. Here is how it looks like:</SPAN>
Quick step 1</SPAN>
Selected based on C28 in excel (text: cleaning of rooms)</SPAN>
Category “Yes” on the mail</SPAN>
Flag mark complete on the mail</SPAN>
Replies with positive template (“Ok, agreed”)</SPAN>
Quick step 2</SPAN>
Selected based on C28 in excel (text: cleaning of toilets)</SPAN>
Category “No” on the mail</SPAN>
Flag mark complete on the mail</SPAN>
Replies with refusal template (“Sorry, we do not do that”)</SPAN>
Quick step 3</SPAN>
Selected based on:</SPAN>
1. C28 in excel (text: cleaning of toilets) </SPAN>
2. yellow background colour in any of cells in sheet 1 of the workbook</SPAN>
Category “Maybe” on the mail</SPAN>
Flag mark complete on the mail</SPAN>
Neutral template ( “We need to think about that”)</SPAN>

<TBODY>
</TBODY>

3. attaches the file in the mail </SPAN>(the last saved excel file from the folder with the first two letters initials AY)
4. puts the correct e-mail address in TO:</SPAN>
Here I want the macro to select the correct contact person based on a table with about 50 e-mail addresses. The criteria is cell 50D, which is a string, e.g. John Zink. I think I need vlookup formula here but not sure how to approach this. Then the correct emails are exported in outlook.</SPAN>
After the mail is categorised, with the correct text in the body and the correct mail addresses, I would like to check and send it myself to the respected people. However, it is important that the final document is macro-free.</SPAN>
5. moves the already sent e-mail from the inbox to the respective folder in outlook</SPAN>

After I send the mail, I want the macro to move the mail to the correct folder, e.g John Zink would be in folder John. It is important that it takes only the first word from cell 50D to search for the correct folder</SPAN>

One more specification is that I have two outlook mails and I need to connect the macro to the second one (not the primary).

Any help will be much appreaciated! </SPAN></SPAN>
 
Last edited:

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Nravota

New Member
Joined
Apr 2, 2015
Messages
7
If all of this cannot be implemented, maybe it can be made more simple. Maybe there is a way to automatically attach in outlook the last saved file which starts with these two letters and include that in the above-mentioned quick step? Thanks for any info shared!
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,721
Messages
5,597,752
Members
414,171
Latest member
12Rev79

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