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]

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