Wednesday 21 January 2009

Read an Excel document and Send an email from outlook in c# .net

This is a handy example if you need to read an excel document and/or send an email from outlook in some c# code.

Remember to add the following references (Project > Add Reference > COM)

Microsoft Excel 12.0 Object Library
Microsoft Office 12.0 Object Library
Microsoft Outlook 12.0 Object Library

if you have older versions of office , they should work too.

using System;
using System.Collections.Generic;

using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;

using Outlook = Microsoft.Office.Interop.Outlook;
using Microsoft.Office.Core;

namespace StatusNotifier
{
class StatusNotifier
{
static void Main(string[] args)

{
string Path = @"D:\MyExcelFile.xls";
// initialize the Excel Application class

Excel.ApplicationClass app = new Excel.ApplicationClass();

// create the workbook object by opening the excel file.
Excel.Workbook workBook = app.Workbooks.Open(Path,
0,
true,
5,
"",
"",
true,
Excel.XlPlatform.xlWindows,
"\t",
false,
false,
0,
true,
1,
0);

// get the active worksheet using sheet name or active sheet
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];

int index = 0;
// This row,column index should be changed as per your need.
// i.e. which cell in the excel you are interesting to read.
object rowIndex = 2;


object colA = 1;
object colB = 2 ;


StringBuilder message = new StringBuilder();

message.AppendLine("My Message");

message.AppendLine("--------------");
message.AppendLine("");


//----------------------------

//READ EXCEL WORKBOOK
//------------------------------

try
{
while (((Excel.Range)workSheet.Cells[rowIndex, colDateRaised]).Value2 != null)

{
rowIndex = 2 + index;
DateTime dateRaised = DateTime.FromOADate(double.Parse(((Excel.Range)workSheet.Cells[rowIndex, colA]).Value2.ToString()));

string description = ((Excel.Range)workSheet.Cells[rowIndex, colB]).Value2.ToString();



message.AppendLine(rowIndex + " DateRaised :" + dateRaised.ToShortDateString() + "Description: " + description) ;



index++;
}
}
catch (Exception ex)

{
app.Quit();
Console.WriteLine(ex.Message);

}

message.AppendLine("For more inforamtion please see:");
message.AppendLine(Path);

Console.WriteLine("Message:\n " + message);

//----------------------------
//CREATE OUTLOOK MESSAGE
//------------------------------

try
{
// Create the Outlook application by using inline initialization.

Outlook.Application oApp = new Outlook.Application();

//Create the new message by using the simplest approach.
Outlook.MailItem oMsg = (Outlook.MailItem)oApp.CreateItem(Outlook.OlItemType.olMailItem);

//Add a recipient.
// TODO: Change the following recipient where appropriate.
Outlook.Recipient oRecip = (Outlook.Recipient)oMsg.Recipients.Add("someone@somewhere.com");

oRecip.Resolve();

//Set the basic properties.
oMsg.Subject = "Status Update";

oMsg.Body = message.ToString();

//Add an attachment.

// TODO: change file path where appropriate
String sSource = "C:\\setupxlg.txt";
String sDisplayName = "MyFirstAttachment";

int iPosition = (int)oMsg.Body.Length + 1;

int iAttachType = (int)Outlook.OlAttachmentType.olByValue;

Outlook.Attachment oAttach = oMsg.Attachments.Add(sSource, iAttachType, iPosition, sDisplayName);

// If you want to, display the message.
// oMsg.Display(true); //modal

//Send the message.
oMsg.Save();

oMsg.Send();

//Explicitly release objects.
oRecip = null;

oAttach = null;
oMsg = null;
oApp = null;

}

// Simple error handler.
catch (Exception e)
{

Console.WriteLine("{0} Exception caught: ", e);
}



}
}
}

This code is based upon examples from the following links:

Excel: http://www.codeproject.com/KB/cs/Excel_Application_in_C_.aspx
Outlook: http://support.microsoft.com/kb/310263

1 comment:

  1. This comment has been removed by a blog administrator.

    ReplyDelete