Thursday, May 28, 2015

MS CRM 2011 - Send Email with attachment ( C# )

      private static void SendEmail(string fromUserEmailId, List<string> toUsersEmails, string attachmentfileLocation, IOrganizationService orgService)
      {       
          EntityReference fromUser=GetUserbyEmailId(fromUserEmailId,orgService);
           List<Entity>  emailFromList =new List<Entity>();
          if(fromUser!=null)
          {
                Entity emailFrom=new Entity("activityparty");
                emailFrom["partyid"] = fromUser;
                emailFromList.Add(emailFrom) ;
          }
         List<Entity> emailToList=new List<Entity>();        
          foreach (string toUser in toUsersEmails)
          {
            
              EntityReference toUserID = GetUserbyEmailId(toUser, orgService);
              if (toUserID != null)
              {
                  Entity emailTo = new Entity("activityparty");
                  emailTo["partyid"] = toUserID;
                  emailToList.Add (emailTo);
              }
          }
            Entity newEmail = new Entity("email");
            newEmail["from"] = emailFromList.ToArray<Entity>();
            newEmail["to"] = emailToList.ToArray<Entity>();
            newEmail["subject"] = "test subject";
            newEmail["description"] = " test description";
            newEmail["directioncode"] = true;
            Guid newEmailId= orgService.Create(newEmail);
            AddAttachmentToEmail(new EntityReference(newEmail.LogicalName, newEmailId), attachmentfileLocation, orgService);
       
              // Use the SendEmail message to send an e-mail message.
              SendEmailRequest sendEmailreq = new SendEmailRequest
              {
               EmailId = newEmailId,
               TrackingToken = "",
               IssueSend = true
              };
              SendEmailResponse sendEmailresp = (SendEmailResponse)orgService.Execute(sendEmailreq);
      }
    
    
      private static void AddAttachmentToEmail(EntityReference email, string fileLocation, IOrganizationService service)
      {
          // Open a file and read the contents into a byte array
          FileStream stream = System.IO.File.OpenRead(fileLocation);
          byte[] byteData = new byte[stream.Length];
          stream.Read(byteData, 0, byteData.Length);
          stream.Close();
        
          // Encode the data using base64.
          string encodedData = System.Convert.ToBase64String(byteData);
          // Add attachment
          Entity newAttachment = new Entity("activitymimeattachment");
          newAttachment["body"] = encodedData;
          newAttachment["filename"] = Path.GetFileName(fileLocation);
          newAttachment["objectid"] = email;
          newAttachment["objecttypecode"] = "email";
          // Create the attachment
          service.Create(newAttachment);
      }
      private static EntityReference GetUserbyEmailId(string emailID, IOrganizationService orgService)
      {
          string fetchQuerytoGetSystemUser = @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
                                              <entity name='systemuser'>
                                                <attribute name='fullname' />
                                                <attribute name='businessunitid' />
                                                <attribute name='title' />
                                                <attribute name='address1_telephone1' />
                                                <attribute name='systemuserid' />
                                                <order attribute='fullname' descending='false' />
                                                <filter type='and'>
                                                  <condition attribute='internalemailaddress' operator='eq' value='" + emailID + "' />"
                                                + @"</filter>
                                              </entity>
                                            </fetch>";
          EntityCollection usersList = orgService.RetrieveMultiple(new FetchExpression(fetchQuerytoGetSystemUser));
          if (usersList != null && usersList.Entities != null && usersList.Entities.Count > 0)
          {
              return new EntityReference(usersList[0].LogicalName, usersList[0].Id);
          }
          return null;
      }
 

Sunday, March 1, 2015

MS Excel - Using Index Match instead of Vlookup



=INDEX ( Column I want a return value from , MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0” ))

 Example,

=IFERROR(INDEX(Sheet5!I:I,MATCH('duplicated clean'!A3,Sheet5!A:A,0)),"")

Reference:

http://www.randomwok.com/excel/how-to-use-index-match/

http://fiveminutelessons.com/learn-microsoft-excel/how-use-index-match-instead-vlookup

Wednesday, January 28, 2015

SSRS - Show blank / Empty rows in Table

1.Insert a new rows in the table

2.Set the visibility using the following expression

=IIF(CountRows() < 1 = 0, true, false)

Note:
Similarly we can show multiple empty rows.
in my case ,i added 10 empty rows & set the row visibility using below expression

=IIF(CountRows() < 1 = 0, true, false) - first row
=IIF(CountRows() < 2 = 0, true, false) - 2nd row, etc..

SSRS - Forcing subreport Contains Empty data / no rows to Show



1.Add a dataset ("dataset2)" in sub report with following query

SELECT ' ' AS DUMMY
 
 
2.Add a empty textbox with following expression
=First(Fields!DUMMY.Value, "DataSet2")
 

Reference:

https://coderwall.com/p/meqrng/forcing-a-ssrs-subreport-to-show