private static void ToggleWorkOffline(OutlookApplication outlook, ComObjectManager com)
{
var explorer = com.Get(outlook.ActiveExplorer);
var commandBars = com.Get(() => explorer.CommandBars);
commandBars.ExecuteMso(@"ToggleOnline");
}
private static void BuildWorksheet(ComObjectManager com, NominationList nominationList, Worksheet worksheet)
{
var cells = com.Get(() => worksheet.Cells);
cells.NumberFormat = "@"; // Format all cells as text.
SetCellValue(cells, 1, 1, @"Quarter");
SetCellValue(cells, 1, 2, @"Name");
SetCellValue(cells, 1, 3, @"Office");
SetCellValue(cells, 1, 4, @"Values");
SetCellValue(cells, 1, 5, @"WriteUps");
var rowNumber = 2;
foreach (var awardWinner in nominationList.StarValuesAwardWinners)
{
var companyValues = string.Join("; ",
nominationList.GetCompanyValuesForAwardWinner(awardWinner).Select(cv => cv.ToString()));
var writeUps = CompileWriteUps(nominationList.GetNominationWriteUpsForAwardWinner(awardWinner));
SetCellValue(cells, rowNumber, 1, nominationList.AwardsPeriod.Quarter.Abbreviation);
SetCellValue(cells, rowNumber, 2, awardWinner.Name.FullName);
SetCellValue(cells, rowNumber, 3, awardWinner.OfficeLocation.Name);
SetCellValue(cells, rowNumber, 4, companyValues);
SetCellValue(cells, rowNumber, 5, writeUps);
++rowNumber;
}
}
private static void BuildWorksheet(ComObjectManager com, IEnumerable <Nomination> nominations,
Worksheet worksheet)
{
var cells = com.Get(() => worksheet.Cells);
cells.NumberFormat = "@"; // Format all cells as text.
SetCellValue(cells, 1, 1, @"Record_");
SetCellValue(cells, 1, 2, @"Nomination_IDs");
SetCellValue(cells, 1, 3, @"Continuously_Improving");
SetCellValue(cells, 1, 4, @"Driving_Innovation");
SetCellValue(cells, 1, 5, @"Delighting_Customers");
SetCellValue(cells, 1, 6, @"Behaving_with_Integrity");
SetCellValue(cells, 1, 7, @"Delivering_Meaningful_Outcomes");
SetCellValue(cells, 1, 8, @"Streaming_Good");
SetCellValue(cells, 1, 9, @"WRITEUP");
var rowNumber = 2;
foreach (var nomination in nominations)
{
SetCellValue(cells, rowNumber, 1, rowNumber - 1);
SetCellValue(cells, rowNumber, 2, nomination.VotingIdentifier.ToString());
SetCellValue(cells, rowNumber, 3, GetCompanyValue(nomination, CompanyValue.ContinuouslyImproving));
SetCellValue(cells, rowNumber, 4, GetCompanyValue(nomination, CompanyValue.DrivingInnovation));
SetCellValue(cells, rowNumber, 5, GetCompanyValue(nomination, CompanyValue.DelightingCustomers));
SetCellValue(cells, rowNumber, 6, GetCompanyValue(nomination, CompanyValue.BehavingWithIntegrity));
SetCellValue(cells, rowNumber, 7, GetCompanyValue(nomination, CompanyValue.DeliveringMeaningfulOutcomes));
SetCellValue(cells, rowNumber, 8, GetCompanyValue(nomination, CompanyValue.StreamingGood));
SetCellValue(cells, rowNumber, 9, nomination.WriteUp.ToString());
++rowNumber;
}
}
private static void BuildWorksheet(ComObjectManager com, NominationList nominationList, Worksheet workSheet)
{
var cells = com.Get(() => workSheet.Cells);
var attendees = nominationList
.AwardsLuncheonInvitees
.OrderBy(i => i.OfficeLocation.ToString())
.ThenBy(i => i.Name.FullName)
.ToList();
cells.NumberFormat = "@"; // Format all cells as text.
SetCellValue(cells, 1, 1, @"Nominee Name");
SetCellValue(cells, 1, 2, @"Nominee Office");
SetCellValue(cells, 1, 3, @"Nominee Email Address");
for (var i = 0; i < attendees.Count; ++i)
{
var rowNumber = i + 2;
var attendee = attendees[i];
SetCellValue(cells, rowNumber, 1, attendee.Name.FullName);
SetCellValue(cells, rowNumber, 2, attendee.OfficeLocation.ToString());
SetCellValue(cells, rowNumber, 3, attendee.EmailAddress.ToString());
}
}
private static void AddVotingGuideAttachment(ComObjectManager com, MailItem mailItem,
IMailMergeFactory mailMergeFactory,
NominationList nominationList, AwardType awardType)
{
var attachments = com.Get(() => mailItem.Attachments);
var fileName = awardType.GetVotingGuideFileName(nominationList.AwardsPeriod);
var filePath = FilePath.Create(Path.Combine(Path.GetTempPath(), fileName), false);
if (File.Exists(filePath.Value))
{
File.Delete(filePath.Value);
}
var mailMerge = mailMergeFactory.GetVotingGuideMailMerge(awardType, nominationList);
mailMerge.Execute(filePath);
com.Get(() => attachments.Add(filePath.Value));
}
private static void AddVotingKeyAttachment(ComObjectManager com, MailItem mailItem,
IExcelFileFactory excelFileFactory,
NominationList nominationList, AwardType awardType)
{
var attachments = com.Get(() => mailItem.Attachments);
var fileName = awardType.GetVotingKeyFileName(nominationList.AwardsPeriod);
var filePath = FilePath.Create(Path.Combine(Path.GetTempPath(), fileName), false);
if (File.Exists(filePath.Value))
{
File.Delete(filePath.Value);
}
using (var excelFile = excelFileFactory.GetVotingKeyExcelFile(awardType, nominationList))
{
excelFile.Save(filePath);
}
com.Get(() => attachments.Add(filePath.Value));
}
private static void AddAwardLuncheonInviteeListAttachment(ComObjectManager com, MailItem mailItem,
IExcelFileFactory excelFileFactory,
NominationList nominationList)
{
var attachments = com.Get(() => mailItem.Attachments);
var fileName =
$@"{nominationList.AwardsPeriod.FileNamePrefix}_StarAwards_LuncheonInvitees.xlsx";
var filePath = FilePath.Create(Path.Combine(Path.GetTempPath(), fileName), false);
if (File.Exists(filePath.Value))
{
File.Delete(filePath.Value);
}
using (var excelFile = excelFileFactory.GetAwardsLuncheonInviteeListExcelFile(nominationList))
{
excelFile.Save(filePath);
}
com.Get(() => attachments.Add(filePath.Value));
}
private static void AddVotingGuideAttachmentAndInstructions(ComObjectManager com, MailItem mailItem,
HtmlNode content, IMailMergeFactory mailMergeFactory, NominationList nominationList, AwardType awardType,
string instructions)
{
var attachments = com.Get(() => mailItem.Attachments);
var fileName = awardType.GetVotingGuideFileName(nominationList.AwardsPeriod);
var filePath = FilePath.Create(Path.Combine(Path.GetTempPath(), fileName), false);
if (File.Exists(filePath.Value))
{
File.Delete(filePath.Value);
}
var mailMerge = mailMergeFactory.GetVotingGuideMailMerge(awardType, nominationList);
mailMerge.Execute(filePath);
com.Get(() => attachments.Add(filePath.Value));
AppendSection(content, $@"<b><i>{awardType.PrettyName} Nominees:</i></b> {fileName} -- {instructions}");
}
private void Execute(ComObjectManager com, FilePath outputFilePath)
{
object no = false;
object missing = Missing.Value;
var dataSourcePath = GetDataSourcePath();
object templatePath = ExtractMailMergeTemplate(_mailMergeTemplateResourceName);
var word = com.Get(() => new Application {
Visible = false
});
var documents = com.Get(() => word.Documents);
var mergeTemplateDocument =
com.Get(() => documents.Add(ref templatePath, ref missing, ref missing, ref no));
try
{
Execute(com, word, dataSourcePath, mergeTemplateDocument, outputFilePath);
}
finally
{
Cleanup(word, mergeTemplateDocument, dataSourcePath.Value, templatePath.ToString());
}
}
private void Initialize()
{
if (_isInitialized)
{
return;
}
InitializationStarted?.Invoke(this, EventArgs.Empty);
using (var com = new ComObjectManager())
{
var outlook = com.Get(() => new OutlookApplication());
var session = com.Get(() => outlook.Session);
var globalAddressList = com.Get(() => session.GetGlobalAddressList());
var addressEntries = com.Get(() => globalAddressList.AddressEntries);
var people = new List <Person>(3000);
people.AddRange(addressEntries
.Cast <AddressEntry>()
.Select(ae => com.Get(() => ae))
.Select(ae => GetPerson(com, ae))
.Where(p => p != null));
foreach (var group in people.GroupBy(p => p.LastName))
{
PeopleByLastName.Add(group.Key, group.ToList());
}
foreach (var group in people.GroupBy(p => p.EmailAddress))
{
PeopleByEmailAddress.Add(group.Key, group.ToList());
}
}
InitializationCompleted?.Invoke(this, EventArgs.Empty);
_isInitialized = true;
}
private static void SaveMailMergeOutputDocument(ComObjectManager com, Application word, FilePath outputFilePath,
object no)
{
var activeDocument = com.Get(() => word.ActiveDocument);
object filePath = outputFilePath.Value;
var extension = Path.GetExtension(outputFilePath.Value);
var saveFormat = string.Equals(@".pdf", extension, StringComparison.InvariantCultureIgnoreCase)
? WdSaveFormat.wdFormatPDF
: WdSaveFormat.wdFormatDocumentDefault;
activeDocument.SaveAs(ref filePath, saveFormat);
activeDocument.Close(ref no);
}
private void Execute(ComObjectManager com, Application word, FilePath dataSourcePath,
Document mergeTemplateDocument, FilePath outputFilePath)
{
object yes = true;
object no = false;
object missing = Missing.Value;
object format = WdOpenFormat.wdOpenFormatAuto;
object subtype = WdMergeSubType.wdMergeSubTypeAccess;
object connection =
$"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source={dataSourcePath.Value};Mode=Read;Extended Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:Engine Type=37;Jet OLEDB:Database ";
object sqlStatement = @"SELECT * FROM `Sheet1$`";
var mailMerge = com.Get(() => mergeTemplateDocument.MailMerge);
mailMerge.MainDocumentType = _mailMergeDocType;
mailMerge.OpenDataSource(dataSourcePath.Value, ref format, ref no, ref no, ref yes,
ref no,
ref missing, ref missing, ref no, ref missing, ref missing, ref connection, ref sqlStatement,
ref missing, ref no, ref subtype);
DoMergeTypeSetup(com, mailMerge);
mailMerge.SuppressBlankLines = true;
var dataSource = com.Get(() => mailMerge.DataSource);
dataSource.FirstRecord = (int)WdMailMergeDefaultRecord.wdDefaultFirstRecord;
dataSource.LastRecord = (int)WdMailMergeDefaultRecord.wdDefaultLastRecord;
mailMerge.Execute(ref no);
if (outputFilePath != null)
{
SaveMailMergeOutputDocument(com, word, outputFilePath, no);
}
}
private static Person GetPerson(ComObjectManager com, AddressEntry addressEntry)
{
if (addressEntry.AddressEntryUserType != OlAddressEntryUserType.olExchangeUserAddressEntry &&
addressEntry.AddressEntryUserType != OlAddressEntryUserType.olExchangeRemoteUserAddressEntry)
{
return(null);
}
var exchangeUser = com.Get(addressEntry.GetExchangeUser);
if (exchangeUser == null)
{
return(null);
}
var person = new Person(exchangeUser);
return(person);
}
private static void BuildWorksheet(ComObjectManager com, AwardsPeriod awardsPeriod,
IEnumerable <Nomination> nominations, Worksheet worksheet)
{
var cells = com.Get(() => worksheet.Cells);
cells.NumberFormat = "@"; // Format all cells as text.
SetCellValue(cells, 1, 1, @"Year");
SetCellValue(cells, 1, 2, @"Quarter");
SetCellValue(cells, 1, 3, @"Submitted by");
SetCellValue(cells, 1, 4, @"NOMINEE'S NAME");
SetCellValue(cells, 1, 5, @"NOMINEE'S OFFICE");
SetCellValue(cells, 1, 6, @"Continuously_Improving");
SetCellValue(cells, 1, 7, @"Driving_Innovation");
SetCellValue(cells, 1, 8, @"Delighting_Customers");
SetCellValue(cells, 1, 9, @"Behaving_with_Integrity");
SetCellValue(cells, 1, 10, @"Delivering_Meaningful_Outcomes");
SetCellValue(cells, 1, 11, @"Streaming_Good");
SetCellValue(cells, 1, 12, @"WRITE-UP");
SetCellValue(cells, 1, 13, @"Email");
var rowNumber = 2;
foreach (var nomination in nominations)
{
SetCellValue(cells, rowNumber, 1, awardsPeriod.Year.ToString());
SetCellValue(cells, rowNumber, 2, awardsPeriod.Quarter.Abbreviation);
SetCellValue(cells, rowNumber, 3, nomination.NominatorName.FullName);
SetCellValue(cells, rowNumber, 4, nomination.NomineeName.FullName);
SetCellValue(cells, rowNumber, 5, nomination.NomineeOfficeLocation.ToString());
SetCellValue(cells, rowNumber, 6, GetCompanyValue(nomination, CompanyValue.ContinuouslyImproving));
SetCellValue(cells, rowNumber, 7, GetCompanyValue(nomination, CompanyValue.DrivingInnovation));
SetCellValue(cells, rowNumber, 8, GetCompanyValue(nomination, CompanyValue.DelightingCustomers));
SetCellValue(cells, rowNumber, 9, GetCompanyValue(nomination, CompanyValue.BehavingWithIntegrity));
SetCellValue(cells, rowNumber, 10, GetCompanyValue(nomination, CompanyValue.DeliveringMeaningfulOutcomes));
SetCellValue(cells, rowNumber, 11, GetCompanyValue(nomination, CompanyValue.StreamingGood));
SetCellValue(cells, rowNumber, 12, nomination.WriteUp.ToString());
SetCellValue(cells, rowNumber, 13, nomination.NomineeEmailAddress.ToString());
++rowNumber;
}
}
private static void BuildWorksheet(ComObjectManager com, NominationList nominationList, Worksheet worksheet)
{
var cells = com.Get(() => worksheet.Cells);
var awardWinners = nominationList.RisingStarAwardWinners;
cells.NumberFormat = "@"; // Format all cells as text.
SetCellValue(cells, 1, 1, @"Nominee Name");
SetCellValue(cells, 1, 2, @"Nominee Office");
for (var i = 0; i < awardWinners.Count; ++i)
{
var rowNumber = i + 2;
var awardWinner = awardWinners[i];
SetCellValue(cells, rowNumber, 1, awardWinner.Name.FullName);
SetCellValue(cells, rowNumber, 2, awardWinner.OfficeLocation.Name);
}
}
public static void ActivateWorkOffline(this OutlookApplication outlook, ComObjectManager com)
{
if (outlook == null)
{
throw new ArgumentNullException(nameof(outlook));
}
if (com == null)
{
throw new ArgumentNullException(nameof(com));
}
var session = com.Get(() => outlook.Session);
if (session.Offline)
{
return;
}
ToggleWorkOffline(outlook, com);
}
private static void BuildWorksheet(ComObjectManager com, AwardsPeriod awardsPeriod,
IEnumerable <AwardWinner> certificateRecipients, Worksheet worksheet)
{
var cells = com.Get(() => worksheet.Cells);
cells.NumberFormat = "@"; // Format all cells as text.
SetCellValue(cells, 1, 1, @"Year");
SetCellValue(cells, 1, 2, @"Quarter");
SetCellValue(cells, 1, 3, @"Name");
var rowNumber = 2;
foreach (var certificateRecipient in certificateRecipients)
{
SetCellValue(cells, rowNumber, 1, awardsPeriod.Year.ToString());
SetCellValue(cells, rowNumber, 2, awardsPeriod.Quarter.FullName);
SetCellValue(cells, rowNumber, 3, certificateRecipient.Name.FullName);
++rowNumber;
}
}
private static void BuildWorksheet(ComObjectManager com, IEnumerable <Nomination> nominations,
Worksheet workSheet)
{
var cells = com.Get(() => workSheet.Cells);
cells.NumberFormat = "@"; // Format all cells as text.
SetCellValue(cells, 1, 1, @"Nominiation ID(s)");
SetCellValue(cells, 1, 2, @"Nominee Name");
SetCellValue(cells, 1, 3, @"Nominee Office");
var rowNumber = 2;
foreach (var nomination in nominations)
{
SetCellValue(cells, rowNumber, 1, nomination.VotingIdentifier.ToString());
SetCellValue(cells, rowNumber, 2, nomination.NomineeName.FullName);
SetCellValue(cells, rowNumber, 3, nomination.NomineeOfficeLocation.ToString());
++rowNumber;
}
}
private static void BuildWorksheet(ComObjectManager com, IEnumerable <Person> nomineesx, Worksheet workSheet)
{
var cells = com.Get(() => workSheet.Cells);
var orderedNominees = nomineesx
.OrderBy(i => i.OfficeLocation.ToString())
.ThenBy(i => i.Name.FullName);
cells.NumberFormat = "@"; // Format all cells as text.
SetCellValue(cells, 1, 1, @"Nominee Name");
SetCellValue(cells, 1, 2, @"Nominee Office");
var rowNumber = 1;
foreach (var nominee in orderedNominees)
{
++rowNumber;
SetCellValue(cells, rowNumber, 1, nominee.Name.FullName);
SetCellValue(cells, rowNumber, 2, nominee.OfficeLocation.ToString());
}
}
private static void ActivateOutlookWorkOffline(ComObjectManager com)
{
var outlook = com.Get(() => new OutlookApplication());
outlook.ActivateWorkOffline(com);
}