Macro for creating Mail template from cell value

[anuragit13]

Hi,

I need to create a macro so that on the basis of the cell values the mail template must be created automatically and it should send mail automatically to the Distribution list.

I have below data is excel:

Complaint Type Student Name Roll No Level Teacher Principal Branch Issue Date Close Date Past Issue Case Summary Conclusion Action Taken History of Past Issue Was Teacher Suppot Required Did Teacher Provide support What was lacking from Teacher Elaborate the focus area of the TEACHER Elaborate Highlights of the Teacher Bus Issue AK 123 Junior Anita KK LKO 3/1/2018 7-Mar No Bus not came on time Informed to driver Done NA Yes No Process Gaps Teacher did not follow up properly Lost- Found GK 456 Senior Garvita PK VNS 3/3/2018 8-Mar No My watch is lost in campus Reimbursement of money In process NA Yes Yes Not Applicable NA Teacher was ver supportive and coordinated with accounts team <tbody> </tbody>

<tbody>
</tbody>

Below is What I want is in mail:

Subject line: Teacher Info Share: Case Summary

Mail Body:

Dear

Below is the case summary of the recently concluded case on Dissatisfied with Management. The summary includes highlight/focus areas for the teachers.

Case
Complaint Type
Student Name	Level 8
Roll No
Level
Branch	HBRA
Issue Date	16 January 2017
Close Date	6 February 2017
Past Issue	No

<tbody>
</tbody>

Case Summary: Bus not came on time



Conclusion of the Case Informed to driver

Action Taken Done

History of pervious cases (If Any): NA

Was Teacher support required: Yes

Did the teacher provide adequate support: No

What was lacking from the teacher: Process Gaps

Elaborate the focus areas for the teacher: Teacher did not follow up properly

Elaborate highlights of the teacher:


Regards,
XYZ
SSM School


I want mail to sent to DL : In above mail format Branch value defines the DL. Suppose LKO has two mail ids i.e xyz@ss.com, abc@ss.com and VNS has two mail id i.e. aaa@ss.com, bbb@ss.com. I have another sheet in tab 2 which has list of email ids for LKO and VNS. What i want is that:

1. This macro should create a mail template first.
2. On the basis of branch values in sheet 2 it should send mails



It would be great if anyone can help in this.

Thanks in Advance,
Anurag

 

[daverunt]

Re: Macro for creating Mail teamplate from cell vale

Use the link in the previous posts. The code is in the text file on Dropbox.

 

[anuragit13]

Re: Macro for creating Mail teamplate from cell vale

Getting Compile error : Next without For. PLease correct it.

 

[daverunt]

Re: Macro for creating Mail teamplate from cell vale

I am seeing no such error.

It's likely you have missed the End If here:

mailTemplate = ""
End If
Next

 

[anuragit13]

Hi,

The macro is working that you have provided in your last thread.

The only thing i need to change is that the bold Red value change and is customized as per value in cell column B . Below is the mail body:

Dear

Below is the case summary of the recently concluded case on Dissatisfied with Management. The summary includes highlight/focus areas for the teachers.

 

[anuragit13]

Also the Subject line change with value in B column.

Suppose Subject line is : Manager Info Share: Case Summary Dissatisfied with Management

The value in bold red must be changed as per the value in B column.

 

[daverunt]

Hi,

3 changes to the text file:
1x added 2x updated

https://www.dropbox.com/s/jxwe5jni6rin23u/mail.txt?dl=0