xp_sendmail with inline attachment

[dhkom]

I'm having trouble getting the following xp_sendsmtp documentation example working: 'This example uses SMTP to send inline HTML formatted message with a signature and two attachments, one of which is a ZIP file.'

The slightly modified example, with obvious placeholders, is shown below.

Suggestions or insights welcome. Thank you!

begin
  declare l_rc                  integer;
    declare s_content                           long varchar;
  --
    SET s_content = 
        'Content-Type: multipart/mixed; boundary="xxxxx";\\n' ||
        'This part of the email should not be shown.  If this ' ||
        'is shown then the email client is not MIME compatible\\n\\n' ||
        '--xxxxx\\n' ||
        'Content-Type: text/html;\\n' ||
        'Content-Disposition: inline;\\n\\n' ||
        'Plain text.' ||
        /* xp_read_file( '\\\\temp\\\\johndoe.sig.html' ) || */
        '--xxxxx\\n' ||
        'Content-Type: application/zip; name="test.zip"\\n' ||
        'Content-Transfer-Encoding: base64\\n' ||
        'Content-Disposition: attachment; filename="test.zip"\\n\\n' ||
        base64_encode( xp_read_file( 'c:\\\\temp\\\\test.zip' ) ) ||
        '\\n\\n' ||
        '--xxxxx--\\n'
    ; 
    --
    -- Port 2525 is for non-ssl email
    l_rc = call xp_startsmtp (
        smtp_sender = '(sender email)',
        smtp_server = '(mail server)',
        smtp_port = 2525,
        smtp_sender_name = 'John Doe',
        smtp_auth_username = '(sender email)',
        smtp_auth_password = '(password)'
    );
    --
  l_rc = call xp_sendmail (
        recipient = 'joe@contoso.com', 
        subject = 'Test sending email with attachment from SQL Anywhere', 
        "message" = s_content,
        content_type = 'ASIS',
        include_file = '\\\\temp\\\\bla.sql'
    );
    --
  l_rc = call xp_stopsmtp ();
end
go

[chris_keating]

Getting xp_get_mail_error_code() and xp_get_mail_error_text() might provide more insight into the problem. I would collect it after each xp_* call to troubleshoot.

[former_SQLA_member1694879]

Who is the ISP that you are connecting to? A lot have moved away from non-secure and only permit secure connections - MS and Google now only use modern authentication, leaving passwords and usernames in the past.

[dhkom]

Thanks for your replies. I should have been more clear on what didn't work. First, each of the xp_* calls succeeded and an email message is sent. The body as received by Outlook looks as follows (please see further commentary after snippet):

--==EMAIL_MIME_2024_0819_113522== Content-Type: application/octet-stream; name="bla.sql" Content-transfer-encoding: base64 Content-Disposition: attachment; filename="bla.sql"

Y2FsbCBwcmNfbXVsdGlfc2l0ZV9lbmJsICgnbicpOw0KY29tbWl0Ow0KDQpyZWFkICJDOlxcTWFp bnRTY2FwZSAtIE5vdCBWb2xhdGlsZVxcYmxkXzE0NFxcRGVwbG95XFxkYXRhYmFzZVxcYmxkXzE0 NC5zcWwiOw0KcmVhZCAiQzpcXE1haW50U2NhcGUgLSBOb3QgVm9sYXRpbGVcXGJsZF8xNDVcXENh bmRpZGF0ZXNcXDIwMjQtMDctMTJcXHdvcmsuc3FsIjsNCg0KICAgICAgICAgICBjYWxsIHByY19t dWx0aV9zaXRlX2VuYmwgKCd5Jyk7DQpjb21taXQ7DQo=

--==EMAIL_MIME_2024_0819_113522==--

Note that the verbiage "Plain Text." is not seen in the email.

As for my ISP, I can use the xp_* functions to send "regular" email messages just fine, including attachments via parameter "include_file".

I am using SA version 16 to test, because the customer we are developing for uses this version. The documentation code example I am modelling is taken from the SA16 documentation. SA16 does not support the "secure" parameter to xp_startsmtp.

I could try this code from SA17, including "secure=1", but I suspect it won't make a difference. I suspect the problem is with the construction of the multi-part email.

[PCollins]

We have had all sorts of issues with include_file in recent years, particularly when the emails are received / handled by Exchange/Outlook, the problem being content isn't shown.

