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:

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

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 420, align: left"]
<tbody>[TR]
[TD]Case
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Complaint Type
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student Name
[/TD]
[TD]Level 8
[/TD]
[/TR]
[TR]
[TD]Roll No
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Level
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Branch
[/TD]
[TD]HBRA
[/TD]
[/TR]
[TR]
[TD]Issue Date
[/TD]
[TD]16 January 2017
[/TD]
[/TR]
[TR]
[TD]Close Date
[/TD]
[TD]6 February 2017
[/TD]
[/TR]
[TR]
[TD]Past Issue
[/TD]
[TD]No
[/TD]
[/TR]
</tbody>[/TABLE]


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