CRM Customization - Import Leads from Excel via Email into MS CRM

Introduction

This article shows how to setup a process to import/upload Leads from an Excel file into a Microsoft CRM via email.

See other Siccolo articles about working with Excel and SQL:

• Mass Export All Tables Into Excel Files
• Stored procedure to convert Excel file into a SQL table

and another article showing MS CRM customization:

• Add Custom Report to MS CRM Reports - Change Reports Grid in MS CRM.

1. Transfer Leads from Excel into CRM Leads

Let's say, we have an Excel file, for example, like this:

To access it from SQL:

    ...
    select      first_name
        , last_name
        , company
        , number
        , street
        , city
        , state
        , zip


    from 
        OpenRowSet('MSDASQL'
            ,'Driver={Microsoft Excel Driver (*.xls)};' +
            'DBQ=c:\inetpub\wwwroot\sfa\leads\leads_to_be_imported\leads.xls', 
            'SELECT * FROM [excel_data$]') tmp

    ...

where c:\inetpub\wwwroot\sfa\leads\ is a folder on the CRM server. First, I'll load the Leads records from Excel into a temp table:

    if not exists (select id 
            from [MSCRM].dbo.sysobjects
            where name = 'tmp_leads_to_be_imported' and type ='U')
    begin
        -- create temp table:
        create table [MSCRM].dbo.tmp_leads_to_be_imported
        (
             number     varchar(150)    null    -- phone number
            , first_name    varchar(50)    null    
            , last_name    varchar(50)    null    
            , company    varchar(100)    null
            , street    varchar(50)    null
            , city        varchar(50)    null
            , state        varchar(50)    null
            , zip        varchar(50)    null
        )
    end
    else
    begin
        delete [MSCRM].dbo.tmp_leads_to_be_imported 
    end


    insert into  [MSCRM].dbo.tmp_leads_to_be_imported     
    (
        number         
        , first_name    
        , last_name    
        , company    
        , street    
        , city        
        , state        
        , zip        

    
    )
    select     number
        , first_name
        , last_name
        , company
        , street
        , city
        , state
        , zip


    from 
        OpenRowSet('MSDASQL'
            ,'Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\inetpub\wwwroot\
                     sfa\leads\leads_to_be_imported\temp_leads.xls', 
            'SELECT * FROM [excel_data$]') tmp

    -- to handle possible "oops":
    select @err_code = @@error
    select @records = @@rowcount
    if @err_code<>0
    begin
        set @err = 'Failed to insert into temp table. error=[' + 
                   convert(varchar(10),@err_code) + ']'
        set @message = 'Failed Process Excel Leads Records' + char(13) + @err
        exec master.dbo.xp_sendmail @recipients ='CRM Person in Charge'
            ,@message = @message
            ,@subject = 'Failed Process Excel Leads Records'
        RAISERROR (@err, 16, 1)
        return
    end
    if @records=0
    begin
        set @err =  'Failed to insert into temp table. error=[no records inserted]'
        set @message = 'Failed Process Excel Leads Records' + char(13) + @err
        exec master.dbo.xp_sendmail @recipients ='CRM Person in Charge'
            ,@message = @message
            ,@subject = 'Failed Process Excel Leads Records'
        RAISERROR (@err, 16, 1)
        return
    end

    -- get number of Leads records from the original Excel file
    set @file_count = (select count(*) from  [MSCRM].dbo.tmp_leads_to_be_imported )

where [MSCRM] is the name of the CRM database. Usually, it looks like: [company name]_MSCRM; for example, Siccolo_MSCRM.

After that, we can transfer records from the temp table into the Lead view. As you may know, CRM has an extra layer between the underlying data and the CRM user interfaces - views. For example, the underlying table for storing the leads information is LeadBase; but CRM interfaces work with the Lead view object.

    -- in order insert new records into Leads
    -- we need  SecurityDescriptor and ModifiedBy values:
    declare @security_descriptor     varchar(555)
    declare @modified_by         varchar(255)     
    
    select @security_descriptor = SecurityDescriptor
        , @modified_by = ModifiedBy
    from [MSCRM].dbo.SystemUser
    -- find CRM user by using originator email address
    where InternalEmailAddress = @user_email or ( isnull(PersonalEmailAddress,'')!='' 
                                 and PersonalEmailAddress = @user_email)


    -- now, insert into Leads view:
    insert into [MSCRM].dbo.Lead
    ( 
        Subject
        , CompanyName
        , FirstName
        , LastName

        , Telephone1

        , Address1_Line1
        , Address1_City
        , Address1_StateOrProvince
        , Address1_PostalCode

        , Description

        , OwningUser
        , LeadId
        , StateCode
        , DeletionStateCode
        , SecurityDescriptor
        , ModifiedBy
        , CreatedOn
        , ModifiedOn
    )

    select     company as Subject
        , company    
        , first_name    
        , last_name    

        , number         

        , street    
        , city        
        , state        
        , zip        
        

        , 'This Lead was created automatically from Excel file' as Description
    
        , @modified_by        as OwningUser
        , NEWID()        as LeadID
        , 0            as StateCode        -- open lead
        , 0            as DeletionStateCode
        , @security_descriptor    as SecurityDescriptor    
        , @modified_by        as ModifiedBy
        , getdate()        as CreatedOn
        , getdate()        as CreatedOn


    from [MSCRM].dbo.tmp_leads_to_be_imported 
    
    select @record_count = @@rowcount

    select @file_count        as 'original_file_count' 
        , @record_count     as 'processed_record_count'

