CRM 定制 - 通过电子邮件从 Excel 导入潜在客户到 MS CRM
构建一个流程，通过电子邮件将 Excel 中的潜在客户记录导入到 MS CRM 中。
引言
本文介绍如何设置一个流程，通过电子邮件将 Excel 文件中的潜在客户导入/上传到 Microsoft CRM 中。
查看其他关于使用 Excel 和 SQL 的Siccolo 文章
以及另一篇展示 MS CRM 定制的文章
1. 将 Excel 中的潜在客户转移到 CRM 潜在客户
假设我们有一个 Excel 文件，例如这样
从 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
...
其中c:\inetpub\wwwroot\sfa\leads\ 是 CRM 服务器上的一个文件夹。首先，我将从 Excel 将 Leads 记录加载到一个临时表中
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 )
其中 [MSCRM] 是 CRM 数据库的名称。通常，它看起来像：[公司名称]_MSCRM；例如，Siccolo_MSCRM。
之后，我们可以将记录从临时表转移到 Lead 视图。您可能知道，CRM 在底层数据和 CRM 用户界面之间有一个额外的层 - 视图。例如，用于存储潜在客户信息的底层表是 *LeadBase*；但 CRM 接口使用 Lead 视图对象。
-- 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'
其中 [MSCRM] 是 CRM 数据库的名称。通常，它看起来像 [公司名称]_MSCRM；例如，Siccolo_MSCRM。
现在，把它变成一个存储过程
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
旁注
MS CRM 为了处理 INSERT/UPDATE/DELETE，在 Lead 视图上有一组**触发器**
不幸的是，Microsoft 的 CRM 团队没有计划一次插入多个 Lead 记录的情况，因此我需要对 t_create_lead 触发器进行一些更改，并创建一个新的函数 f_GetFullName。
对 t_create_lead 触发器的更改
...
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
...
...
旁注
@@ROWCOUNT- 返回受上一个语句影响的行数。@@ERROR- 返回上一个 Transact-SQL 语句执行的错误号。
2. 处理电子邮件并导入潜在客户
因此，此时，我有一个程序可以从 Excel 文件中导入/上传潜在客户记录到 Lead 视图中。下一步 - 创建一个程序来处理收到的电子邮件并将它们路由到 p_Lead_Process_Excel_Leads_File。 为了在 SQL Server 中/使用 SQL Server 处理电子邮件，我们可以使用 xp_readmail、xp_findnextmsg、xp_sendmail 和 xp_deletemail SQL 邮件扩展存储过程。 像这样
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
因此，为了让 SQL Server 将 Excel 文件中的潜在客户处理到 Lead 视图中，只需将带有 Excel 文件附件的电子邮件发送到 SQL Server，并将 *import excel CRM leads* 作为电子邮件主题。
历史
到目前为止没有改进。 几乎完美。
关注点
如果您想阅读更多关于这个故事的信息 - 请查看 Siccolo - 用于 SQL Server 的免费移动管理工具，以及 Siccolo 上的更多文章。