These days we now don't use include_file and attached everything "manually" using repeated part sections in the email content build up

[dhkom]

Thanks. Per my comment above, "content isn't shown" is consistent with what we are running into. "include_file" works fine for us if the "content" value string is just text, including HTML.

It would be great if you could post some examples of "manually using repeated part sections in the email content build up". We're not expert on this. We want the email message to be HTML content to display in a body, plus an attachment file, ideally without having to create the file and then use "include_file" to attach it.

[PCollins]

This is a subset of a procedure that emails various documents which are held in variables (Doc1, Doc2, Doc3) with inline image in the signature.

I'm not saying it is perfect but it works 😉

Hope this helps (my forum foo formatting isn't very good)

set "vBody" = 'Content-Type: multipart/mixed; boundary="xxxxx";\
'
  || 'This part of the email should not be shown.  If this '
  || 'is shown then the email client is not MIME compatible\
\
'
  || '--xxxxx\
'
  || 'Content-Type: text/html;\
'
  || 'Content-Disposition: inline;\
\
'
  || '`<html>\
<head></head>\
<body>
Docs received
These docs have been received.
\
'
  || '
Doc1: ' || "Doc1" || '.
'`
  || "xp_read_file"('D:\\\\system-email-sig.htm')
  || '--xxxxx\
'
  || 'Content-Type: application/png; name="image001.png"\
'
  || 'Content-Transfer-Encoding: base64\
'
  || 'Content-Disposition: attachment; filename="image001.png"\
\
'
  || "base64_encode"("xp_read_file"('D:\\\\image001.png'))
  || '\
\
';
  if "Doc1" is not null then
  set "vBody" = "vBody"
    || '--xxxxx\
'
    || 'Content-Type: application/pdf; name="Doc1.pdf"\
'
    || 'Content-Transfer-Encoding: base64\
'
    || 'Content-Disposition: attachment; filename="Doc1.pdf"\
\
'
    || "base64_encode"("Doc1")
    || '\
\
'
  end if;
  if "Doc2" is not null then
  set "vBody" = "vBody"
    || '--xxxxx\
'
    || 'Content-Type: application/pdf; name="Doc2.pdf"\
'
    || 'Content-Transfer-Encoding: base64\
'
    || 'Content-Disposition: attachment; filename="Doc2.pdf"\
\
'
    || "base64_encode"("Doc2")
    || '\
\
'
  end if;
  if "Doc3" is not null then
  set "vBody" = "vBody"
    || '--xxxxx\
'
    || 'Content-Type: application/pdf; name="Doc3.pdf"\
'
    || 'Content-Transfer-Encoding: base64\
'
    || 'Content-Disposition: attachment; filename="Doc3.pdf"\
\
'
    || "base64_encode"("Doc3")
    || '\
\
'
  end if;
  set "vBody" = "vBody"
    || '--xxxxx--\
';
  call "xp_startsmtp"('noreply@domain.comm','mail.domain.com');
  call "xp_sendmail"("recipient" = 'ops@domain.com',"subject" = 'Duplicate PreAlert processed',"message" = "vBody","content_type" = 'ASIS');
  call "xp_stopsmtp"();

[VolkerBarth]

Oops, I had thought a pre-tag would be of help but it apparently does not work well with code with included HTML tags...

[PCollins]

Hi,

I have been looking at this again and I can confirm we still have issues using Exchange Server as the first SMTP server. We now send all email out via either a Linux MTA or through Mimecast, both of which forward onto Exchange and the result is the email displays correctly.

We spent hours playing with content build up and never manage to get Exchange / Outlook to display anything when Exchanged was used as the initial SMTP server.

The headers show it thinks the mail is text/plain every time so there is clearly something weird going on with the data it parses in the receipt. But short of putting wireshark on the exchange server we ran out of ideas.

Anyway this is a complete example that works although note some deliberate spaces in some html tags so they display here:

begin
declare vBody long varchar;
set "vBody" = 'Content-Type: multipart/mixed; boundary="mixed_boundary"\
\
'
         || '--mixed_boundary\
'
         || 'Content-Type: text/html; charset="utf-8"'
         || 'Content-Transfer-Encoding: 8bit\
\
'
         || '< !DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">< html>< body style="font-family: consolas, monospace;"><h3 style="font-size:10.0pt; font-family:Arial,Sans-serif;">Email with multiple attachments</h3><p>This is an example email with multiple attachments.</p>\
'
         || '<p>Some other line here.</p><img border=0 width=148 height=71 src="cid:50eef9fb" alt="image001.png"">< /body>< /html>\
\
'
         || '--mixed_boundary\
'
         || 'Content-Type: image/png;\
 name="image001.png"\
'
         || 'Content-Description: image001.png\
'
         || 'Content-Disposition: inline; filename="image001.png";\
'
         || 'Content-ID: <50eef9fb>\
'
         || 'Content-Transfer-Encoding: base64\
\
'
         || "base64_encode"("xp_read_file"('D:\\\\Scripts\\\\image001.png'))
         || '\
\
'
         || '--mixed_boundary\
'
         || 'Content-Type: application/pdf;\
 name="DOC1.pdf"\
'
         || 'Content-Transfer-Encoding: base64\
'
         || 'Content-Disposition: attachment;\
 filename="DOC1.pdf"\
\
'
         || "base64_encode"("xp_read_file"('D:\\\\Scripts\\\\DOC1.pdf'))
         || '\
\
'
         || '--mixed_boundary\
'
         || 'Content-Type: application/pdf;\
 name="DOC2.pdf"\
'
         || 'Content-Transfer-Encoding: base64\
'
         || 'Content-Disposition: attachment;\
 filename="DOC2.pdf"\
\
'
         || "base64_encode"("xp_read_file"('D:\\\\Scripts\\\\DOC2.pdf'))
         || '\
\
'
         || '--mixed_boundary\
'
         || 'Content-Type: application/pdf;\
 name="DOC3.pdf"\
'
         || 'Content-Transfer-Encoding: base64\
'
         || 'Content-Disposition: attachment;\
 filename="DOC3.pdf"\
\
'
         || "base64_encode"("xp_read_file"('D:\\\\Scripts\\\\DOC3.pdf'))
         || '\
\
'
         || '--mixed_boundary--';
      call "xp_startsmtp"('example@domain.com','smtp.domain.com');
      call "xp_sendmail"("recipient" = 'user@domain.com',"subject" = 'Multi Attachement Email',"message" = "vBody","content_type" = 'ASIS');
      call "xp_stopsmtp"();
end

If you want to include a plain text version as well this should work:

begin
declare vBody long varchar;
set "vBody" = 'Content-Type: multipart/mixed; boundary="mixed_boundary"\
\
'
         || '--mixed_boundary\
'
         || 'Content-Type: multipart/related; boundary="related_boundary"\
\
'
         || '--related_boundary\
'
         || 'Content-Type: multipart/alternative; boundary="alternative_boundary"\
\
'
         || '--alternative_boundary\
'
         || 'Content-Type: text/plain; charset="us-ascii"\
'
         || 'Content-Transfer-Encoding: 8bit\
\
'
         || 'Plain Text says Hello\
\
'
         || '--alternative_boundary\
'
         || 'Content-Type: text/html; charset="utf-8"'
         || 'Content-Transfer-Encoding: 8bit\
\
'
         || '< !DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">< html>< body style="font-family: consolas, monospace;"><h3 style="font-size:10.0pt; font-family:Arial,Sans-serif;">Email with multiple attachments</h3><p>This is an example email with multiple attachments.</p>\
'
         || '<p>Some other line here.</p><img border=0 width=148 height=71 src="cid:50eef9fb" alt="image001.png"">< /body>< /html>\
\
'
         || '--alternative_boundary--\
\
'
         || '--related_boundary\
'
         || 'Content-Type: image/png;\
 name="image001.png"\
'
         || 'Content-Description: image001.png\
'
         || 'Content-Disposition: inline; filename="image001.png";\
'
         || 'Content-ID: <50eef9fb>\
'
         || 'Content-Transfer-Encoding: base64\
\
'
         || "base64_encode"("xp_read_file"('D:\\\\Scripts\\\\image001.png'))
         || '\
\
--related_boundary--\
\
'
         || '--mixed_boundary\
'
         || 'Content-Type: application/pdf;\
 name="DOC1.pdf"\
'
         || 'Content-Transfer-Encoding: base64\
'
         || 'Content-Disposition: attachment;\
 filename="DOC1.pdf"\
\
'
         || "base64_encode"("xp_read_file"('D:\\\\Scripts\\\\DOC1.pdf'))
         || '\
\
'
         || '--mixed_boundary\
'
         || 'Content-Type: application/pdf;\
 name="DOC2.pdf"\
'
         || 'Content-Transfer-Encoding: base64\
'
         || 'Content-Disposition: attachment;\
 filename="DOC2.pdf"\
\
'
         || "base64_encode"("xp_read_file"('D:\\\\Scripts\\\\DOC2.pdf'))
         || '\
\
'
         || '--mixed_boundary\
'
         || 'Content-Type: application/pdf;\
 name="DOC3.pdf"\
'
         || 'Content-Transfer-Encoding: base64\
'
         || 'Content-Disposition: attachment;\
 filename="DOC3.pdf"\
\
'
         || "base64_encode"("xp_read_file"('D:\\\\Scripts\\\\DOC3.pdf'))
         || '\
\
'
         || '--mixed_boundary--';
      call "xp_startsmtp"('example@domain.com','smtp.domain.com');
      call "xp_sendmail"("recipient" = 'user@domain.com',"subject" = 'Multi Attachement Email',"message" = "vBody","content_type" = 'ASIS');
      call "xp_stopsmtp"();
end

[dhkom]

Thanks for all the work you put in on this!

The SMTP server I'm using is from a Linux Shared Hosting account. I'm not an expert on this "stuff", but I'm guessing that wouldn't be Exchange.

Some problems we have the need and/or time to "put in what it takes" to solve, but not this one right now. We went ahead to create a temporary file for the attachment, and then referenced it with an "include_file" parameter to xp_sendmail. This works.

I'll keep your code handy in case we need to revisit this.

[dhkom]

pcollins - regarding your comment, above, "we still have issues using Exchange Server as the first SMTP server" - I just ran into issues too.

I use xp_sendmail specifying parameters "message" and "include_file", using the exact same SQL Anywhere version (16.0.0.2798), and get different results depending on the SMTP server: 1) Using the SMTP Server of a Linux Shared Hosting account, the received email includes a body and attachment.
2) Using the SMTP Server of my customer, almost certainly Exchange, the received email has NO body but does have an attachment.

