e-mail with Databasemail pictures and Mao

I do not know if your boss has such a request, some of the system's data need to regularly send email reminders, such as the material will expire or some to-do reminders. Of course, these can be achieved with SSRS report subscriptions, but some companies do not set up the corresponding Reporting Services, or just some simple data, do not want to do some redundant reports. This time directly with Dbmail to send some data is the most convenient direct. At first I also tried to send text messages directly to the corresponding people. But text text user experience is not very good after all. Therefore, an HTML template is constructed so that some data can be emitted directly from subsequent simple modifications. The following is the corresponding code, please shoot bricks.

--notify related people by emailSelectId=Identity(int,1,1), Followuserid, Jobcontent,enddate, Mailid into#t fromHrmonthjobdtlwhere  ISNULL(Finished,0)=0  and getdate()betweenStartDate and DateAdd( Day,1, EndDate)--SELECT * from HrmonthjobdtlDeclare @successto  varchar( -),@successtocc varchar( -),@sub varchar( -) Declare @id int,@mailid varchar( the),@user varchar(Ten)Declare @msg varchar(Max),@captionstyle varchar( +) Set @captionstyle ='' Set @captionstyle = @captionstyle +'<style>' Set @captionstyle = @captionstyle +'BODY {border-right:0px; border-top:0px; margin:0px; border-left:0px;  border-bottom:0px;  Scrollbar-face-color: #f4f2f3;    Scrollbar-highlight-color: #FFFFFF;    Scrollbar-shadow-color: #DEE3E7;    Scrollbar-3dlight-color: #D1D7DC;    Scrollbar-arrow-color: #006699;    Scrollbar-track-color: #EFEFEF; Scrollbar-darkshadow-color: #98AAB1}' Set @captionstyle = @captionstyle +'. Tableheader{background-color: #cccccc; COLOR: #000000}' Set @captionstyle = @captionstyle +'. Tableheaderfont{color: #003399; Font-family:"'Song Body"',"'Verdana"', "'Arial"', "'Helvetica"', "'Sans-serif"'; FONT-SIZE:14PX}' Set @captionstyle = @captionstyle +'. Tableitem{background-color: #f4f2f3; COLOR: #000000}' Set @captionstyle = @captionstyle +'. tableitemfont{COLOR: #003399; Font-family:"'Song Body"',"'Verdana"', "'Arial"', "'Helvetica"', "'Sans-serif"'; FONT-SIZE:13PX}' Set @captionstyle = @captionstyle +'</style>'   while((Select Count(*) from#t)>0) begin    Select @id=Id@mailid=Mailid,@user=Followuserid from#tSet @msg =@captionstyle+''+ @user +', here are the reminders </font>'; Set @msg = @msg + '<table width=100%><tr><td align=left><table bordercolor= #ffffff cellspacing=0.5px bordercolordark= #ffffff cellpadding=1 width=100% align=left bordercolorlight= #ffffff border=0>'    Set @msg = @msg + '<tr class=tableitem>'    Set @msg = @msg + '<td width=80%><font class=tableheaderfont><b> work content </b></font></td>'    Set @msg = @msg + '<td><font Class=tableheaderfont><b> Estimated time to complete </b></font></td>'    Set @msg = @msg + '</tr>'    Select @msg = @msg + '<tr><td class=tableitem ><font class=tableitemfont>'                            + RTrim(IsNull(Jobcontent,"'))+ '</font></td><td class=tableitem ><font class=tableitemfont>'                          --+ RTrim (followuserid) + ' </font></td><td class=tableitem ><font class=tableitemfont> '                         + Convert(varchar(Ten), EndDate,121)+ '</font></td></tr>'                         from#twhereId=@id    Set @msg = @msg + '</table></td></tr><tr><td colspan=8>"'#000099"'></td></tr><tr><td align=left Colspan=8><font class="'Tableheaderfont"'><b> Please login: <a href=#> Management System </A> Inquiry details! If you have any questions, please contact your system administrator!! </b></font></a></td></tr></table></form></body></HTML> '        Select  @sub='Todo Reminders',@successto=Successto,@successtocc=Successtocc fromMaillistwhereId=@mailid        exec [SendMail] @successto,@msg,"',"',@successtocc,"',@sub,'HTML'        Delete#twhereId=@idEnd--SELECT sent_status,* from Msdb.dbo.sysmail_allitems

Secondly, there is no picture here, but we can still do it, for example, send a sympathy card to the employee's mailbox before the holiday, or send some pictures and text congratulations on the day before the employee's birthday. Because of the employee information, the film is just my new sample, please refer to and comment.

First of all, we can design the style in vs.

And then copy the constructed HTML code into SQL.

Declare @msg varchar(Max)            Set @msg = '"'s Most valuable Companies award'--Select @sub as AA, @rep as BB, @cc as CC, @msg as DDexec [SendMail]‘[email protected]',@msg,"',"',"',"','Dbmaile-mail with illustrations','HTML'

Then issued.

Here is just random to do a sample version, in practice, you can according to the actual requirements of the production of beautiful HTML template. (Original works, reproduced please indicate the source.)

If you have a good opinion, please put forward ....