1

The following TSQL script works like a charm:

declare @result as int
declare @myID as int
declare @sig as varchar(MAX)
declare @MsgBody as varchar(MAX)
declare @Attachments as varchar(MAX)

set @Attachments = '\\the-SQL-servers-host\the-share- 
name\myemail@harryLLC.com_files\image001.png'
--set @Attachments = @Attachments + ';\\the-SQL-servers-host\the-share- 
 name\the Estimate #1002.pdf'   

set @sig=
'<BR>
<BR>
<table>
  <tr>
    <td><img src="CID:image001.png"></td>
    <TD><table>
   <tr>    <td>Harry Abramowski</td></tr>
<tr>    <td>Harry Abramowski Services, LLC</td></tr>
    <tr><TD>(800)555-1212</TD></tr>
  <tr>  <td>myemail@HarryLLC.com</td></tr>
   <tr> <td><A href="https://www.facebook.com/harryLLC/">Facebook</a></td> 
</tr>
</table>
    </TD>
  </tr>
</table>'


set @MsgBody=  'This is my message. there is a signature at the bottom, with 
logo. And what follows this sentence is a picture.<br> <img 
src="cid:image001.png">'
+ '<BR>Pretty cool, Huh?<br><br>Harry' + @sig

EXEC  @result=msdb.dbo.sp_send_dbmail @Body_format=HTML,  @profile_name = 
 'myemail@harryLLC.com',
    @recipients = 'myemail@harryLLC.com',
    @subject = 'The DBMail answer with logo insertion', 
    @Body =  @MsgBody   ,
     --all of the following are optional
    @file_attachments= @Attachments 
    --, @reply_to = 'myemail@harryLLC.com'
    ,@mailitem_ID = @myID OUTPUT

 select items.mailitem_id, items.sent_status, items.sent_date from 
 msdb.dbo.sysmail_allitems items where items.mailitem_id = @myID

HOWEVER when I use VB6 code to call on msdb.dbo.sp_send_dbmail with even simpler HTML that above, I cannot get the image001.png to appear inside the message. It shows up as an attachment. I have mulled over this for two days and cannot figure out what Outlook sees differently between the vb6 code and the SQL script..

Any of you VB6 gurus ever tackle DBMail before? Oh and here's the sub I am testing with in VB6 - it delivers the message, and the image file. it just doesnt get the imagine inside the mail message like the script does.

Private Sub hardcoded()

Dim ObjCommand As New ADODB.Command
 ObjCommand.ActiveConnection = objConn
 ObjCommand.CommandText = "msdb.dbo.sp_send_dbmail"
 ObjCommand.CommandType = adCmdStoredProc
 ObjCommand.NamedParameters = True
  ObjCommand.Parameters.Append ObjCommand.CreateParameter("@profile_name", adVarChar, adParamInput, 100, "myemail@harryLLC.com")
  ObjCommand.Parameters.Append ObjCommand.CreateParameter("@Body_format", adVarChar, adParamInput, 5, "HTML")
  ObjCommand.Parameters.Append ObjCommand.CreateParameter("@recipients", adVarChar, adParamInput, 200, "myemail@harryLLC.com")
  ObjCommand.Parameters.Append ObjCommand.CreateParameter("@subject", adVarChar, adParamInput, 200, "Hardcoded test")

  Dim BodyString As String
  BodyString = "This is an inserted image that does not appear in the 
attachments list." & _
      "<BR>This is it right here => <img width=80 height=80 src=" & Chr(34) & 
"CID:image001.png" & Chr(34) & ">"

  ObjCommand.Parameters.Append ObjCommand.CreateParameter("@Body", adVarChar, adParamInput, 4000, BodyString)

  Dim AttachmentString As String
  AttachmentString = "\\the-sql-host\theshare\myemail@harryLLC.com_files\image001.png"
  ObjCommand.Parameters.Append 
ObjCommand.CreateParameter("@file_attachments", adVarChar, adParamInput, 500, Trim(AttachmentString))
  ObjCommand.Parameters.Append ObjCommand.CreateParameter("@mailitem_id", adInteger, adParamOutput)
  On Error Resume Next
 ObjCommand.Execute
If Err Then
 MsgBox Err.Number & ": " & Err.Description
Else
  MsgBox "Your mail id is " & ObjCommand("@mailitem_id")
End If

End Sub
Harry A
  • 105
  • 1
  • 10

1 Answers1

1

Of ALL the stupidest things Microsoft has done, this one is up there in the top five if not grand prize winner! OUTLOOK is case sensitive to the HTML in my messages! I notice that in the SQL script that I used cid:image001.png and in the VB6 code I had CID.....now at this point I was grasping at straws so I said ok lets see if that's it......and it was!

Harry A
  • 105
  • 1
  • 10