Fortunately, my customer will be happy with receiving two emails: one with an email body, and the other just an attachment.

If I had to get around this, I'm thinking of invoking a .NET DLL to send the email. I believe from SA16, at least, this would have to be an old .NET Framework DLL. I'm just thinking of a potential contingency.

[dhkom]

I may be on to something on this:

I am testing using 16.0.0.1324. I vaguely remember experiencing a problem whereby a sent email message had an empty body. Sure enough, I found the following by searching through my documentation:

================(Build #1535 - Engineering Case #739835)================

The body of email messages sent using xp_sendmail would have been truncated 
at 255 characters. This has been fixed.

... I suspect this is what is going on here. Will investigate further and post an update.

Thanks to all, especially pcollins for code sample - which I will study.

[PCollins]

We are using the latest version of 17 and I'm certain it's still an issue for us - I will test again

Also the same email shows content when displayed in say GMail

[VolkerBarth]

Hm, if you do have to test with the long EOL'ed version 16, I would recommend to use the customer's version (hopefully the latest EBF, i.e. built 2798 on Windows/Linux) instead of the much older GA version 1324... (Note, I'm not suggesting the particular issue does behave differently with those versions... I simply don't know.)

[dhkom]

I have now been testing in SA 17 (17.0.10.5963 - older there too), and am not having any more success. I have verified that my "usual" emails send in my SA17 whereas they did not in my long EOL'ed SA16 with the 255 character bug.

pcollins - I'm not having much success yet adapting your sample. I'm noticing, however, there are no end tags for <head> or <body>.

For the sake of expediency, I'll probably just create a file in %temp% and send it via "include_file", which does work for me.

I'd be very appreciative if someone were inclined to post an easily reproducible SQL Anywhere snippet. No worries if not convenient.

As for that obsolete SA16 version - for better or for worse, I'm nervous about mucking with my well-oiled development machine. However I am about to move to a new development machine, and I'll make sure a much more recent SA16 is up on running on that.

[PCollins]

sorry I should have included a copy of the D:\\system-email-sig.htm, this has the closing tags in it, along with a signature html before it