where [MSCRM] is the name of the CRM database. Usually, it looks like [company name]_MSCRM; for example, Siccolo_MSCRM.

And now, to make a stored procedure out of this:

    CREATE procedure <code>p_Lead_Process_Excel_Leads_File
    (
        @user_email            varchar(50)
        ,@leads_excel_file_name     varchar(255)

        ,@file_count            int         =null output
        ,@record_count             int        =null output
    )
    as
    set nocount on
        ...
        ...
        -- insert into temp table from Excel
        ...
        ...
        -- insert into Lead view from temp table
        ...
        ...

    set nocount off

Side note:

MS CRM, to handle INSERT/UPDATE/DELETE, has a set of triggers on the Lead view:

Unfortunately, the CRM team at Microsoft did not plan for someone inserting more than one Lead record at a time, so I needed to make some changes to the t_create_lead trigger, and create a new function f_GetFullName.

Changes to the t_create_lead trigger:

        ...
        declare @organizationid uniqueidentifier
        select @organizationid = BusinessUnitBase.OrganizationId
        from BusinessUnitBase
        where BusinessUnitBase.BusinessUnitId = @owningbusinessunit

        
        -- -----------------------------------------------------------
        -- Before changes:
        /*
            declare @firstname nvarchar(50)
            declare @lastname nvarchar(50)
            declare @middlename nvarchar(50)
            declare @fullname nvarchar(160)

            select @firstname = FirstName, @middlename = MiddleName, 
                                @lastname = LastName, @fullname = FullName
            from inserted

            if @fullname is null 
               exec p_GetFullName @organizationid, @firstname, 
                                  @lastname, @middlename, @fullname output
        */

        ...
        ...

        insert LeadBase(
                ...
                ...
                )
        select 
                LeadId, 
                ...
                FirstName, 
                MiddleName, 
                LastName, 
                ...
                EMailAddress3, 
                -- -----------------------------------------------------------------
                -- full name!:
                dbo.f_GetFullName(@organizationid, FirstName, LastName, MiddleName)
                -- ------------------------------------------------------------------
                ...
        from inserted

        ...
        ...

Side note:

• @@ROWCOUNT - Returns the number of rows affected by the last statement.
• @@ERROR - Returns the error number for the last Transact-SQL statement executed.

2. Process Email and Import Leads

