Extract outlook email attachment files and save in sql

Joined
Feb 2, 2022
Messages
15
Programming Experience
Beginner
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

C#:
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

1644948489897.png
 
Last edited:

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,469
Location
Chesapeake, VA
Programming Experience
10+
You'll need to access the Attachments property of a MailItem to get the list of attachments:

As you iterate over that collection of attachments, you'll get back an Attachment object.

If you were writing straight on MAPI code, you can access the data stream that has the file data. Using the Outlook Object Model, I believe that you are forced to use one of the two methods that saves the data stream out to a file, and then you can open the file to get the data stream.
 

andrewchatsama6

New member
Joined
Jul 21, 2022
Messages
1
Programming Experience
1-3
C#:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using Outlook = Microsoft.Office.Interop.Outlook;
using System.Threading;

namespace OutlookAttachmentExtractorApp
{
    public partial class Form1 : Form
    {

        // Path where attachments will be saved
        static readonly string basePath = @"C:\Users\achatsama\Desktop\emailfolder\";
        // Integer to store to the total size of all the files - displayed
        // after processing to indicate possible PST file size saving
        static int totalfilesize = 0;

        // Variables
        static Outlook.Application Application = new Outlook.Application();
        Outlook.Accounts accounts = Application.Session.Accounts;
        Label lblStatusObj = new Label();
      

        public Form1()
        {
            InitializeComponent();
            lblStatusObj = lblStatus;
            lblStatusObj.Text = "Ready";
        }

        static void EnumerateFoldersInDefaultStore()
        {
            Outlook.Application Application = new Outlook.Application();
            Outlook.Folder root = Application.Session.DefaultStore.GetRootFolder() as Outlook.Folder;
            EnumerateFolders(root);
        }

        // Uses recursion to enumerate Outlook subfolders.
        static void EnumerateFolders(Outlook.Folder folder)
        {
            Outlook.Folders childFolders = folder.Folders;
            if (childFolders.Count > 0)
            {
                // loop through each childFolder (aka sub-folder) in current folder
                foreach (Outlook.Folder childFolder in childFolders)
                {
                    // We only want Inbox folders - ignore Contacts and others
                    if (childFolder.FolderPath.Contains("Inbox"))
                    {
                         //Write the folder path.
                       Console.WriteLine(childFolder.FolderPath);
                        //Call EnumerateFolders using childFolder,
                        //a to see if there are any sub-folders within this one
                       EnumerateFolders(childFolder);
                    }
                }
            }
            // pass folder to IterateMessages which processes individual email messages
            Console.WriteLine("Checking in " + folder.FolderPath);
            IterateMessages(folder);
        }

