SSIS MS Outlook Email Attachment

SSIS and Import/Export
1

I have been able to save MS Outlook email attachments by using MS Access VBA. This has been achieved on a client side.

On the server side, I will need to create something similar (either in SQL Server or SSIS). I have researched into this and found third party controls. I think I also found some C# code that did it too.

The tricky part is on client side, the VBA automatically uses my email account. How would I use a different email account to log into MS Outlook and save the attachment on server side?

2

so the vba code uses your account even on another user's desktop?

have you tested things on another client's computer?

3

If you get to the point you need to install Outlook on a server, you're doing it wrong. You need to look into getting the email directly from your Exchange server, authenticating with whatever credentials make sense for what you want to access.

4

Hi Yosiasz, I have not tried with another user account yet but will let you know and update this thread once I attempt to do so.

Hi Andyc, I agree that installation of Office on the server is probably not the best method. But we don't have an Exchange Server since we are on Office 365.

5

I believe you can access Office 365 via IMAP. That would be an infinitely better idea than trying to automate Outlook.

6

Hi Andy, can you provide a link or example? Maybe we are talking about the samething. Here is my VBA automation code to initialize to MS Outlook:

Dim mfInbox As MAPIFolder

'B---------------- ***** Initialize and Connect to the MS Outlook Inbox 2018_08_03(10:56 AM) ***** ----------------B
Dim oNS As Outlook.Namespace
Set oNS = GetNamespace("MAPI")
Set mfInbox = oNS.GetDefaultFolder(olFolderInbox)
If mfInbox.Items.Count = 0 Then
    MsgBox "There are no messages in the Inbox.", vbInformation, "Nothing Found"
    Exit Sub
End If
'E---------------- ***** Initialize and Connect to the MS Outlook Inbox 2018_08_03(10:56 AM) ***** ----------------E

If I go this route, then I still would need to install the bare minimum Office drivers to the server. I have not tried to use another account yet to run this code but when running locally, it works. Is this the same IMAP that you are referring to?

7

How about using EWS?
https://blogs.msdn.microsoft.com/webdav_101/2015/05/03/getting-started-with-ews/

1 Like
8

listen to bitsmed...on everything he posts.
EWS is super nice but a bit bloated in the sense that it gives you EVERYTHING. A bit too much but that is what I use and works amazingly

1 Like
9

Hi Bitsmed, is that different from Exchange Server since it is Exchange Web Services. It will connect to Office 365?

Thank You Bitsmed and Yosiasz

10

Or "just" use built in SMTP.
Here's a smalle snippet I use (not VBA though, it's C# - sorry):

using System.Net;
using System.Net.Mail;
using System.Net.Mime;
.
.
.
SmtpClient mclient=new SmtpClient(mailhost);
MailMessage mail=new MailMessage(mailuseraccount,mailaddrress,mailsubject,mailbody);
mail.Attachments.Add(new Attachment(@"c:\some folder\some file.txt"));
mclient.Credentials=new NetworkCredential(mailuseraccount,mailpassword);
mclient.ServicePoint.MaxIdleTime=1;
mclient.Send(mail);
mail.Dispose();
mail=null;

Edit: @yosiasz is right. EWS is a bit bloated, but you can do everything - even create Word/Excel/PowerPoint.

11

We use Exchange Server, but documentation states, it also work with Office 365.

12

No, IMAP is a method for talking directly to an email server by acting as a mail client. It's a fairly common and straightforward protocol and you can probably find a helper library for whatever language you're using. A big advantage to IMAP over just about anything else is that it's pretty much supported by every mail server going, so you aren't tied to an Exchange (or O365) based solution.

As to EWS, you should note it has been deprecated and the suggestion is you should look to Microsoft Graph these days: https://developer.microsoft.com/en-us/graph/blogs/upcoming-changes-to-exchange-web-services-ews-api-for-office-365/

SMTP, on the other hand, is for sending mail and not receiving it.

1 Like
13

My bad - I thought we were discussing sending attachments in mail - sorry.

1 Like
14

Yes, I was just about to ask Bitsmed, can EWS save attachments from emails. Regardless, thank you Bitsmed!

AndyC, thank you for your knowledge sharing and I will look into IMAP.

15

Yes, EWS can save attachments.