Create a Excel File and Send by email with Microsoft Graph

Question

I am triying to create an Excel file and then send by email using my microsft email adress using Microsft Graph.

If the only thing that i do is send an email works fine, but if create the excel and then try send email using the same code stops working, no errors, simply stop working.

This is my code:

class Solds
        {
            public string Empres { get; set; }
            public string NClient { get; set; }
            public string Name { get; set; }
            public string PurchaseNumber { get; set; }
            public DateTime Date { get; set; }
            public string Codart { get; set; }
            public string Description { get; set; }
            public string Fampro { get; set; }
            public string Serpro { get; set; }
            public string Group { get; set; }
            public decimal Price { get; set; }
            public decimal Cost { get; set; }
            public string Seller { get; set; }
            public string Quarter { get; set; }
        }

static void Main(string[] args)
    {
        List<String> Destinations = new List<string>() { "myemail@mycompany.com" };
        List<string> Cc = new List<string>();
        List<System.IO.FileInfo> Filess = new List<System.IO.FileInfo>();

        List<Solds> lstSolds = GetData();

        SenMailUsingMicrosoftGraph(Destinations, Cc, "", "Text of the Body", "title of the mail", Filess);
      // GenerateExcel creates a Excel File (i use ClosedXML) and retuns a FileInfo
        Files.Add(GenerateExcel(lstSolds));

        
        SenMailUsingMicrosoftGraph(Destinations, Cc, "", "Text of the Body", "title of the mail", Filess);
    }


private static async void SenMailUsingMicrosoftGraph(List<String>Destinations, List<String>Cc, string HidenCopy, string Body, string Title, List<FileInfo>Filess);
{
ClientSecretCredential credential = new ClientSecretCredential("MyTenantID", "MyClientId", "MyClientSecret");

        
        List<Recipient> recipientsDestinatarios = new List<Recipient>();
        List<Recipient> recipientsCopias = new List<Recipient>();

        foreach (var c in Destinations)
        {
            recipientsDestinatarios.Add(
                new Recipient
                {
                    EmailAddress = new EmailAddress
                    {
                        Address = c
                    }
                });
        }

        foreach (var mail in Cc)
        {

            recipientsCopias.Add(
                new Recipient
                {
                    EmailAddress = new EmailAddress
                    {
                        Address = mail
                    }
                });
        }
        #endregion

        var message = new Microsoft.Graph.Message
        {
            Subject = Title,
            Body = new ItemBody
            {
                ContentType = BodyType.Html,
                Content = Body
            },
            ToRecipients = recipientsDestinatarios
            ,
            CcRecipients = recipientsCopias
            ,
            BccRecipients = new List<Recipient>()
            {
                new Recipient
                {
                    EmailAddress=new EmailAddress{Address=Hiden}
                }
            }


        };

        GraphServiceClient graphClient = new GraphServiceClient(credential);
        #endregion

        #region adjuntar ficheros
        var msgResult = await graphClient.Users["myemail@mycompany.com"].MailFolders.Drafts.Messages
                                    .Request()
                                    .WithMaxRetry(9)
                                    .AddAsync(message);
        foreach (var Archivo in Filess)
        {
            var attachmentContentSize = Archivo.Length;
            var attachmentItem = new AttachmentItem
            {
                AttachmentType = AttachmentType.File,
                Name = Archivo.Name,
                Size = attachmentContentSize,
            };

            //initiate the upload session for large files 
            var uploadSession = await graphClient.Users["myemail@mycompany.com"].Messages[msgResult.Id].Attachments
                                                                    .CreateUploadSession(attachmentItem)
                                                                    .Request()
                                                                    .PostAsync();

            var maxChunkSize = 1024 * 320;
            var allBytes = System.IO.File.ReadAllBytes(Archivo.FullName);

            using (var stream = new MemoryStream(allBytes))
            {
                stream.Position = 0;
                LargeFileUploadTask<FileAttachment> largeFileUploadTask = new LargeFileUploadTask<FileAttachment>(uploadSession, stream, maxChunkSize);

                await largeFileUploadTask.UploadAsync();
            }
        }
        await graphClient.Users["myemail@mycompany.com"].Messages[msgResult.Id].Send().Request().PostAsync();

}

