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

[Nravota]

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>
[TABLE="width: 638"]
<TBODY>[TR]
[TD]Quick step 1</SPAN>
[/TD]
[TD]Selected based on C28 in excel (text: cleaning of rooms)</SPAN>
[/TD]
[TD]Category “Yes” on the mail</SPAN>
[/TD]
[TD]Flag mark complete on the mail</SPAN>
[/TD]
[TD]Replies with positive template (“Ok, agreed”)</SPAN>
[/TD]
[/TR]
[TR]
[TD]Quick step 2</SPAN>
[/TD]
[TD]Selected based on C28 in excel (text: cleaning of toilets)</SPAN>
[/TD]
[TD]Category “No” on the mail</SPAN>
[/TD]
[TD]Flag mark complete on the mail</SPAN>
[/TD]
[TD]Replies with refusal template (“Sorry, we do not do that”)</SPAN>
[/TD]
[/TR]
[TR]
[TD]Quick step 3</SPAN>
[/TD]
[TD]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>
[/TD]
[TD]Category “Maybe” on the mail</SPAN>
[/TD]
[TD]Flag mark complete on the mail</SPAN>
[/TD]
[TD]Neutral template ( “We need to think about that”)</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

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>

 

[Nravota]

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!