Extract outlook email attachment files and save in sql

Question

I'm trying to save the Outlook attachment file email and save in sql. I tried this following code and it only saved a numerical values

using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Text;  
using System.Threading.Tasks;  
using System.Data.SqlClient;  
using System.Data;  
using Outlook = Microsoft.Office.Interop.Outlook;  
  
  
namespace RetrieveEmail  
{  
    public class Program  
    {  
         static void Main(string[] args)  
        {  
            Outlook.Application oLk = new Outlook.Application();  
            Outlook._NameSpace olNS = oLk.GetNamespace("MAPI");  
            olNS.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderSentMail);  
  
  
            Outlook.Items oItems = oFolderIn.Items;  
  
            foreach (object item in oFolderIn.Items)  
            {  
                if (item is Outlook.MailItem oMailItem)  
                {  
                      
                    SqlConnection con = new SqlConnection(@"Data Source=\SQLEXPRESS; initial catalog=EmailReply;Integrated Security=True");  
                  
                    SqlCommand cmd = new SqlCommand("INSERT INTO Emails (SenderName, Subject, Body, Attachment) VALUES (@SenderName, @Subject, @Body, @Attachment)", con);  
                    
                    cmd.Parameters.AddWithValue("@SenderName", oMailItem.SenderName);  
                    cmd.Parameters.AddWithValue("@Subject", oMailItem.Subject);  
                    cmd.Parameters.AddWithValue("@Body", oMailItem.Body);  
                    cmd.Parameters.AddWithValue("@Attachment", oMailItem.EnableSharedAttachments); // I tried this code to extract Outlook attachment file email  
                      
  
                    con.Open();  
                    int k = cmd.ExecuteNonQuery();  
                    if (k != 0)  
                    {  
                        Console.WriteLine("Record Inserted Succesfully into the Database");  
  
                    }  
                    con.Close();  
                }  
            }  
        }  
    }  
}  

This is what it saved in my sql, it didn't save the attachment I'm trying to save the attachment file with extension of (.docx .pdf .excel .pptx)
Note: I used varbinary(MAX) for attachment data type in sql

Answer 1

@ika palad , as others suggested, we need to use _MailItem.Attachments Property to get the information about attahment files from email.

Based on my research, I find that we could not convert the files to the byte array in database directly.

Therfore, I folllow the following steps to save the attchment files to database.

First, Please download files and convert files to byte array, then delete the files.

Second, Please create List of byte array to store the byte array.

Third, Please convert List of byte array to byte array.

Finally, Please store the byte array to the database.

Code example:

internal class Program  
    {  
        static void Main(string[] args)  
        {  
            Outlook.Application oLk = new Outlook.Application();  
            Outlook._NameSpace olNS = oLk.GetNamespace("MAPI");  
            var oFolderIn=olNS.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderDeletedItems);  
            List<byte[]> data = new List<byte[]>();  
            List<string> file = new List<string>();  
            foreach (object item in oFolderIn.Items)  
            {  
                if (item is Outlook.MailItem oMailItem)  
                {  
                    if(oMailItem.Subject.Contains("Test"))  
                    {  
                        file = oMailItem.Attachments.Cast<Outlook.Attachment>().Select(i => i.FileName).ToList();  
                        for (int i = 1; i < oMailItem.Attachments.Count+1; i++)  
                        {  
                            string path = @"C:\Users\username\Desktop\" + oMailItem.Attachments[i].FileName;  
                            oMailItem.Attachments[i].SaveAsFile(path);  
                            byte[] b = FileToByteArray(path);  
                            data.Add(b);  
                            File.Delete(path);  
                        }  
                        byte[] total = ListToFileToByteArray(data);  
                        SqlConnection con = new SqlConnection(@"connstr");  
  
                        SqlCommand cmd = new SqlCommand("INSERT INTO Emails (SenderName, Subject, Body, Attachment) VALUES (@SenderName, @Subject, @Body, @Attachment)", con);  
  
                        cmd.Parameters.AddWithValue("@SenderName", oMailItem.SenderName);  
                        cmd.Parameters.AddWithValue("@Subject", oMailItem.Subject);  
                        cmd.Parameters.AddWithValue("@Body", oMailItem.Body);  
                        cmd.Parameters.AddWithValue("@Attachment", total);   
  
  
                        con.Open();  
                        int k = cmd.ExecuteNonQuery();  
                        if (k != 0)  
                        {  
                            Console.WriteLine("Record Inserted Succesfully into the Database");  
  
                        }  
                        con.Close();  
  
                    }  
  
  
  
  
                }  
            }  
  
  
            SqlConnection connection = new SqlConnection(@"connstr");  
  
            SqlCommand sqlCommand = new SqlCommand("select * from Emails", connection);  
            connection.Open();  
            SqlDataReader reader = sqlCommand.ExecuteReader();  
            while(reader.Read())  
            {  
                byte[] total = (byte[])reader["Attachment"];  
                List<byte[]> bytes = ByteArrayToList(total);  
                for (int i = 0; i < bytes.Count; i++)  
                {  
                    File.WriteAllBytes(file[i], bytes[i]);         //Code to download file from database  
                }  
            }  
  
            Console.WriteLine("success");  
        }  
  
  
        public static byte[] FileToByteArray(string fileName)  
        {  
            byte[] fileData = null;  
  
            using (FileStream fs = File.OpenRead(fileName))  
            {  
                var binaryReader = new BinaryReader(fs);  
                fileData = binaryReader.ReadBytes((int)fs.Length);  
            }  
            return fileData;  
        }  
  
        public static byte[] ListToFileToByteArray(List<byte[]>list)  
        {  
            var binFormatter = new BinaryFormatter();  
            var mStream = new MemoryStream();  
            binFormatter.Serialize(mStream, list);  
  
            //This gives you the byte array.  
            return mStream.ToArray();  
        }  
        public static List<byte[]>ByteArrayToList(byte[]b)  
        {  
            var mStream = new MemoryStream();  
            var binFormatter = new BinaryFormatter();  
  
            // Where 'objectBytes' is your byte array.  
            mStream.Write(b, 0, b.Length);  
            mStream.Position = 0;  
  
            var myObject = binFormatter.Deserialize(mStream) as List<byte[]>;  
            return myObject;  
        }  
    }  

Hope this could help you.

Best Regards,
Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.