So, at this point, I have a procedure to import/upload Leads records from an Excel file into the Lead view. Next step - create a procedure to process incoming emails and route them to p_Lead_Process_Excel_Leads_File. To process emails in/with SQL Server, we can use the xp_readmail, xp_findnextmsg, xp_sendmail, and xp_deletemail SQL mail extended stored procedures. Something like this:

    CREATE procedure p_email_Process_Incoming_Emails
    as

        set nocount on
            ...
            ...
        /* get first message id */
        declare @status int
        declare @msg_id varchar(94)
        declare @mapifailure int
    
        exec @status     = master.dbo.<code>xp_findnextmsg
            @msg_id    = @msg_id output,
            @unread_only='true'

        if @status <> 0
        begin
            set @mapifailure=1
            select 'failed to execute xp..findnextmessage'
        end
        
        while (@mapifailure=0)
        begin
            if @msg_id is null break
            if @msg_id = '' break
    
            -- peek at incoming email message
            -- and see if we need to process it:

            declare @originator varchar(255)
                ,@originator_address varchar(255)

            declare @cc_list varchar(255)
            declare @msg_subject varchar(255)
            declare @msg_message varchar(8000)

            exec @status = master.dbo.xp_readmail
                @msg_id        = @msg_id,
                @originator    = @originator output,
                @cc_list    = @cc_list output,
                @subject    = @msg_subject output,
                @message    = @msg_message output,
                @peek        = 'true',
                @suppress_attach='true',
                @originator_address = @originator_address output

            if @status <> 0
            begin
                select @mapifailure=1
                break
            end

            /* get new message id before processing & deleting current */
            set @current_msg=@msg_id
            exec @status     = master.dbo.xp_findnextmsg
                @msg_id    = @msg_id output,
                @unread_only='true'

            if @status <> 0
            begin
                select @mapifailure=1
            end
            print 'checking email [' + @msg_subject + ']'


            declare @attachments varchar(255)
                
            -- if message subject contains "magic" words:
            if (lower(@msg_subject) = 'iMport Excel CRM Leads')
            begin
                print 'import excel CRM leads'
                set @bad_input = 0
            
                exec @status         = master.dbo.xp_readmail
                    @msg_id        = @current_msg,
                    @originator    = @originator output,
                    @cc_list    = @cc_list output,
                    @subject    = @msg_subject output,
                    @message    = @msg_message output,
                    @peek        = 'false',    -- this time no peeking, get email message
                    @suppress_attach='false',
                    @attachments     = @attachments output, -- and get attachted Excel file!
                    @originator_address = @originator_address output
                    -- make sure user has rights to import CRM Leads!!!
                /* 
                    check user credentials bases on user email address
                */
                -- also, check if user email is in CRM SystemUser:
                if not exists ( select SystemUserId from [MSCRM].dbo.SystemUser
                        where InternalEmailAddress = @originator or 
                                ( isnull(PersonalEmailAddress,'')!='' 
                                  and PersonalEmailAddress = @originator)
                        ) 
                begin
                    set @msg_message = 'User [' + @originator + 
                                       '] does not have access to CRM!'
                    set @msg_subject = @msg_message
                    exec @status= master.dbo.xp_sendmail @recipients = @originator
                                ,@message     = @msg_message
                                ,@subject     = @msg_subject
                                ,@attachments     = @attachments
                        set @bad_input = 1
                end
                    
                if @bad_input = 0     
                begin
                exec [MSCRM].dbo.p_Lead_Process_Excel_Leads_File
                                    @originator
                                    , @attachments
                                    , @file_count            output
                                    , @record_count         output


                    -- if number of Leads records in Excel file is different
                    -- then number of records inserted into CRM Leads,
                    -- let user know:
                    if (@file_count != @record_count)
                    begin
                      set @msg_subject = 'Processed '  + 
                          @msg_subject + ' - !Totals Do Not Match!'
                      set @msg_message = 'Processed ' + @msg_subject + char(13)+ char(13)+
                          'File Record Count: ' + char(9)+ 
                          '   convert(varchar,isnull(@file_count,0)) + char(13)+ char(13)+
                          'Processed Count: ' + char(9)+
                          '   convert(varchar,isnull(@record_count,0)) + char(13)+ char(13)+
                          char(9)+ '!File Total Does Not Match Processed Total!'
                    end
                    else
                    begin
                        -- number of records in Excel is the same
                        -- as number of records inserted into CRM Leads:
                        -- simply let user know that Leads Excel file is processed
                        set @msg_subject = 'Processed '  + @msg_subject
                        set @msg_message = 'Processed '  + @msg_subject + char(13)+ char(13)+
                                'Record Count: ' + char(9)+
                                '  convert(varchar,@file_count) + char(13)+ char(13)+
                                'Processed Count: ' + char(9)+ convert(varchar,@record_count)
                    end
                
                    -- and send email response back to user:
                    exec @status= master.dbo.xp_sendmail @recipients = @originator
                                ,@message     = @msg_message
                                ,@subject     = @msg_subject
                                ,@attachments     = @attachments
                end -- end of if @bad_input = 0 

                print 'deleting  - import excel CRM leads' + convert(varchar, @current_msg)
                exec master.dbo.xp_deletemail @current_msg

            end    -- end of if (lower(@msg_subject) = 'import excel CRM leads')

        end -- end of while (@mapifailure=0)

    -- done with messages in Inbox
    if @mapifailure=1
          begin
        raiserror(15079,-1,-1,@messages)
        return(1)
    end
      else
    begin
        return(0)
    end

    set nocount off

So, in order to have SQL Server process Leads from an Excel file into the Lead view, simply send email message to SQL Server with the Excel file attached, and put import excel CRM leads as the email subject.

History

No improvements so far. Nearly perfect.

Points of Interest

If you would like to read more on this story - please take a look at Siccolo - Free Mobile Management Tool For SQL Server, and more articles at Siccolo.