OutlookAddin Solution

[DanHood]

Hi,

I am currently creating Add-in using Visual Studio Tools for Office which
will automate following manual actions:

attachment of the excel file with the unique name (eg excel file starting
with MLC word)
and sending it to multiple number of email address that have unique name +
number logic (eg MLC1, MLC2,)

At this stage I have resolved two most important issues : mapping the
recipients with corresponding files and mappin Z:\ directory to SharePoint so
as to open the communication between OutlookAddin and DocumentLibrary(stored
excel files) . I have tested it with small fund sample and it takes aroun
1.5-2 second to send an email with appropriate attachments to desired
recipient. That's good, but what if there is 156 recipients?

Do I really have to create Outlook Addin with almost 12 000 lines of code,
as I would be just copying general parts of the code and just changing search
entries or it is possible to create a global property or Array List etc that
would actually reduce the need for copying of the code all over again. I
will show you code in VB.Net as I am not sure which of two (vb.net, c#) you
use in your code projects, and let me observe that GetFiles really does
outstanding job, and its introduction in Net 2.0 framework was more than
welcome. I would highly appreciate if your suggestions are not mare general
words but are followed up with general code sample.

Imports Office = Microsoft.Office.Core

Imports System.Xml

Imports System.IO

Imports System.Configuration

Imports System.Collections

Imports System.Net.Mail

Imports System.Web

public class ThisApplication

Private WithEvents _Explorers As Outlook.Explorers

Private WithEvents _Explorer As Outlook.Explorer

Private _contactFolder As Outlook.Explorer

Private _mapiContact As Outlook.MAPIFolder

Private _helpMenuIdex As Object

Private _menuBar As Office.CommandBar

Private WithEvents _Email As Outlook.MailItem

Private Const COMMAND_BAR As String = "EDC"

Private Const BUTTON_NAME As String = "Send_EDC"

Private WithEvents myButton As Office.CommandBarButton

Private Const folder As String = "Z:\GeneratedForms\2006-08"

Private Sub ThisApplication_Startup(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Startup

_menuBar = Me.ActiveExplorer().CommandBars.Add(COMMAND_BAR,
Office.MsoBarPosition.msoBarTop, False, True)

_menuBar.Visible = True

myButton =
CType(_menuBar.Controls.Add(Office.MsoControlType.msoControlButton,
Type.Missing, Type.Missing, Type.Missing, True), Office.CommandBarButton)

myButton.Caption = BUTTON_NAME

myButton.Style = Office.MsoButtonStyle.msoButtonIconAndCaption

myButton.Enabled = True

End Sub



Private Sub myButton_Click(ByVal Ctrl As
Microsoft.Office.Core.CommandBarButton, ByRef CancelDefault As Boolean)
Handles myButton.Click

Try

Call SendEmailtoContacts()

Catch ex As Exception

ex.ToString()

End Try

End Sub

Public Sub SendEmailtoContacts()

Dim objContactItem As Outlook.ContactItem

Dim outlookNameSpace As Outlook.NameSpace = Me.GetNamespace("MAPI")

Dim subjectEmail As String = "EDC"

Dim bodyEmail As String = "text"

'create and instance of the contact folder

Try

_mapiContact =
outlookNameSpace.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderContacts)

For Each objContactItem In _mapiContact.Items.Restrict("[CompanyName] =
Intech")

CreateEmailItem(subjectEmail, objContactItem.Email1Address, bodyEmail,
"EDC_IMD_Forms")

Next

Catch objexc As Exception

objexc.ToString()

Finally

End Try

End Sub





Private Sub CreateEmailItem(ByVal subjectEmail As String, _

ByVal toEmail As String, ByVal bodyEmail As String, _

ByVal AttachmentFiles As String)

Dim eMail As Outlook.MailItem = Me.CreateItem _

(Outlook.OlItemType.olMailItem)

With eMail

..Subject = subjectEmail

..To = toEmail

..Body = bodyEmail

..Importance = Outlook.OlImportance.olImportanceLow

For Each foundFile As String In My.Computer.FileSystem.GetFiles( folder,
FileIO.SearchOption.SearchTopLevelOnly, "Aberdeen*.*")

..Attachments.Add(foundFile)

Next

..Send()

End With

End Sub

 

[Ken Slovak - [MVP - Outlook]]

I'm not quite sure what the question is here. What code copying are you
talking about? If you send out an email with 156 recipients it should go out
as one email with that many recipients, unless you are sending separate
emails.

Hi,

I am currently creating Add-in using Visual Studio Tools for Office which
will automate following manual actions:

attachment of the excel file with the unique name (eg excel file starting
with MLC word)
and sending it to multiple number of email address that have unique name +
number logic (eg MLC1, MLC2,)

At this stage I have resolved two most important issues : mapping the
recipients with corresponding files and mappin Z:\ directory to SharePoint
so
as to open the communication between OutlookAddin and
DocumentLibrary(stored
excel files) . I have tested it with small fund sample and it takes aroun
1.5-2 second to send an email with appropriate attachments to desired
recipient. That's good, but what if there is 156 recipients?

Do I really have to create Outlook Addin with almost 12 000 lines of code,
as I would be just copying general parts of the code and just changing
search
entries or it is possible to create a global property or Array List etc
that
would actually reduce the need for copying of the code all over again. I
will show you code in VB.Net as I am not sure which of two (vb.net, c#)
you
use in your code projects, and let me observe that GetFiles really does
outstanding job, and its introduction in Net 2.0 framework was more than
welcome. I would highly appreciate if your suggestions are not mare
general
words but are followed up with general code sample.

Imports Office = Microsoft.Office.Core

Imports System.Xml

Imports System.IO

Imports System.Configuration

Imports System.Collections

Imports System.Net.Mail

Imports System.Web

public class ThisApplication

Private WithEvents _Explorers As Outlook.Explorers

Private WithEvents _Explorer As Outlook.Explorer

Private _contactFolder As Outlook.Explorer

Private _mapiContact As Outlook.MAPIFolder

Private _helpMenuIdex As Object

Private _menuBar As Office.CommandBar

Private WithEvents _Email As Outlook.MailItem

Private Const COMMAND_BAR As String = "EDC"

Private Const BUTTON_NAME As String = "Send_EDC"

Private WithEvents myButton As Office.CommandBarButton

Private Const folder As String = "Z:\GeneratedForms\2006-08"

Private Sub ThisApplication_Startup(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Startup

_menuBar = Me.ActiveExplorer().CommandBars.Add(COMMAND_BAR,
Office.MsoBarPosition.msoBarTop, False, True)

_menuBar.Visible = True

myButton =
CType(_menuBar.Controls.Add(Office.MsoControlType.msoControlButton,
Type.Missing, Type.Missing, Type.Missing, True), Office.CommandBarButton)

myButton.Caption = BUTTON_NAME

myButton.Style = Office.MsoButtonStyle.msoButtonIconAndCaption

myButton.Enabled = True

End Sub



Private Sub myButton_Click(ByVal Ctrl As
Microsoft.Office.Core.CommandBarButton, ByRef CancelDefault As Boolean)
Handles myButton.Click

Try

Call SendEmailtoContacts()

Catch ex As Exception

ex.ToString()

End Try

End Sub

Public Sub SendEmailtoContacts()

Dim objContactItem As Outlook.ContactItem

Dim outlookNameSpace As Outlook.NameSpace = Me.GetNamespace("MAPI")

Dim subjectEmail As String = "EDC"

Dim bodyEmail As String = "text"

'create and instance of the contact folder

Try

_mapiContact =
outlookNameSpace.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderContacts)

For Each objContactItem In _mapiContact.Items.Restrict("[CompanyName] =
Intech")

CreateEmailItem(subjectEmail, objContactItem.Email1Address, bodyEmail,
"EDC_IMD_Forms")

Next

Catch objexc As Exception

objexc.ToString()

Finally

End Try

End Sub





Private Sub CreateEmailItem(ByVal subjectEmail As String, _

ByVal toEmail As String, ByVal bodyEmail As String, _

ByVal AttachmentFiles As String)

Dim eMail As Outlook.MailItem = Me.CreateItem _

(Outlook.OlItemType.olMailItem)

With eMail

.Subject = subjectEmail

.To = toEmail

.Body = bodyEmail

.Importance = Outlook.OlImportance.olImportanceLow

For Each foundFile As String In My.Computer.FileSystem.GetFiles( folder,
FileIO.SearchOption.SearchTopLevelOnly, "Aberdeen*.*")

.Attachments.Add(foundFile)

Next

.Send()

End With

End Sub

 

[DanHood]

Hi Ken,

thanks for your email. Yes I am sending seperate emails to seperate
recipients.
I can understand frow where you are coming, so I will try this time to be
more descreptive (as the last time I gave a code).

At the moment I have 156 different funds that participate in our monthly
survey. To each fund on monthly basis we send excel files that are specific
for each fund. The reason why I have unique excel file for each fund is that
code behind excel file connects them with SQL database where I store
information for each of them.

So the logic is simple, all what has to be done is to map correct recipient
with correct excel files. However, at this stage I have only come to the
point to create code that will search through contact list and select all
recipients with the same company field property, then the code will search
through folder where excel files are stored and attach all excel files that
start with "name of the fund"*.*.

This code works fine but the problem is that it is static and not dynamic
code, and if I want to apply code to other 154 funds then I should copy same
lines of code and only change "name of fund" search criteria. And if I do
that (I just applied a simple mathematic) I would come up with 12 000 of
almost indentical lines of code apart of the name of the fund in both
searching methods (for contact, and for excel file).

Hope this is much more understandable, but what I am hoping is to get an
advice or lead so as to create general code, that will create an email with
an attachment only if company filed in contact folder is equal to start of
the excel file name in excel folder. Or any other solution that would not
dramatically affect performance.

Otherwise I might use timer, to call sub , and certainly open to any other
your suggestion . Cheers d

I'm not quite sure what the question is here. What code copying are you
talking about? If you send out an email with 156 recipients it should go out
as one email with that many recipients, unless you are sending separate
emails.

Hi,

I am currently creating Add-in using Visual Studio Tools for Office which
will automate following manual actions:

attachment of the excel file with the unique name (eg excel file starting
with MLC word)
and sending it to multiple number of email address that have unique name +
number logic (eg MLC1, MLC2,)

At this stage I have resolved two most important issues : mapping the
recipients with corresponding files and mappin Z:\ directory to SharePoint
so
as to open the communication between OutlookAddin and
DocumentLibrary(stored
excel files) . I have tested it with small fund sample and it takes aroun
1.5-2 second to send an email with appropriate attachments to desired
recipient. That's good, but what if there is 156 recipients?

Do I really have to create Outlook Addin with almost 12 000 lines of code,
as I would be just copying general parts of the code and just changing
search
entries or it is possible to create a global property or Array List etc
that
would actually reduce the need for copying of the code all over again. I
will show you code in VB.Net as I am not sure which of two (vb.net, c#)
you
use in your code projects, and let me observe that GetFiles really does
outstanding job, and its introduction in Net 2.0 framework was more than
welcome. I would highly appreciate if your suggestions are not mare
general
words but are followed up with general code sample.

Imports Office = Microsoft.Office.Core

Imports System.Xml

Imports System.IO

Imports System.Configuration

Imports System.Collections

Imports System.Net.Mail

Imports System.Web

public class ThisApplication

Private WithEvents _Explorers As Outlook.Explorers

Private WithEvents _Explorer As Outlook.Explorer

Private _contactFolder As Outlook.Explorer

Private _mapiContact As Outlook.MAPIFolder

Private _helpMenuIdex As Object

Private _menuBar As Office.CommandBar

Private WithEvents _Email As Outlook.MailItem

Private Const COMMAND_BAR As String = "EDC"

Private Const BUTTON_NAME As String = "Send_EDC"

Private WithEvents myButton As Office.CommandBarButton

Private Const folder As String = "Z:\GeneratedForms\2006-08"

Private Sub ThisApplication_Startup(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Startup

_menuBar = Me.ActiveExplorer().CommandBars.Add(COMMAND_BAR,
Office.MsoBarPosition.msoBarTop, False, True)

_menuBar.Visible = True

myButton =
CType(_menuBar.Controls.Add(Office.MsoControlType.msoControlButton,
Type.Missing, Type.Missing, Type.Missing, True), Office.CommandBarButton)

myButton.Caption = BUTTON_NAME

myButton.Style = Office.MsoButtonStyle.msoButtonIconAndCaption

myButton.Enabled = True

End Sub



Private Sub myButton_Click(ByVal Ctrl As
Microsoft.Office.Core.CommandBarButton, ByRef CancelDefault As Boolean)
Handles myButton.Click

Try

Call SendEmailtoContacts()

Catch ex As Exception

ex.ToString()

End Try

End Sub

Public Sub SendEmailtoContacts()

Dim objContactItem As Outlook.ContactItem

Dim outlookNameSpace As Outlook.NameSpace = Me.GetNamespace("MAPI")

Dim subjectEmail As String = "EDC"

Dim bodyEmail As String = "text"

'create and instance of the contact folder

Try

_mapiContact =
outlookNameSpace.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderContacts)

For Each objContactItem In _mapiContact.Items.Restrict("[CompanyName] =
Intech")

CreateEmailItem(subjectEmail, objContactItem.Email1Address, bodyEmail,
"EDC_IMD_Forms")

Next

Catch objexc As Exception

objexc.ToString()

Finally

End Try

End Sub





Private Sub CreateEmailItem(ByVal subjectEmail As String, _

ByVal toEmail As String, ByVal bodyEmail As String, _

ByVal AttachmentFiles As String)

Dim eMail As Outlook.MailItem = Me.CreateItem _

(Outlook.OlItemType.olMailItem)

With eMail

.Subject = subjectEmail

.To = toEmail

.Body = bodyEmail

.Importance = Outlook.OlImportance.olImportanceLow

For Each foundFile As String In My.Computer.FileSystem.GetFiles( folder,
FileIO.SearchOption.SearchTopLevelOnly, "Aberdeen*.*")

.Attachments.Add(foundFile)

Next

.Send()

End With

End Sub

 

[Ken Slovak - [MVP - Outlook]]

Why not just create a function or sub that you call with the company name
and that would search the contacts and the Excel files and do that matching.
I see no reason for having tons of copies of almost identical code, other
than the fact that inlining the code instead of calling a procedure would
save you probably about 1 second or less of execution time.

 

[DanHood]

Hi Ken,

I tried to create a general sub with If statement that would compare
eg. company name with excel file and if they are matching, then call sub
procedure to send an email. This did not turn out to be feasible solution as
there are differences in naming convention between company name and excel
file name.

For instance,while the company name is eg Aberdeen there are three excel
files named Aberdeen_IMDP001_31_08_2006, Aberdeen_IMDP006_31_08_2006,
Aberdeen_IMDA001_31_08_2006. As you could see from my code, I was trying to
increase the performance by adding constrains to company search and using
"Restrict" property and the same with GetFiles by using "Aberdeen*.*"

Then again I could not apply an static trim function to the string, which
would make both side of equation identical, as length of funds name(156) is
different and that could cause extra problem.

Based on code that I have provided you with can you come up with an sample
solution that would overcome some limitations that I am currently facing. I
believe there has to be way around it.

Cheers, Dan

 

[Ken Slovak - [MVP - Outlook]]

The optimizations you can make are probably rather minor, but they can help
some.

For really fast performance you'd have to bypass the Outlook object model
and work in Extended MAPI or a wrapper for Extended MAPI, and you'd probably
have to use Win32 API calls to find the files. Not really appropriate for
managed code.

What you can do is not use the dot operators in the loop and also use
SetColumns:

Dim colItems = _mapiContact.Items.Restrict("[CompanyName] = 'Intech'")
colItems.SetColumns "CompanyName, Email1Address"

For Each objContactItem In colItems