        static void IterateMessages(Outlook.Folder folder)
        {
            // attachment extensions to save
            string[] extensionsArray = {".txt",".csv",".xls"};

            // Iterate through all items ("messages") in a folder
            var fi = folder.Items;
            if (fi != null)
            {

                try
                {
                    foreach (Object item in fi)
                    {
                        Outlook.MailItem mi = (Outlook.MailItem)item;
                        var attachments = mi.Attachments;
                        // Only process item if it has one or more attachments
                        if (attachments.Count != 0)
                        {

                            // Create a directory to store the attachment
                            if (!Directory.Exists(basePath))
                            {
                                Directory.CreateDirectory(basePath);
                            }

                            // Loop through each attachment
                            for (int i = 1; i <= mi.Attachments.Count; i++)
                            {
                                // Check wither any of the strings in the
                                // extensionsArray are contained within the filename
                                var fn = mi.Attachments.FileName.ToLower();
                                if (extensionsArray.Any(fn.Contains))
                                {

                                    // Create a further sub-folder for the sender
                                    if (!Directory.Exists(basePath))
                                    {
                                        Directory.CreateDirectory(basePath);
                                    }
                                    totalfilesize = totalfilesize + mi.Attachments.Size;
                                    if (!File.Exists(basePath + mi.Attachments.FileName))
                                    {
                                        Console.WriteLine("Saving " + mi.Attachments.FileName);
                                        mi.Attachments.SaveAsFile(basePath  +
                        mi.Attachments.FileName);
                                        
                                        // Uncomment next line to delete attachment after saving it
                                         mi.Attachments.Delete();
                                    }
                                    else
                                    {
                                        Console.WriteLine("Already saved " + mi.Attachments.FileName);
                                    }
                                }
                            }
                        }
                    }
                }
                  
                catch (Exception e)
                {
                    // Console.WriteLine("An error occurred: '{0}'", e);
                }
            }
        }
        // Retrieves the email address for a given account object
        static string EnumerateAccountEmailAddress(Outlook.Account account)
        {
            try
            {
                if (string.IsNullOrEmpty(account.SmtpAddress) || string.IsNullOrEmpty(account.UserName))
                {
                    Outlook.AddressEntry oAE = account.CurrentUser.AddressEntry as Outlook.AddressEntry;
                    if (oAE.Type == "EX")
                    {
                        Outlook.ExchangeUser oEU = oAE.GetExchangeUser() as Outlook.ExchangeUser;
                        return oEU.PrimarySmtpAddress;
                    }
                    else
                    {
                        return oAE.Address;
                    }
                }
                else
                {
                    return account.SmtpAddress;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return "";
            }
        }

        public void EnumerateAccounts()
        {
            progressBar.Value = progressBar.Minimum;
            lblStatus.Text = "Fetching Mailboxes...";
            
            int id;

            id = 1;
            foreach (Outlook.Account account in accounts)
            {
                listViewMailboxes.Items.Clear();
                ListViewItem listViewItem = new ListViewItem(id.ToString());
                listViewItem.SubItems.Add(EnumerateAccountEmailAddress(account));
                listViewMailboxes.Items.Add(listViewItem);
                id++;
            }
            btnExtract.Enabled = true;
            lblStatus.Text = "Select Mailbox to extract ...";
        }

        int selected;
        public void selectMailbox()
        {
            try
            {
                selected = Int32.Parse(listViewMailboxes.SelectedItems[0].SubItems[0].Text);
            }
            catch
            {
                MessageBox.Show("Select Mail address to extract from first.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                lblStatus.Text = "Select Mailbox to extract attachments...";
                return;
            }
            
            if (selected >= 1)
            {
                progressBar.Enabled = true;
                progressBar.Style = ProgressBarStyle.Marquee;
                progressBar.MarqueeAnimationSpeed = 20;
                BackgroundWorker backgroundWorker = new BackgroundWorker();
                backgroundWorker.DoWork += BackgroundWorker_FindEmailAttachments;
                backgroundWorker.RunWorkerCompleted += backgroundWorker_RunWorkerCompleted;
                backgroundWorker.RunWorkerAsync();
            
            }
            else
            {
                MessageBox.Show("Invalid Account Selected");
            }
        }

        private void backgroundWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            progressBar.Style = ProgressBarStyle.Continuous;
            progressBar.MarqueeAnimationSpeed = 0;
            progressBar.Value = 100;
            lblStatus.Text = "Completed";
            //btnImages.Enabled = true;
        }

        Outlook.Folder selectedFolder;
        private void BackgroundWorker_FindEmailAttachments(object sender, DoWorkEventArgs e)
        {
            selectedFolder = Application.Session.DefaultStore.GetRootFolder() as Outlook.Folder;
            selectedFolder = GetFolder(@"\\" + accounts[selected].DisplayName);
            EnumerateFolders(selectedFolder);
            Thread.Sleep(7500);
        }

        // Returns Folder object based on folder path
        private Outlook.Folder GetFolder(string folderPath)
        {
            Console.WriteLine("Looking for: " + folderPath);
            Outlook.Folder folder;
            string backslash = @"\";
            try
            {
                if (folderPath.StartsWith(@"\\"))
                {
                    folderPath = folderPath.Remove(0, 2);
                }
                String[] folders = folderPath.Split(backslash.ToCharArray());
                Outlook.Application Application = new Outlook.Application();
                folder = Application.Session.Folders[folders[0]] as Outlook.Folder;
                if (folder != null)
                {
                    for (int i = 1; i <= folders.GetUpperBound(0); i++)
                    {
                        Outlook.Folders subFolders = folder.Folders;
                        folder = subFolders[folders] as Outlook.Folder;
                        if (folder == null)
                        {
                            return null;
                        }
                    }
                }
                return folder;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return null;
            }
        }

        private void btnSearchMail_Click(object sender, EventArgs e)
        {
            EnumerateAccounts();
        }

        private void listViewMailboxes_DoubleClick(object sender, EventArgs e)
        {
            ListView.SelectedIndexCollection selIndx = listViewMailboxes.SelectedIndices;
            if (selIndx.Count > 0)
            {
                selectMailbox();
            }

        }

        private void btnExtract_Click(object sender, EventArgs e)
        {
            lblStatus.Text = "Extracting...";
            selectMailbox();
        }

       

        private void label1_Click(object sender, EventArgs e)
        {

        }

        private void listViewMailboxes_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            List<string> stringList = new List<string>();
            //Get filenames from directory
            string[] files = Directory.GetFiles(@"C:\Users\achatsama\Desktop\test\");

            //loop through array of files, read their contents and add to collection List<string> stringList
            foreach (string file in files)
            {
                stringList.Add(System.IO.File.ReadAllText(file));
            }

            //Join to one string variable List<string> values
            string resultText = string.Join("", stringList.ToArray());

            //Write result to textFile
            using (StreamWriter outFile = new StreamWriter(@"C:\Users\achatsama\Desktop\test\result.txt"))
            {
                outFile.Write(resultText);
            }
            MessageBox.Show("File Created");
        }

    }

}
that is the code i used but its not saving recent email attachments..help on that but its saving old attachment
 
Last edited by a moderator:

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,469
Location
Chesapeake, VA
Programming Experience
10+
Thanks for sharing your code. In the future, please post your code in code tags.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,469
Location
Chesapeake, VA
Programming Experience
10+
Top Bottom