How to send data in excel along with an email in CRM using C#

How to send data in excel along with an email in CRM using C#

In the previous post i discussed how can we convert a datatable into a csv file using c#.

Often in Dynamics crm we face the requirement that customer wants some data in excel attached along with the email on some regular interval or so. To do so, the first option looks like we paste the result into email body and send it as email to customer. But that doesn't looks good as user can't do much with email body what he can do if he receive that data in an excel sheet. What if we can send him data into excel attached with the email?? But the question arise, do we need to have office on the server running code?? The latest answer is: NO. Because you can create a csv file in c# and attach it along with a crm email and send it to concerned person.

For ex: Customer wants that he should get an email daily of having the number of cases created on the same day as per their origin.

Like:
Originated from    Count
Phone call             200
Email                    120
Chat                       80
Web                       400

So to send such kind of data into excel what we can do is, we can extract the data into a datatable using some C# code from their crm application and convert the datatable into CSV as explained in previous post.   Once you get the comma separated value as result from datatable, you can then create an email record into crm and pass the guid of email record along with csv result.

Below function will attach the csv string in the form of a csv with that email which can be opened easily in excel.

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
   //Function to  add csv message as attachment with the email record of passed guid
        public static void addAttachmenttoEmail(Guid emailguid, String message, IOrganizationService service)
        {
            Entity attachment = new Entity("activitymimeattachment");
            attachment["subject"] = "SampleCSV";
            string fileName = "Sample.csv";
            attachment["filename"] = fileName;
            byte[] fileStream = Encoding.ASCII.GetBytes(message);

            attachment["body"] = Convert.ToBase64String(fileStream);
            attachment["mimetype"] = "text/plain";
            attachment["attachmentnumber"] = 1;
            attachment["objectid"] = new EntityReference("email", emailguid);
            attachment["objecttypecode"] = "email";
            service.Create(attachment);
        }
     


/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Don't forget the send that email with which we just attached the csv string.

Happy Learning...!!

How to convert datatable into csv/excel file in C#

How to convert datatable into csv/excel file in C#

Often we require to convert the datatable in context in a c# code into a plain text csv (comma separated values) or a excel readable file. Here i have created a c#  console application code snipped to convert a sample datatable into csv notepad file or csv excel readable file. You can utilize it where you need it.

Hope it would be helpful:

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

using System;

using System.Text;

using System.Data;

using System.IO;

namespace ConsoleApplication1

{

    class Program

    {

        public  static void Main(string[] args)

        {

            //Create a sample datatable

            DataTable dt=new DataTable();

            dt.Columns.Add("FisrtName", typeof(string));

            dt.Columns.Add("LastName", typeof(string));

            DataRow dr1 = dt.NewRow();

            dr1["FisrtName"]="Vishal";

            dr1["LastName"]="Grade";

            dt.Rows.Add(dr1);

            DataRow dr2 = dt.NewRow();

            dr2["FisrtName"] = "Tony";

            dr2["LastName"] = "Stark";

            dt.Rows.Add(dr2);

            String result = DataTableToCSV(dt, ',');

            //Wrire the result comma seprated string to a csv file which can be opened in excel

            System.IO.File.WriteAllText(@"D:\\result.csv", result);

            //Wrire the result comma seprated string to a csv text file 

            System.IO.File.WriteAllText(@"D:\\result.txt", result);

            

            Console.Write(result);

            

        }

        //Function to convert datatble content into comma separated string

        public static string DataTableToCSV(DataTable datatable, char seperator)

        {

            StringBuilder sb = new StringBuilder();

            for (int i = 0; i < datatable.Columns.Count; i++)

            {

                sb.Append(datatable.Columns[i]);

                if (i < datatable.Columns.Count - 1)

                    sb.Append(seperator);

            }

            sb.AppendLine();

            foreach (DataRow dr in datatable.Rows)

            {

                for (int i = 0; i < datatable.Columns.Count; i++)

                {

                    sb.Append(dr[i].ToString());

                    if (i < datatable.Columns.Count - 1)

                        sb.Append(seperator);

                }

                sb.AppendLine();

            }

            return sb.ToString();

        }

    }

}

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Happy learning...!!