How to check email for file using c#?

Question

I receive an email with an excel file every week. I know there are probably better ways to accomplish my goal, but would it be possible to have a script task in SSIS that can open email, look for a specific file name as an attachment and then copy that file to another location?

Here is the scenario. This excel file is important for my team to have in a SQL database, and the provider of the excel source is only willing to email this excel file to us once per week. I then check my email, copy the file to a location where an SSIS dataflow task can then pick it up and insert it into a SQL table. I would like to automate this. So if my original approach is not doable, how else could this be automated? Aside from using a shared network location. Assume the excel file can ONLY come from the email. Using outlook/office 365, SSIS, SSMS, I have DBO access, and can use c#.

I'll admit that I'm ignorant on the email. If there is a procedure that the email client can actually execute to accomplish this, then I'd be all ears!

EDIT: I also have access to a network drive as I realize saving to my local machine may be impossible.

Answer 1

Simple Answer Yes it is Possible.

I had written a console program to process email on Office365 that I was also interfacing with SQL, so it definitely can be done. It isn't necessarily the easiest thing in the world but it is not too hard either.

You can use the Exchange Web Services (EWS) Managed API

Article on stating it is possible and the API documentation https://msdn.microsoft.com/en-us/library/office/dd877012(v=exchg.150).aspx

Github location where you can find the API (note this link is directly form Microsoft's Site) https://github.com/officedev/ews-managed-api

Link on how to reference the assembly which contains the second link above: https://msdn.microsoft.com/en-us/library/office/dn528373(v=exchg.150).aspx

Create and Connect to Service

string emailAddress = 'YourEmail@Domain.com';
ExchangeService exService = new ExchangeService(ExchangeVersion.Exchange2013_SP1);
exService.Credentials = new WebCredentials(emailAddress,"password");

you can autodiscover or if you know the URL just set it so 1 of these lines

exService.AutodiscoverUrl(_emailAddress, delegate { return true; });
exService.Url = new Uri("https://outlook.office365.com/EWS/Exchange.asmx");

Find your Inbox & a Folder to Move the File To After Being Processed:

FolderView folderView = new FolderView(1);
folderView.PropertySet = new PropertySet(BasePropertySet.IdOnly);
folderView.PropertySet.Add(FolderSchema.DisplayName);
folderView.Traversal = FolderTraversal.Deep;
SearchFilter searchFilter = new SearchFilter.IsEqualTo(FolderSchema.DisplayName, "ProcessedFolderName");
Folder Inbox = Folder.Bind(exService, WellKnownFolderName.Inbox);
FindFoldersResults folderResults = Inbox.FindFolders(searchFilter, folderView);
FolderId processedFolderId = folderResults.Folders[0].Id;

Find Messages That meet your criteria:

List<SearchFilter> searchFilterCollection = new List<SearchFilter();
searchFilterCollection.Add(new SearchFilter.ContainsSubstring(ItemSchema.Subject,"Words in Subject"));
searchFilterCollection.Add(new SearchFilter.IsEqualTo(ItemSchema.HasAttachments,true));
searchFilterCollection.Add(new SearchFilter.IsEqualTo(EmailMessageSchema.From,new EmailAddress("From@SendersDomain.com")));
SearchFilter searchFilter = new SearchFilter.SearchFilterCollection(LogicalOperator.And,searchFilterCollection);

ItemView view = new ItemView(50, 0, OffsetBasePoint.Beginning);
view.OrderBy.Add(ItemSchema.DateTimeReceived, SortDirection.Descending);
view.PropertySet = new PropertySet(BasePropertySet.IdOnly, ItemSchema.DateTimeReceived, ItemSchema.Attachments);
view.Traversal = ItemTraversal.Shallow;
FindItemsResults<Item> findResults = exService.FindItems(WellKnownFolderName.Inbox,searchFilter,view);

Process the results and save the attachments when done move the message to another folder so you don't keep importing the same one.

foreach (Item i in findResults.Items)
{
    foreach(FileAttachment attachment in i.Attachments)
    {
        attachment.Load(@"\\FilePathDirectory\" + attachment.FileName);
    }

    i.Move(processedFolderId);
}

you can expand the solution by testing if you get no results sending yourself an error message or throwing an error for SSIS to pickup and fail the job. It is likely that you will overwrite the file multiple times if you have multiple messages to process so you may consider adding something unique in the file name instead of just using the same one but that will present other challenges in SSIS as well.

anyway, its a start hope it helps.