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]

It is assumed the template is Sheet1 and the Branches are in column F - assumed from your post.
It takes what is in each row of column F and compares the value to the list of Select cases. If it doesn't find it listed it comes back with unknown recipients and exits the sub

Worksheets("Sheet1").Activate

'Column a as reference
 Set rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))

 

[anuragit13]

Re: Macro for creating Mail teamplate from cell vale

Hi,

Thanks for helping me. One more thing I want is that - I want some fields let say from "Complaint type to Close date" in tabular format and then want 2-3 line space and then the fields from "Past issue to Elaborate highlights of the teacher" . And I dont want any email linkage now with branches. I just want the mail editor gets open with "to and cc field as blank". Please help

 

[daverunt]

Re: Macro for creating Mail teamplate from cell vale

Hi,

Code is in the top of the text file:
https://www.dropbox.com/s/jxwe5jni6rin23u/mail.txt?dl=0

The top table has a border and is tabulated, the bottom hasn't changed but there's a gap of 2 rows.
mail branches removed.

Sub Send_Mail()

'Column a as reference
 Set rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
          

For Each cell In rng

    mailHdr = "<p>Dear</p>" _
    & "<p><i>Below is the case summary of the recently concluded case on Dissatisfied with Management. The summary includes highlight/focus areas for the teachers.</i></p>"
    
    mailFooter = "<p>Regards,xyzSSM School</p>"
       

mailTemplate = "<table border=""1"">" _
            & "<tr><td><b>Complaint Type</b></td><td> " & cell.Value & "</td></tr>" _
            & "<tr><td><b>Student Name</b></td><td> " & cell.Offset(0, 1).Value & "</td></tr>" _
            & "<tr><td><b>Roll No.</b></td><td> " & cell.Offset(0, 2).Value & "</td></tr>" _
            & "<tr><td><b>Level</b></td><td> " & cell.Offset(0, 3).Value & "</td></tr>" _
            & "<tr><td><b>Branch</b></td><td> " & cell.Offset(0, 6).Value & "</td></tr>" _
            & "<tr><td><b>Issue Date</b></td><td> " & cell.Offset(0, 7).Value & "</td></tr>" _
            & "<tr><td><b>Close Date</b> </td><td>" & cell.Offset(0, 8).Value & "</td></tr>" _
            & "</table><br><br>" _
            & "<table>" _
            & "<tr><td><b>Past Issue</b> " & cell.Offset(0, 9).Value & "</td></tr>" _
            & "<tr><td><b>Case Summary</b> " & cell.Offset(0, 10).Value & "</td></tr>" _
            & "<tr><td><b>Conclusion of the Case</b> " & cell.Offset(0, 11).Value & "</td></tr>" _
            & "<tr><td><b>History of previous cases (If Any)</b> " & cell.Offset(0, 13).Value & "</td></tr>" _
            & "<tr><td><b>Was Teacher support required:</b> " & cell.Offset(0, 14).Value & "</td></tr>" _
            & "<tr><td><b><i>Did the teacher provide adequate support:</i></b> " & cell.Offset(0, 15).Value & "</td></tr>" _
            & "<tr><td><b><i>What was lacking from the teacher:</i></b> " & cell.Offset(0, 16).Value & "</td></tr>" _
            & "<tr><td><b><i>Elaborate the focus areas for the teacher: </i></b> " & cell.Offset(0, 17).Value & "</td></tr>" _
            & "<tr><td><b><i>Elaborate highlights of the teacher:</i></b> " & cell.Offset(0, 18).Value & "</td></tr>" _
            & "</table>"
       

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    mailSubject = "Teacher Info Share: Case Summary"
    
   
        With OutMail
            .To = ""
            .CC = ""
            .Subject = mailSubject
            .HTMLBody = mailHdr & mailTemplate & mailFooter
            .Display
        'send
       End With
        
  

mailTo = ""
mailSubject = ""
mailHdr = ""
mailTemplate = ""
Next

 

[anuragit13]

Re: Macro for creating Mail teamplate from cell vale

I want 2 line space between close date and past issue. Please help with that.

 

[daverunt]

Re: Macro for creating Mail teamplate from cell vale

Use the code from the text file link as the code here doesn't render the < br > tags that are between the tables.

 

[anuragit13]

Re: Macro for creating Mail teamplate from cell vale

Thanks a lot Daverunt. You are a champ. Thank you so much. Really appreciate.

 

[daverunt]

Re: Macro for creating Mail teamplate from cell vale

You're welcome.

 

[anuragit13]

Re: Macro for creating Mail teamplate from cell vale

Hi,

I want that if I add 1 column in the last in the sheet with name "Mail" as a header. And if it has only 2 distinct values Yes or No.

If from total of 10 entries in the sheet I write "Yes" in front of 6 entries and "No" in front of 4 entries and then after running macro it should compose the mails only of the entries where "Yes" is written. Please help.

 

[daverunt]

Re: Macro for creating Mail teamplate from cell vale

See the same link as before for modified code.

It looks at column T for a Yes . Using the offset from column A like all of the other checks. You may need to modify that.

If it doesn't find "Yes" then 'No' is assumed and no mail is sent so a blank cell for example is also 'No'
If a mail IS sent it then changes the value of the cell from Yes to No

 

[anuragit13]

Re: Macro for creating Mail teamplate from cell vale

please share the code