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

I tried to send a pm in response to you but it is reporting:

"anuragit13 has exceeded their stored private messages quota and cannot accept further messages until they clear some space."

Considering you only have 1 post and joined this month I am guessing you have messaged a list of people to look at your post and your Sent Items folder maxed out your quota?

 

[anuragit13]

Re: Macro for creating Mail teamplate from cell vale

Hi,

I have deleted messages now. Please help with the micro.

 

[anuragit13]

Re: Macro for creating Mail teamplate from cell vale

Hi,

I have deleted messages now. Please help with the macro.

 

[daverunt]

Re: Macro for creating Mail teamplate from cell vale

Hi,

this is the main part of the mail.
It creates the body of the mail using the rows.

I haven't done the 'mail to" as yet but you can run the macro as is to see the output without the recipients.
What is the worksheet name and what columns are the branches listed in for mailing to?
How many branches are there?

There's a DropBox link here as some of the HTML may be incorrectly rendered in the post
https://www.dropbox.com/s/jxwe5jni6rin23u/mail.txt?dl=0

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>"
    
       
    mailTemplate = "<table>" _
            & "<tr><td><b>Complaint Type</b> " & cell.Value & "</td></tr>" _
            & "<tr><td><b>Student Name</b> " & cell.Offset(0, 1).Value & "</td></tr>" _
            & "<tr><td><b>Roll No.</b> " & cell.Offset(0, 2).Value & "</td></tr>" _
            & "<tr><td><b>Level</b> " & cell.Offset(0, 3).Value & "</td></tr>" _
            & "<tr><td><b>Branch</b> " & cell.Offset(0, 6).Value & "</td></tr>" _
            & "<tr><td><b>Issue Date</b> " & cell.Offset(0, 7).Value & "</td></tr>" _
            & "<tr><td><b>Close Date</b> " & cell.Offset(0, 8).Value & "</td></tr>" _
            & "<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)

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

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


End Sub

 

[daverunt]

Re: Macro for creating Mail teamplate from cell vale

If you want the mail to contain a table with 2 columns rather than one then you need to add a couple of html tags.
It hasn't rendered properly so I have added the alternative to the bottom of the file in the DropBox link

 

[daverunt]

Re: Macro for creating Mail teamplate from cell vale

Additions for delivery addresses and previously missed regards blah blah;
Assumes Sheet2 is the store for the addresses
Change the column numbers in the Select Case to reflect the correct column for the Branches

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,<br>xyz<br>SSM School</p>"
       
    mailTemplate = "<table>" _
            & "<tr><td><b>Complaint Type</b> " & cell.Value & "</td></tr>" _
            & "<tr><td><b>Student Name</b> " & cell.Offset(0, 1).Value & "</td></tr>" _
            & "<tr><td><b>Roll No.</b> " & cell.Offset(0, 2).Value & "</td></tr>" _
            & "<tr><td><b>Level</b> " & cell.Offset(0, 3).Value & "</td></tr>" _
            & "<tr><td><b>Branch</b> " & cell.Offset(0, 6).Value & "</td></tr>" _
            & "<tr><td><b>Issue Date</b> " & cell.Offset(0, 7).Value & "</td></tr>" _
            & "<tr><td><b>Close Date</b> " & cell.Offset(0, 8).Value & "</td></tr>" _
            & "<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>"
            
 '-------------------------------------------------------Assumes column A for LKO, column B  for VNS - modify and add cases as required       
      Select Case cell.Offset(0, 6).Value

      Case Is = "LKO"
      col = 1
      
      Case Is = "VNS"
      col = 2
    
      Case Else
      
            MsgBox ("Recipients column unknown macro will exit")
            Exit Sub
      
      End Select
         
         
    With Worksheets("Sheet2")             '------------------------Assumed addresses on sheet2
      lRow = .Cells(Rows.Count, col).End(xlUp).row
      For i = 1 To lRow
      mailTo = mailTo & .Cells(i, col).Value & "; "
      Next
     End With
            

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

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

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


End Sub

 

[daverunt]

Re: Macro for creating Mail teamplate from cell vale

Copy the code from the text file in Dropbox (link in post no 5) as there are html tags missing from the posted code.

 

[anuragit13]

Re: Macro for creating Mail teamplate from cell vale

After running macro..its showing error "Recipients column unknown macro will exit". I have created two columns in sheet two.. Column A for LKO and col B for VNS and below this i entered email id but still its showing error.

 

[daverunt]

For now make sure the template is the active sheet when you start the macro as it is looking in column F for the branches.