spMFSendHTMLBodyEmail¶

Signature¶

EXEC dbo.spMFSendHTMLBodyEmail
    @Body = N'<html>...</html>',
    @MessageTitle = N'...',
    @FromEmail = N'sender@example.com',
    @ToEmail = N'user1@example.com;user2@example.com',
    @CCEmail = NULL,
    @AttachementPaths = NULL,
    @Mailitem_ID = NULL OUTPUT,
    @ProcessBatch_ID = NULL,
    @Debug = 0;

Returns¶

INT: 1 on success; -1 on error.

Parameters¶

@Body

Type: NVARCHAR(MAX)
Required: Yes
Description: Email body in HTML format.

@MessageTitle

Type: NVARCHAR(255)
Required: Yes
Description: Email subject.

@FromEmail

Type: NVARCHAR(255)
Required: Yes
Description: Sender email address (from mail profile).

@ToEmail

Type: NVARCHAR(MAX)
Required: Yes
Description: Recipient addresses; use ‘;’ as delimiter for multiple recipients.

@CCEmail

Type: NVARCHAR(MAX)
Required: No
Description: CC recipient addresses; use ‘;’ as delimiter.

@AttachementPaths

Type: NVARCHAR(MAX)
Required: No
Description: Full path(s) to file attachments; use ‘;’ as delimiter.

@Mailitem_ID

Type: INT
Required: No (OUTPUT)
Description: msdb Database Mail item id.

@ProcessBatch_ID

Type: INT
Required: No
Description: Process batch logging id.

@Debug

Type: SMALLINT
Required: No (default = 0)
Description: Debug level.

Purpose¶

This procedure will send a single email with a body is formatted in HTML format using msdb database mail manager

Additional Info¶

The @body param must include the full body in HTML format, the following is the bare bones.

<html>
<head> </head>
<body> </body>
</html>

Prerequisites¶

msdb Database mail need to be activiated and configured.

The email from is taken from the mail profile.

Examples¶

Without an attachment

DECLARE @Mailitem_ID INT;
DECLARE @Body NVARCHAR(MAX) = '<html>  <head>   <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />   <style type="text/css">    div {line-height: 100%;}      body {-webkit-text-size-adjust:none;-ms-text-size-adjust:none;margin:0;padding:0;}     body, #body_style {min-height:1000px;font: 10pt Verdana, Geneva, Arial, Helvetica, sans-serif;}    p {margin:0; padding:0; margin-bottom:0;}    h1, h2, h3, h4, h5, h6 {color: black;line-height: 100%;}      table {     border-collapse: collapse;  ??      border: 1px solid #3399FF;  ??      font: 10pt Verdana, Geneva, Arial, Helvetica, sans-serif;  ??      color: black;        padding:5;        border-spacing:1;        border:0;       }    table caption {font-weight: bold;color: blue;}    table td, table th, table tr,table caption { border: 1px solid #eaeaea;border-collapse:collapse;vertical-align: top; }    table th {font-weight: bold;font-variant: small-caps;background-color: blue;color: white;vertical-align: bottom;}   </style>  </head><body><div class=greeting><p>Hi </p><br></div><div class=content><p> This is the body </p><br></div><div class=signature><p> yours sincerely Me </p><br></div><div class=footer><p>Company details</p></div></body></html>'
EXEC dbo.spMFSendHTMLBodyEmail @Body = ,
@MessageTitle = 'test',
@FromEmail = 'support@lamininsolutions.com',
@ToEmail = 'support@lamininsolutions.com',
@CCEmail = 'support@lamininsolutions.com',
@Mailitem_ID = @Mailitem_ID OUTPUT,
@ProcessBatch_ID = null,
@Debug = 1
SELECT @Mailitem_ID

With an Attachment (include a test.txt file on the folder referenced below to allow this example to work)

DECLARE @Mailitem_ID INT;
DECLARE @Body NVARCHAR(MAX) = '<html>  <head>   <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />   <style type="text/css">    div {line-height: 100%;}      body {-webkit-text-size-adjust:none;-ms-text-size-adjust:none;margin:0;padding:0;}     body, #body_style {min-height:1000px;font: 10pt Verdana, Geneva, Arial, Helvetica, sans-serif;}    p {margin:0; padding:0; margin-bottom:0;}    h1, h2, h3, h4, h5, h6 {color: black;line-height: 100%;}      table {     border-collapse: collapse;  ??      border: 1px solid #3399FF;  ??      font: 10pt Verdana, Geneva, Arial, Helvetica, sans-serif;  ??      color: black;        padding:5;        border-spacing:1;        border:0;       }    table caption {font-weight: bold;color: blue;}    table td, table th, table tr,table caption { border: 1px solid #eaeaea;border-collapse:collapse;vertical-align: top; }    table th {font-weight: bold;font-variant: small-caps;background-color: blue;color: white;vertical-align: bottom;}   </style>  </head><body><div class=greeting><p>Hi </p><br></div><div class=content><p> This is the body </p><br></div><div class=signature><p> yours sincerely Me </p><br></div><div class=footer><p>Company details</p></div></body></html>'
EXEC dbo.spMFSendHTMLBodyEmail @Body = ,
@MessageTitle = 'test',
@FromEmail = 'support@lamininsolutions.com',
@ToEmail = 'support@lamininsolutions.com',
@CCEmail = 'support@lamininsolutions.com',
@AttachementPaths = 'c:\test.txt;c:\test1.txt',
@Mailitem_ID = @Mailitem_ID OUTPUT,
@ProcessBatch_ID = null,
@Debug = 1
SELECT @Mailitem_ID

Changelog¶

Date	Author	Description
2024-03-02	LC	Increase the size of the toemail paramters to allow for sending emails to larger groups
2023-11-16	LC	Add attachment as an option to email
2022-01-18	LC	Fix cc email bug
2021-01-29	LC	Updated to allow for setting profile in MFEmailTemplate
2021-01-26	LC	Create procedure