private static FileInfo GenerateExcel(List<Solds> lstSolds)
        {
            
            System.IO.FileInfo file= new System.IO.FileInfo(@"E:\MyFolder\MyFile.xlsx");
            

            if (file.Exists) file.Delete();

            using (var wb = new XLWorkbook())
            {
                var ws = wb.Worksheets.Add("Example");
                ws.Cell(2, 1).InsertTable(lstSolds);

                wb.SaveAs(file.FullName);

            }
            return file;
        }
   private static List<ventas> ObtenerDatos()
        {
            List<ventas> lstSolds = new List<Solds>();

            string connString = @"Data Source=MyServer\SQLExpress; Initial Catalog=MyDataBAse;User Id=User;Password=password;";
            string sentenciaSQL = "QuarterSolds";

            using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString))
            {
                using (SqlCommand comm = new SqlCommand(sentenciaSQL, conn))
                {
                   
                    DateTime t = DateTime.Now;
                    conn.Open();
                    comm.CommandType = System.Data.CommandType.StoredProcedure;
                    comm.CommandTimeout = 240;
                    using (SqlDataReader reader = comm.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                Solds v = new Solds();
                                decimal d = 0;

                                v.Empres = reader.GetValue(0).ToString();
                                v.NClient = reader.GetValue(1).ToString();
                                v.Name = reader.GetValue(2).ToString();
                                v.PurchaseNumber = reader.GetValue(3).ToString();
                                v.Date = DateTime.TryParse(reader.GetValue(4).ToString(), out t) ? t : t;
                                v.Codart = reader.GetValue(5).ToString();
                                v.Description = reader.GetValue(6).ToString();
                                v.Fampro = reader.GetValue(7).ToString();
                                v.Serpro = reader.GetValue(8).ToString();
                                v.Group = reader.GetValue(9).ToString();
                                v.Price = decimal.TryParse(reader.GetValue(10).ToString(), out d) ? d : 0;
                                v.Cost = decimal.TryParse(reader.GetValue(11).ToString(), out d) ? d : 0;
                                v.Seller = reader.GetValue(12).ToString();
                                v.Quarter = reader.GetValue(13).ToString();


                                lstSolds.Add(v);
                            }
                        }
                        else Console.WriteLine("No lines");
                    }
                  
                }
            }

If i execute this first call to my method SenMailUsingMicrosoftGraph works fine and sends an email. But if i call again to SenMailUsingMicrosoftGraph after creating the Excel, the program stops when arrives to:

var msgResult = await graphClient.Users["myemail@mycompany.com"].MailFolders.Drafts.Messages
                                    .Request()
                                    .WithMaxRetry(9)
                                    .AddAsync(message);

Any suggestions?

Answer 1

Make your code really async. Now your program doesn't wait for the response from Graph API and ends immediately after the second call of SenMailUsingMicrosoftGraph.

Use static async Task Main(string[] args), private static async Task SenMailUsingMicrosoftGraph and await before SenMailUsingMicrosoftGraph.

static async Task Main(string[] args)
{
    List<String> Destinations = new List<string>() { "myemail@mycompany.com" };
    List<string> Cc = new List<string>();
    List<System.IO.FileInfo> Filess = new List<System.IO.FileInfo>();

    List<Solds> lstSolds = GetData();

    await SenMailUsingMicrosoftGraph(Destinations, Cc, "", "Text of the Body", "title of the mail", Filess);
  // GenerateExcel creates a Excel File (i use ClosedXML) and retuns a FileInfo
    Files.Add(GenerateExcel(lstSolds));

    
    await SenMailUsingMicrosoftGraph(Destinations, Cc, "", "Text of the Body", "title of the mail", Filess);
}

private static async Task SenMailUsingMicrosoftGraph
{
...
}

Answer 2

Whilst debugging, go to 'Exception settings' and click on the box 'Common Language Runtime Exception' so that it turns into a checkmark.

You've probably disabled the specific error being thrown.

After this you'll need to restart debugging.